[postgis-users] help wanted with performance/GIST index question

[pcreso at pcreso.com]

I'm doing some left outer spatial joins using the && operator, which should be relatively quick. 

When I join across 5 tables, the result is fast enough, & explain shows index scans are being applied to the spatial tables. When I add another table, explain shows a seq scan is used, despite the table having a suitable index, and given 90,000,000 records, it is more than somewhat sluggish.

Any advice??


The query I'm running (with the extra table  & the explain results are:

explain select s.ogc_fid,
       s.trip::integer as trip,
       s.station::varchar(20) as station,
       avg(d.depth)::decimal(6,2) as avg_depth,
       avg(t.botemp)::decimal(8,6) as avg_botemp,
       avg(a.aou)::decimal(8,6) as avg_aou,
       avg(o.boto2sat)::decimal(8,6) as avg_o2
--       avg(p.botpho)::decimal(8,6) as avg_pho
from allstations s
left outer join depths d on d.geom && buffer(s.geom2,125)
left outer join botemp t on t.geom && buffer(s.geom2,125)
left outer join aou a on a.geom && buffer(s.geom2,125)
left outer join boto2sat o on o.geom && buffer(s.geom2,125)
--left outer join botpho p on p.geom && buffer(s.geom2,125)
where s.ogc_fid =1 
group by s.ogc_fid,
         s.trip,
         s.station
order by s.ogc_fid;


Query plan 1 
--------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=210102370480.36..210102370480.40 rows=1 width=293)
   ->  Nested Loop Left Join  (cost=38.17..209249565267.93 rows=48731726425 width=293)
         ->  Nested Loop Left Join  (cost=0.75..452592989.59 rows=103730722 width=371)
               ->  Nested Loop Left Join  (cost=0.50..969144.98 rows=220802 width=365)
                     ->  Nested Loop Left Join  (cost=0.25..2066.85 rows=470 width=357)
                           ->  Index Scan using allstations_pk on allstations s  (cost=0.00..8.28 rows=1 width=349)
                                 Index Cond: (ogc_fid = 1)
                           ->  Index Scan using aou_point_idx on aou a  (cost=0.25..1935.19 rows=470 width=92)
                                 Index Cond: (geom && buffer(s.geom2, 125::double precision))
                     ->  Index Scan using botemp_point_idx on botemp t  (cost=0.25..1934.24 rows=470 width=92)
                           Index Cond: (geom && buffer(s.geom2, 125::double precision))
               ->  Index Scan using boto2sat_point_idx on boto2sat o  (cost=0.25..1922.00 rows=470 width=90)
                     Index Cond: (geom && buffer(s.geom2, 125::double precision))
         ->  Bitmap Heap Scan on depths d  (cost=37.42..1889.50 rows=470 width=90)
               Recheck Cond: (geom && buffer(s.geom2, 125::double precision))
               ->  Bitmap Index Scan on depth_point_idx  (cost=0.00..37.30 rows=470 width=0)
                     Index Cond: (geom && buffer(s.geom2, 125::double precision))
(17 rows)


Query plan 2 (extra table causes Seq scan)
--------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=98761396253872.30..98761396253872.34 rows=1 width=301)
   ->  Nested Loop Left Join  (cost=38.17..98303522035976.80 rows=22893710894775 width=301)
         ->  Nested Loop Left Join  (cost=0.75..212650447941.07 rows=48731726425 width=379)
               ->  Nested Loop Left Join  (cost=0.50..481774313.60 rows=103730722 width=373)
                     ->  Nested Loop Left Join  (cost=0.25..27449239.14 rows=220802 width=365)
                           ->  Nested Loop Left Join  (cost=0.00..26481714.34 rows=470 width=357)
                                 Join Filter: (p.geom && buffer(s.geom2, 125::double precision))
                                 ->  Index Scan using allstations_pk on allstations s  (cost=0.00..8.28 rows=1 width=349)
                                       Index Cond: (ogc_fid = 1)
                                 ->  Seq Scan on botpho p  (cost=0.00..1817695.36 rows=93958136 width=92)
                           ->  Index Scan using aou_point_idx on aou a  (cost=0.25..1935.19 rows=470 width=92)
                                 Index Cond: (geom && buffer(s.geom2, 125::double precision))
                     ->  Index Scan using botemp_point_idx on botemp t  (cost=0.25..1934.24 rows=470 width=92)
                           Index Cond: (geom && buffer(s.geom2, 125::double precision))
               ->  Index Scan using boto2sat_point_idx on boto2sat o  (cost=0.25..1922.00 rows=470 width=90)
                     Index Cond: (geom && buffer(s.geom2, 125::double precision))
         ->  Bitmap Heap Scan on depths d  (cost=37.42..1889.50 rows=470 width=90)
               Recheck Cond: (geom && buffer(s.geom2, 125::double precision))
               ->  Bitmap Index Scan on depth_point_idx  (cost=0.00..37.30 rows=470 width=0)
                     Index Cond: (geom && buffer(s.geom2, 125::double precision))
(20 rows)

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120423/8453b5c4/attachment.html>