|
I have the following query: SELECT * FROM polygons WHERE zoomlevel <= {zoom} AND st_intersects(way,{tileboundary}) For any given tile according to the openstreetmap tiles. So zoomlevel is from 0..14 and the number of polygons in each level is roughly exponential. Postgres doc (https://www.postgresql.org/docs/current/indexes-multicolumn.html) states that
“A GiST index will be relatively ineffective if its first column has only a few distinct values, even if there are many distinct values in additional columns.” So I am trying to figure out the difference between the column order in the index. Created following indices: CREATE INDEX polygon_minzoom_geo ON polygons USING gist (minzoom,way) CREATE INDEX polygon_minzoom_geo2 ON polygons USING gist (way,minzoom) And did a test for a zoom=9 tile: SELECT * FROM polygons WHERE zoomlevel <= 9 AND st_intersects(way,'SRID=3857;POLYGON((547900 6653078,547900 6574807,626172 6574807,626172 6653078,547900 6653078))') After running that query with either one of the indices disabled (BEGIN; DROP INDEX etc) I get the following explain results: Using (minzoom,way): Result (cost=0.42..228992.47 rows=229000 width=113) (actual time=149.483..1471.819 rows=42463 loops=1) Buffers: shared hit=352653 -> ProjectSet (cost=0.42..4572.47 rows=229000 width=88) (actual time=149.461..1234.048 rows=42463 loops=1) Buffers: shared hit=274281 -> Index Scan using polygon_minzoom_geo on polygons (cost=0.42..3283.20 rows=229 width=264) (actual time=149.409..955.849 rows=42463 loops=1) Index Cond: ((minzoom <= 9) AND (way && '0103000020110F00000100000005000000BE5C7A45F81B23412C8346BC25615941BE5C7A45F81B2341B99930DBB51459412611CB3C79B82041B99930DBB51459412611CB3C79B820412C8346BC25615941BE5C7A45F81B23412C8346BC25615941'::geometry)) Filter: ((minzoom <= 9) AND (way && '0103000020110F00000100000005000000BE5C7A45F81B23412C8346BC25615941BE5C7A45F81B2341B99930DBB51459412611CB3C79B82041B99930DBB51459412611CB3C79B820412C8346BC25615941BE5C7A45F81B23412C8346BC25615941'::geometry)) Rows Removed by Filter: 345 Buffers: shared hit=274025 Planning Time: 7.220 ms Execution Time: 1494.267 ms Using (way,minzoom): Result (cost=0.42..228992.47 rows=229000 width=113) (actual time=178.747..1715.135 rows=42463 loops=1) Buffers: shared hit=350570 -> ProjectSet (cost=0.42..4572.47 rows=229000 width=88) (actual time=178.731..1436.764 rows=42463 loops=1) Buffers: shared hit=272198 -> Index Scan using polygon_minzoom_geo2 on polygons (cost=0.42..3283.20 rows=229 width=264) (actual time=178.683..1118.691 rows=42463 loops=1) Index Cond: ((way && '0103000020110F00000100000005000000BE5C7A45F81B23412C8346BC25615941BE5C7A45F81B2341B99930DBB51459412611CB3C79B82041B99930DBB51459412611CB3C79B820412C8346BC25615941BE5C7A45F81B23412C8346BC25615941'::geometry)
AND (minzoom <= 9)) Filter: ((way && '0103000020110F00000100000005000000BE5C7A45F81B23412C8346BC25615941BE5C7A45F81B2341B99930DBB51459412611CB3C79B82041B99930DBB51459412611CB3C79B820412C8346BC25615941BE5C7A45F81B23412C8346BC25615941'::geometry)
AND (minzoom <= 9)) Rows Removed by Filter: 345 Buffers: shared hit=271942 Planning Time: 9.427 ms Execution Time: 1742.729 ms So all in all not really a big difference. Is this situation somehow special and thus the remark in the documentation not applicable here, or am I missing something in the analysis that would show up the difference? Paul P.S. when replying, please include me too |
