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

[Stephen Woodbridge]

Try setting:

set enable_seqscan=false;

and then try your explain again. I have run into this before with other 
queries and this solved the problem, but I'm sure there is a better way 
of dealing with this and I'm not sure what causes it.

-Steve

On 4/23/2012 11:08 PM, pcreso at pcreso.com wrote:
> 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