TransWikia.com

PostGIS: Speed up intersect by filtering first

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;

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP