Jah, seda koodi saab kindlasti **kiirendada ja optimeerida**, eriti kuna tegemist on keeruka **ruumilise JOIN-iga**, mida tehakse mitmekordselt igas batch’is. Siin on kõige olulisemad punktid, kuidas seda kiiremaks ja tõhusamaks muuta:

---

## ✅ **1. Ära SELECT-i kogu `nodes` tabelit igas batch’is**

Igas `psql -c` käsus tehakse:

```sql
nodes AS (SELECT * FROM ${pk}.nodes),
```

See tähendab, et kogu `nodes` tabel **laetakse mällu iga 10 000 rea kaupa** – see on **ebavajalik kulu**. Tee sellest **ajutine temp tabel varem**:

```bash
psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS tmp_nodes;
CREATE TEMP TABLE tmp_nodes AS SELECT id, geom FROM ${pk}.nodes;
CREATE INDEX ON tmp_nodes USING GIST (geom);"
```

Ja siis asenda igas batch’is:

```sql
nodes AS (SELECT * FROM tmp_nodes)
-- või lihtsalt viita otse: tmp_nodes
```

---

## ✅ **2. Tee geomeetriafunktsioonid eeltöötlusena**

`ST_StartPoint(...)` ja `ST_EndPoint(...)` arvutatakse **iga JOIN puhul uuesti**, iga kord. See on aeglane.

Lahendus: arvuta need **korra ette** ja salvesta ajutiselt:

```bash
psql ${CONNECTION_NAME} -c "DROP TABLE IF EXISTS tmp_link_points;
CREATE TEMP TABLE tmp_link_points AS
SELECT id, ST_StartPoint(geom) AS pt_start, ST_EndPoint(geom) AS pt_end, geom
FROM ${pk}.links;
CREATE INDEX ON tmp_link_points USING GIST (pt_start);
CREATE INDEX ON tmp_link_points USING GIST (pt_end);"
```

Ja siis batch'i sees tee näiteks:

```sql
WITH link_batch AS (
  SELECT * FROM tmp_link_points WHERE id BETWEEN $i AND $END
),
link_data AS (
  SELECT
    l.id,
    n1.id AS source,
    n2.id AS target,
    ST_Length(l.geom) AS cost
  FROM link_batch l
  JOIN tmp_nodes n1 ON ST_DWithin(l.pt_start, n1.geom, 0.01) AND ST_Distance(l.pt_start, n1.geom) = 0
  JOIN tmp_nodes n2 ON ST_DWithin(l.pt_end, n2.geom, 0.01) AND ST_Distance(l.pt_end, n2.geom) = 0
)
UPDATE ${pk}.links l
SET source = d.source,
    target = d.target,
    cost = ROUND(d.cost::numeric, 1)
FROM link_data d
WHERE l.id = d.id;
```

---

## ✅ **3. Vähenda SQL kutsete arvu — tee kõik ühes `psql` sessioonis**

Kui võimalik, tee kogu batchimine **PostgreSQLi enda sees** PL/pgSQL’ga või `DO $$ BEGIN ... END $$`. Aga isegi Bashis saad sa vältida iga 10k kaupa uut `psql` protsessi, kui kasutad nt:

```bash
psql ${CONNECTION_NAME} <<EOF
-- kõik 5 batchi siia
EOF
```

---

## ✅ **4. Kasuta paralleeltäitmist (advanced)**

Kui Sul on mitu südamikku (CPU), saad paralleelselt töödelda nt:

```bash
parallel -j 4 "
psql ${CONNECTION_NAME} -c \"
-- sama batchipõhine SQL siia
\"
" ::: $(seq 1 $BATCH_SIZE $MAX_ID)
```

Aga siin pead olema ettevaatlik, et paralleelsed update'id ei läheks konflikti (soovitan ainult siis, kui tabelil on sobivad indeksid ja väldid samade `id` väärtuste kattumist).

---

## 🧠 Kokkuvõte – kiirendamise võtted

|Võte|Mõju|
|---|---|
|`tmp_nodes` ja `tmp_link_points` tabelid|korduv SELECT → 1x SELECT|
|`ST_StartPoint` ja `ST_EndPoint` eelnevalt arvutatud|väldib 2x geom-funktsiooni iga JOIN-i korral|
|Vähem `psql` kutseid|väldib protsessikulu ja I/O overheadi|
|Paralleeltäitmine (vajadusel)|kiirem suurte andmehulkade korral|

---

Soovi korral võin kõik selle koosmõjus ümber kirjutada täpse, optimeeritud `bash`-skriptina. Kas tahad?

Väga hea – siin on **optimeeritud `bash`-skript**, mis:

- **valmistab ette `tmp_nodes` ja `tmp_link_points`** (eelarvutatud geomeetriad ja GIST-indeksid),
    
- **töötleb `links` tabeli ridu batchide kaupa** (`10 000` kaupa),
    
- **uuendab `source`, `target` ja `cost` veerud** kiirendatud kujul,
    
- kasutab **ainult ühte `psql` sessiooni** (vähem süsteemikulusid).
    

---

## 🔧 **Optimeeritud Bash-skript: `update_links_sources_targets_costs.sh`**

```bash
#!/bin/bash

# Määrangud
CONNECTION_NAME="postgresql://user:password@localhost/dbname"
SCHEMA="your_schema"  # näiteks: public
BATCH_SIZE=10000

echo "🔄 Alustame ettevalmistusega..."
psql "${CONNECTION_NAME}" -v schema="${SCHEMA}" <<EOF

-- 1. Eeltöötlus: arvuta tippude ja lõikude punktid
DROP TABLE IF EXISTS tmp_nodes;
CREATE TEMP TABLE tmp_nodes AS
SELECT id, geom FROM :schema.nodes;
CREATE INDEX ON tmp_nodes USING GIST (geom);

DROP TABLE IF EXISTS tmp_link_points;
CREATE TEMP TABLE tmp_link_points AS
SELECT id, ST_StartPoint(geom) AS pt_start, ST_EndPoint(geom) AS pt_end, geom
FROM :schema.links;
CREATE INDEX ON tmp_link_points USING GIST (pt_start);
CREATE INDEX ON tmp_link_points USING GIST (pt_end);

EOF

# Leia maksimaalne ID
MAX_ID=$(psql "${CONNECTION_NAME}" -tA -c "SELECT max(id) FROM ${SCHEMA}.links;")
echo "📏 MAX_ID = $MAX_ID"

# 2. Batch processing
i=1
while [ "$i" -le "$MAX_ID" ]; do
  END=$((i + BATCH_SIZE - 1))
  echo "🚀 Töötlen batch: $i - $END"

  psql "${CONNECTION_NAME}" -v schema="${SCHEMA}" -v i="${i}" -v end="${END}" <<EOF
WITH link_batch AS (
  SELECT * FROM tmp_link_points WHERE id BETWEEN :i AND :end
),
link_data AS (
  SELECT
    l.id,
    n1.id AS source,
    n2.id AS target,
    ST_Length(l.geom) AS cost
  FROM link_batch l
  JOIN tmp_nodes n1
    ON ST_DWithin(l.pt_start, n1.geom, 0.01)
   AND ST_Distance(l.pt_start, n1.geom) = 0
  JOIN tmp_nodes n2
    ON ST_DWithin(l.pt_end, n2.geom, 0.01)
   AND ST_Distance(l.pt_end, n2.geom) = 0
)
UPDATE ${SCHEMA}.links l
SET source = d.source,
    target = d.target,
    cost = ROUND(d.cost::numeric, 1)
FROM link_data d
WHERE l.id = d.id;
EOF

  i=$((END + 1))
done

echo "✅ Valmis!"
```

---

## ⚙️ Kuidas kasutada?

1. Muuda skriptis:
    
    - `CONNECTION_NAME` – sobivaks PostgreSQL ühendusstringiks
        
    - `SCHEMA` – nt `public`, `mydata`, vms
        
2. Salvesta faili: `update_links_sources_targets_costs.sh`
    
3. Tee käivitatavaks:
    
    ```bash
    chmod +x update_links_sources_targets_costs.sh
    ```
    
4. Käivita:
    
    ```bash
    ./update_links_sources_targets_costs.sh
    ```
    

---

## 🚀 Kasu

|Parandus|Tulem|
|---|---|
|Temp-tabelid|Ei lae `nodes` ja `links` uuesti iga kord|
|Eelarvutatud punktid|Ei kordu `ST_StartPoint()`|
|Ruumiindeksid|Kiirem `JOIN`|
|Batching|Vähem mälu, väiksemad transaktsioonid|
|Üks `psql` per batch|Vähem süsteemikoormust|

---

Kui soovid lisada ka paralleeltöötlust `GNU parallel` abil või täiendada teise andmeväljaga (nt `travel_time`, `is_main_road`), saan kohe ka selle lisada. Kas soovid?