ST_DWithin exponencialmente lento. No puedo encontrar lo que estoy haciendo mal

Aug 20 2020
  • Versión de PostGIS: 3.1
  • Versión de PostgreSQL: 12.3
  • La máquina con la que estoy trabajando tiene: 126G RAM, 48 núcleos de CPU

Info:

Estoy comenzando con PostGIS.

Mi objetivo es obtener todos los datos coincidentes entre dos puntos.

lv.geopoint y sub.geopoint son puntos GEOGRAPHY (SRID: 4326) y tienen índices GIST.

Mi sub SELECT devuelve alrededor de 3k líneas, mi tabla 'valeurs_foncieres' sin embargo tiene 14 000 000 de líneas.

Tengo índices BTREE en valeurs_foncieres.id, caracteristiques_2018.id, caracteristiques_2018.num_acc, usesrs_2018.id, usesrs_2018.num_acc, vehicules_2018.id, vehicules_2018.num_acc.

El problema:

La consulta se vuelve exponencialmente lenta a medida que aumento la distancia de ST_DWithin.

  • Precisión 100: 2 segundos
  • Precisión 1000: 10 segundos
  • Precisión 10000: 6min

Aquí está la consulta:

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);

Aquí está la EXPLICACIÓN:

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

Preguntas:

¿Esto es normal? ¿Cómo puedo disminuir el tiempo de ejecución?

Respuestas

3 robinloche Aug 21 2020 at 09:46

14 000 000 de líneas no es poco. Además, si la geografía que tiene está distribuida uniformemente, el número de puntos en cuestión es alrededor de x100 cuando multiplica su radio x10 (el área del círculo depende de r²), por lo que es normal que su aumento de tiempo parezca cuadrado. Aquí parece ser más que eso, pero cuantos más datos manipule, más operaciones necesitará potencialmente debido a toda la gestión de la caché y la llamada al disco (no es cierto para datos pequeños o caché grande).

Aquí la explicación parece estar bien, usa el índice, por lo que no es el problema. Debe asegurarse de ANALIZAR AL VACÍO sus tablas, pero no debería cambiar mucho.

Lo principal que puede hacer si no lo hizo es modificar su postgresql. Por defecto, los parámetros son realmente conservadores, si tienes un servidor grande necesitas modificar los parámetros para usarlo correctamente. Estos parámetros se pueden manejar en este archivo en linux: /etc/postgresql/12/main/postgresql.conf, luego debe reiniciar postgres (puede encontrar fácilmente el documento en Internet si tiene preguntas al respecto). Normalmente, lo que modifico son los siguientes (adaptados para alrededor de 120 Go y 48 CPU de RAM):

  • shared_buffers = 30GB
  • tamaño_caché_efectivo = 80 GB
  • work_mem = 256 MB
  • mantenimiento_trabajo_mem = 5GB
  • autovacuum_work_mem = 5GB
  • Effective_io_concurrency = 200 (para SSD o 2 para disco)
  • max_worker_processes = 48
  • max_parallel_workers = 48
  • max_parallel_workers_per_gather = 12
  • wal_buffers = 16 MB
  • min_wal_size = 1GB
  • max_wal_size = 2GB

Esos probablemente no sean perfectos, y están definidos en parte debido a la documentación que encontré y en parte por probar y fallar en una gran solicitud. Pero si no configuró su postgresql en absoluto (dijo que comenzó), debería marcar una gran diferencia en el rendimiento para una solicitud grande (la suya no es tan grande, pero debería tener un impacto). Los datos de geometría suelen ser grandes, por lo que deberían necesitar más espacio que el uso típico de postgresql. Además, si puede, asegúrese de poner sus datos en SSD, también puede tener un gran impacto.

EDITAR

Acabo de volver a leer su solicitud, y realmente no entiendo por qué necesita todos los puntos dentro de X metros si después solo mantiene una línea por numacc. O no hizo toda la consulta o realmente solo necesita un punto. Así que lo reescribo en caso de que lo que realmente quisieras fuera llegar al punto más cercano. Usé CTE MATERIALIZADO, que crea una tabla temporal para cada paso, a veces realmente puede mejorar el rendimiento, por lo que en caso de que quisiera obtener todos los puntos y no solo el vecino más cercano, puede intentar ejecutarlo como está quitando el PEDIDO BY y LIMIT en INNER JOIN LATERAL al final. Y, por supuesto, aquí limito la búsqueda con ST_DWithin, pero si desea un verdadero vecino más cercano, puede eliminar este DONDE:

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;