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

[Greg Williamson]

Could you post the results of an EXPLAIN ANALYZE ? At least for the 5 table version -- the 6 table version may take too long.


Also: postgres config settings and version might help shed some light.


Greg WIlliamson
>________________________________
> From: "pcreso at pcreso.com" <pcreso at pcreso.com>
>To: PostGIS Users Discussion <postgis-users at postgis.refractions.net> 
>Sent: Monday, April 23, 2012 8:08 PM
>Subject: [postgis-users] help wanted with performance/GIST index question
> 
>
>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)
>
> 
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>