4

I have multiple spatial tables each of which I have created gist index, and query for each table will cost 10-200ms as shown in the following example, the spatial index can be used.

Example 1(with table poi):

explain analyze 
WITH envelope AS (
    select ST_MakeEnvelope(60.236835,13.635821,60.239667,13.638654, 4326) as geom
) 
SELECT type_code, ST_Intersection(tb.shape, envelope.geom) as shape 
   FROM poi as tb 
   JOIN envelope 
     ON tb.shape && envelope.geom where zoom<21

Result:

"Nested Loop  (cost=10.12..1182.46 rows=1218 width=71) (actual time=0.282..0.678 rows=10 loops=1)"
"  CTE envelope"
"    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)"
"  ->  CTE Scan on envelope  (cost=0.00..0.02 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on poi tb  (cost=10.11..876.71 rows=122 width=39) (actual time=0.102..0.132 rows=10 loops=1)"
"        Recheck Cond: (shape && envelope.geom)"
"        Filter: (zoom < 21)"
"        Rows Removed by Filter: 2"
"        Heap Blocks: exact=9"
"        ->  Bitmap Index Scan on poi_shape_index  (cost=0.00..10.08 rows=222 width=0) (actual time=0.089..0.089 rows=12 loops=1)"
"              Index Cond: (shape && envelope.geom)"
"Planning time: 0.261 ms"
"Execution time: 0.728 ms"

=========================================================

Example 2(with table building_polygon):

explain analyze 
WITH envelope AS (
    select ST_MakeEnvelope(60.236835,13.635821,60.239667,13.638654, 4326) as geom
) 
SELECT type_code, ST_Intersection(tb.shape, envelope.geom) as shape 
FROM building_polygon as tb 
JOIN envelope 
ON tb.shape && envelope.geom where zoom<21

Result:

"Nested Loop  (cost=87.95..16122.09 rows=25185 width=211) (actual time=0.486..4.666 rows=21 loops=1)"
"  CTE envelope"
"    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)"
"  ->  CTE Scan on envelope  (cost=0.00..0.02 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on building_polygon tb  (cost=87.94..9800.63 rows=2518 width=179) (actual time=0.170..0.221 rows=21 loops=1)"
"        Recheck Cond: (shape && envelope.geom)"
"        Filter: (zoom < 21)"
"        Heap Blocks: exact=5"
"        ->  Bitmap Index Scan on sidx_5614839_13  (cost=0.00..87.31 rows=2519 width=0) (actual time=0.156..0.156 rows=21 loops=1)"
"              Index Cond: (shape && envelope.geom)"
"Planning time: 0.329 ms"
"Execution time: 4.725 ms"

==============================================================

However since I have 20+ tables, and I have to query data across these tables, so I created a view to union the required tables with required fields:

create view view_geom as
    select name,null::character as type_code,shape,zoom,'poi'::character as layername from poi UNION ALL 
    select null as name,type_code,shape,zoom,'building_polygon' as layername from building_polygon 

Then I query from this view using the same query conditions:

explain analyze 
WITH envelope AS (
  select ST_MakeEnvelope(60.236835,13.635821,60.239667,13.638654, 4326) as geom
) 
SELECT type_code, ST_Intersection(tb.shape, envelope.geom) as shape 
FROM view_geom as tb 
JOIN envelope 
ON tb.shape && envelope.geom where zoom<21

This will cost 26 seconds. Unacceptable.

The result:

"Nested Loop  (cost=0.01..2378021.23 rows=26403 width=96) (actual time=760.789..26741.084 rows=31 loops=1)"
"  Join Filter: (poi.shape && envelope.geom)"
"  Rows Removed by Join Filter: 26391319"
"  CTE envelope"
"    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)"
"  ->  CTE Scan on envelope  (cost=0.00..0.02 rows=1 width=32) (actual time=0.004..0.006 rows=1 loops=1)"
"  ->  Append  (cost=0.00..1777363.84 rows=26402516 width=182) (actual time=0.008..19095.282 rows=26391350 loops=1)"
"        ->  Seq Scan on poi  (cost=0.00..188742.36 rows=1217827 width=70) (actual time=0.008..1479.618 rows=1206556 loops=1)"
"              Filter: (zoom < 21)"
"              Rows Removed by Filter: 1012113"
"        ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..1576443.21 rows=25184689 width=187) (actual time=0.042..15280.782 rows=25184794 loops=1)"
"              ->  Seq Scan on building_polygon  (cost=0.00..1324596.32 rows=25184689 width=187) (actual time=0.040..11278.918 rows=25184794 loops=1)"
"                    Filter: (zoom < 21)"
"                    Rows Removed by Filter: 3254"
"Planning time: 0.232 ms"
"Execution time: 26741.143 ms"

Seems like the spatial index are not used at all. What's going on? How to fix that?

2

It could be a couple of things:

  • Most likely, Postgres is not able to dynamically route (pull-down) spatial conditions from top-level join to individual tables behind unioned view.

  • It also could be caused by your CTE clause. CTE acts as an optimization fence. In this case, it should not be an issue, but with combination with join and unioned view, who knows...

  • Postgres is not able to pick the right execution plan duo to inaccurate statistics or missing indexes

To resolve this, firstly analyze your tables and try to remove CTE join and inline that geometry condition. Something like this:

SELECT type_code, ST_Intersection(tb.shape, ST_MakeEnvelope(60.236835,13.635821,60.239667,13.638654, 4326)) as shape 
FROM view_geom as tb 
where tb.shape && ST_MakeEnvelope(60.236835,13.635821,60.239667,13.638654, 4326)
and zoom < 21

You can also try to add an index on the zoom field of target tables. But if Postgres recognize that geometry condition can be pulled down to individual tables, then it should be OK even without that index.

EDIT: Related topic on Stackoverflow here.

| improve this answer | |
  • If inline the geometry in the sql, I have to call the ST_MakeEnvelope twice like this: SELECT type_code, ST_Intersection(tb.shape, ST_MakeEnvelope(60.236835,13.635821,60.239667,13.638654, 4326)) as shape FROM view_geom as tb where tb.shape && ST_MakeEnvelope(60.236835,13.635821,60.239667,13.638654, 4326) envelope and zoom < 21. I am not sure if this will cause any problem> – giser Oct 17 '19 at 8:44
  • Yes, that's true, but if it will work...Constructing constant geometry multiple times is definitely faster than scanning all the data ;) I never had a problem with this. – DavidP Oct 17 '19 at 8:53
  • Thanks, after remove the CTE cause, the query from the view cost 200ms. – giser Oct 17 '19 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.