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.