Spatial index not used in view which union several tables

Question

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?

Answer 1

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.