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: "pcr...@pcreso.com" <pcr...@pcreso.com> >To: PostGIS Users Discussion <postgis-users@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@postgis.refractions.net >http://postgis.refractions.net/mailman/listinfo/postgis-users > > > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
