ST_D Entro esponenzialmente lento. Non riesco a trovare quello che sto facendo di sbagliato

Aug 20 2020
  • Versione PostGIS: 3.1
  • Versione PostgreSQL: 12.3
  • La macchina con cui sto lavorando ha: 126 GB di RAM, 48 core della CPU

Informazioni:

Sto iniziando con PostGIS.

Il mio obiettivo è ottenere tutti i dati di corrispondenza tra due punti.

lv.geopoint e sub.geopoint sono entrambi punti GEOGRAPHY (SRID: 4326) e hanno indici GIST su di essi.

Il mio sub SELECT restituisce circa 3k righe, la mia tabella "valeurs_foncieres" ha invece 14.000.000 di righe.

Ho gli indici BTREE su valeurs_foncieres.id, caracteristiques_2018.id, caracteristiques_2018.num_acc, usagers_2018.id, usagers_2018.num_acc, Vehicules_2018.id, Vehicules_2018.num_acc.

Il problema:

La query diventa esponenzialmente lenta man mano che aumento la distanza di ST_DWithin.

  • Precisione 100: 2sec
  • Precisione 1000: 10 sec
  • Precisione 10.000: 6min

Ecco la domanda:

SELECT
    DISTINCT(sub.num_acc),
    sub.geopoint,
    sub.id
FROM
    (
    SELECT
        DISTINCT(u.num_acc) AS unumacc, c.*
    FROM
        usagers_2018 u
    INNER JOIN vehicules_2018 v ON
        u.num_acc = v.num_acc
    INNER JOIN caracteristiques_2018 c ON
        u.num_acc = c.num_acc
    WHERE
        u.grav = '2'
    ORDER BY
        c.id
) AS sub
INNER JOIN valeurs_foncieres vf ON
    ST_DWithin(vf.geopoint,
    sub.geog,
    1000,
    FALSE);

Ecco lo SPIEGAZIONE:

HashAggregate  (cost=265577998.10..265578004.81 rows=671 width=49)
  Group Key: c.num_acc, c.geopoint, c.id
  ->  Nested Loop  (cost=9948.38..264845621.97 rows=97650150 width=49)
        ->  Unique  (cost=9947.84..10316.67 rows=6706 width=170)
              ->  Sort  (cost=9947.84..9964.60 rows=6706 width=170)
                    Sort Key: c.id, u.num_acc, c.an, c.mois, c.jour, c.hrmn, c.lum, c.agg, c."int", c.atm, c.col, c.com, c.adr, c.gps, c.lat, c.long, c.dep, c.lat_gps, c.long_gps, c.geopoint, c.geog
                    ->  Gather  (cost=3200.48..9521.63 rows=6706 width=170)
                          Workers Planned: 1
                          ->  Nested Loop  (cost=2200.48..7851.03 rows=3945 width=170)
                                Join Filter: ((u.num_acc)::text = (v.num_acc)::text)
                                ->  Parallel Hash Join  (cost=2200.06..6686.70 rows=2075 width=170)
                                      Hash Cond: ((c.num_acc)::text = (u.num_acc)::text)
                                      ->  Parallel Seq Scan on caracteristiques_2018 c  (cost=0.00..2859.90 rows=33990 width=157)
                                      ->  Parallel Hash  (cost=2174.12..2174.12 rows=2075 width=13)
                                            ->  Parallel Seq Scan on usagers_2018 u  (cost=0.00..2174.12 rows=2075 width=13)
                                                  Filter: ((grav)::text = '2'::text)
                                ->  Index Only Scan using vehicules_2018_num_acc_idx on vehicules_2018 v  (cost=0.42..0.54 rows=2 width=13)
                                      Index Cond: (num_acc = (c.num_acc)::text)
        ->  Index Scan using valeurs_foncieres_geopoint_idx on valeurs_foncieres vf  (cost=0.54..39477.72 rows=1456 width=32)
              Index Cond: (geopoint && _st_expand(c.geog, '1000'::double precision))
              Filter: st_dwithin(geopoint, c.geog, '1000'::double precision, false)
JIT:
  Functions: 30
  Options: Inlining true, Optimization true, Expressions true, Deforming true

Domande:

È normale? Come posso diminuire il tempo di esecuzione?

Risposte

3 robinloche Aug 21 2020 at 09:46

14.000.000 di righe non sono piccole. Inoltre, se il geog che hai è distribuito uniformemente, il numero di punti interessati è circa x100 quando moltiplichi il tuo raggio x10 (l'area del cerchio dipende da r²), quindi è normale che il tuo aumento del tempo sembri quadrato. Qui sembra esserci qualcosa di più, ma più dati manipoli, più operazioni avrai potenzialmente bisogno a causa di tutte le gestioni della cache e delle chiamate del disco (non vero per piccoli dati o grandi cache).

Qui la spiegazione sembra ok, usa l'indice quindi non è il problema. Dovresti solo assicurarti di VACUUM ANALYZE le tue tabelle ma non dovrebbe cambiare molto.

La cosa principale che puoi fare se non l'hai fatto è modificare il tuo postgresql. Per impostazione predefinita, i parametri sono davvero conservativi, se hai un server di grandi dimensioni devi modificare i parametri per usarlo correttamente. Questi parametri possono essere gestiti in questo file su linux: /etc/postgresql/12/main/postgresql.conf quindi è necessario riavviare postgres (puoi facilmente trovare doc su Internet se hai domande su questo). In genere, quello che modifico sono i seguenti (adattati per circa 120Go e 48 CPU di ram):

  • shared_buffers = 30 GB
  • dimensione_cache_effettiva = 80 GB
  • work_mem = 256 MB
  • maintenance_work_mem = 5 GB
  • autovacuum_work_mem = 5 GB
  • effect_io_concurrency = 200 (per SSD o 2 per disco)
  • max_worker_processes = 48
  • max_parallel_workers = 48
  • max_parallel_workers_per_gather = 12
  • wal_buffers = 16 MB
  • min_wal_size = 1 GB
  • max_wal_size = 2 GB

Probabilmente non sono perfetti e definiti in parte a causa della documentazione che ho trovato e in parte per tentativi e fallimenti su grande richiesta. Ma se non hai configurato affatto il tuo postgresql (hai detto di aver iniziato) dovrebbe fare una grande differenza nelle prestazioni per grandi richieste (la tua non è così grande, ma dovrebbe avere un impatto). I dati della geometria sono generalmente grandi, quindi dovrebbe richiedere più spazio rispetto all'uso tipico di postgresql. Inoltre, se puoi, assicurati di mettere i tuoi dati su SSD, anche questo può avere un grande impatto.

MODIFICARE

Ho appena riletto la tua richiesta e non capisco bene perché hai bisogno di tutti i punti entro X metri se dopo tieni solo una riga per numacc. O non hai inserito l'intera query o hai davvero bisogno solo di un punto. Quindi l'ho riscritto nel caso in cui quello che volevi davvero fosse ottenere il punto più vicino. Ho usato CTE MATERIALIZZATO, che crea una tabella temporanea per ogni passaggio, a volte può davvero migliorare le prestazioni, quindi nel caso in cui volessi ottenere tutti i punti e non solo il vicino più vicino, puoi provare a eseguirlo così com'è rimuovendo l'ORDINE BY e il LIMIT in INNER JOIN LATERAL alla fine. E ovviamente qui limito la ricerca con ST_DWithin ma se vuoi un vero vicino più vicino puoi rimuovere questo WHERE:

WITH usg AS MATERIALIZED
(
    SELECT
            DISTINCT(u.num_acc) AS unumacc
            , c.*
        FROM
            usagers_2018 u
        WHERE
            u.grav = '2'
        INNER JOIN caracteristiques_2018 c ON
            u.num_acc = c.num_acc
        ORDER BY
            c.id
), sub AS MATERIALIZED
(
    SELECT
            DISTINCT(usg.unumacc)
            , usg.*
            , v.*
        FROM
            usg
        INNER JOIN vehicules_2018 v ON
            usg.num_acc = v.num_acc
)
SELECT
        sub.*
        , vf.*
    FROM sub
    INNER JOIN LATERAL 
        (
            SELECT
                    vf.*
                FROM
                    valeurs_foncieres vf
                WHERE
                    ST_DWithin(
                        vf.geopoint
                        ,sub.geog
                        , 1000
                        ,FALSE
                    )
                ORDER BY vf.geopoint <-> sub.geog
                LIMIT 1
        )   
    ON TRUE;