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
