Intersección lenta PostGIS

Pregunta:

Otro problema de intersección. Estoy trabajando con la base de datos OSM en PostGIS. Ahora tratando de contar todos los edificios que caen en un cuadro delimitador de países.

Esta es mi consulta (bbox para Alemania).

SELECT
  COUNT(osm_id) AS num
FROM
  planet_osm_polygon
WHERE
  tags->'building' IS NOT NULL AND
  way && ST_Transform(ST_MakeEnvelope(12.8719,52.3700,13.9382,52.6646, 4326), 3857)

No importa si filtro por edificios o no, la consulta es lenta. Mi expectativa estaría por debajo de los 10 segundos, pero la realidad es de unos 100 segundos.

Este es mi plan de consulta:

Aggregate  (cost=500537.88..500537.89 rows=1 width=8)
 -> Bitmap Heap Scan on planet_osm_polygon  (cost=7691.95..500254.94 rows=113173 width=8)
    Recheck Cond: (way && '0103000020110F0000010000000500000076B9815A3DDD354137FCF32B5A325A4176B9815A3DDD3541133B4058FB665A410E689A53E9AC3741133B4058FB665A410E689A53E9AC374137FCF32B5A325A4176B9815A3DDD354137FCF32B5A325A41'::geometry)
    Filter: ((tags -> 'building'::text) IS NOT NULL)
     -> Bitmap Index Scan on planet_osm_polygon_index  (cost=0.00..7663.66 rows=133748 width=0)
        Index Cond: (way && '0103000020110F0000010000000500000076B9815A3DDD354137FCF32B5A325A4176B9815A3DDD3541133B4058FB665A410E689A53E9AC3741133B4058FB665A410E689A53E9AC374137FCF32B5A325A4176B9815A3DDD354137FCF32B5A325A41'::geometry)

Eventualmente, esto debería coincidir con otra tabla con más de 200 polígonos de países.

Respuesta:

Los comentarios ya parecían ayudar, lo que significa que el análisis de vacío debería funcionar en la mayoría de las situaciones. Sin embargo, aquí hay un método que redujo el tiempo de ejecución de la consulta en un factor 2:

create table buildings as 
select osm_id,building,way from planet_osm_polygon 
where building is not null;
create index on buildings using gist(way);
\timing
Timing is on.
select count(osm_id) as num from planet_osm_polygon 
where way && st_makeenvelope(680000.0,6400000.0,710000.0,6490000.0,3857)
and building is not null;
 num  
------
 8102
(1 line)
Time: 12,594 ms
select count(osm_id) from buildings where 
way && st_makeenvelope(680000.0,6400000.0,710000.0,6490000.0,3857);
 num  
------
 8102
(1 line)
Time: 6,102 ms

Aquí hay un plan de consulta para la primera consulta:

 Aggregate  (cost=6660.68..6660.69 rows=1 width=8)
   ->  Bitmap Heap Scan on planet_osm_polygon  (cost=1086.09..6636.39 rows=9715 width=8)
         Recheck Cond: (way && '0103000020110F000001000000050000000000000080C0244100000000006A58410000000080C0244100000000E4C1584100000000E0AA254100000000E4C1584100000000E0AA254100000000006A58410000000080C0244100000000006A5841'::geometry)
         Filter: (building IS NOT NULL)
         ->  Bitmap Index Scan on planet_osm_polygon_index  (cost=0.00..1083.66 rows=13784 width=0)
               Index Cond: (way && '0103000020110F000001000000050000000000000080C0244100000000006A58410000000080C0244100000000E4C1584100000000E0AA254100000000E4C1584100000000E0AA254100000000006A58410000000080C0244100000000006A5841'::geometry)

Aquí hay un plan de consulta para la segunda consulta:

 Aggregate  (cost=3037.33..3037.34 rows=1 width=8)
   ->  Bitmap Heap Scan on building  (cost=287.66..3016.88 rows=8178 width=8)
         Recheck Cond: (way && '0103000020110F000001000000050000000000000080C0244100000000006A58410000000080C0244100000000E4C1584100000000E0AA254100000000E4C1584100000000E0AA254100000000006A58410000000080C0244100000000006A5841'::geometry)
         ->  Bitmap Index Scan on building_way_idx  (cost=0.00..285.61 rows=8178 width=0)
               Index Cond: (way && '0103000020110F000001000000050000000000000080C0244100000000006A58410000000080C0244100000000E4C1584100000000E0AA254100000000E4C1584100000000E0AA254100000000006A58410000000080C0244100000000006A5841'::geometry)

Como puede ver, el truco consiste en preprocesar los datos tanto como sea posible para obtener el rendimiento de la consulta y obligar al planificador de consultas a hacer exactamente lo que usted desea. En este caso, se omitió la condición de filtro "el edificio no es nulo". Además, los costos estimados para los escaneos agregados y del montón de mapas de bits se redujeron a la mitad.

Para las pruebas, solo usé datos de Luxemburgo , mientras que mi bbox era más pequeño que la extensión de Luxemburgo, por lo tanto, los resultados para conjuntos de datos más grandes pueden diferir.

Leave a Comment

Your email address will not be published.

Scroll to Top

istanbul avukat

-

web tasarım