Geographic Information Systems Asked by Baschdl on December 30, 2020
I have a table with 20 million polygons where queries take some time (with an gist index on the geometries). I thought I could speed up the queries by first filtering on an additional id (selectivity: 1/600).
But the queries take basically the same time despite also creating an additional btree_gist index on (id, geometry)
. The problem is that PostgreSQL runs my filter query and the intersect parallel instead of using the filter query first (Maybe it doesn’t make sense? Is a selectivity of 1/600 too small? Does my (id, geometry)
-index not work?)
Original query to calculate vector tiles:
WITH bounds as (...),
mvtgeom AS (
SELECT ST_AsMVTGeom(ST_Transform(t.geom, 3857), bounds.b2d) AS geom,
FROM data t, bounds
WHERE ST_Intersects(t.geom, ST_Transform(bounds.geom, 32632)))
SELECT ST_AsMVT(mvtgeom.*) FROM mvtgeom;
Query with filter:
WITH bounds as (...),
WITH filter as (SELECT id FROM ... (WHERE x < n OR x > m) AND (y > n OR y < m)),
mvtgeom AS (
SELECT ST_AsMVTGeom(ST_Transform(table.geom, 3857), bounds.b2d) AS geom,
FROM table, bounds, filter
WHERE ST_Intersects(table.geom, ST_Transform(bounds.geom, 32632)))
AND table.id = filter.id
SELECT ST_AsMVT(mvtgeom.*) FROM mvtgeom;
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP