Re: Instances where enable_seqscan = false is good

On Tue, 2007-09-04 at 10:06 +0800, Ow Mun Heng wrote:
> On Mon, 2007-09-03 at 11:31 +0100, Gregory Stark wrote:
> > "Ow Mun Heng" <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> writes:

> > > -> Bitmap Heap Scan on drv (cost=30.44..4414.39 rows=1291 width=24) (actual time=62.980..142.594 rows=12591 loops=1)
> > > Recheck Cond: ((code)::text = 'NRN15'::text)
> > > -> Bitmap Index Scan on idx_drv (cost=0.00..30.11 rows=1291 width=0) (actual time=62.199..62.199 rows=12649 loops=1)
> > > Index Cond: ((code)::text = 'NRN15'::text)
> >
> > So you might want to increase the statistics target for the "code" column.
> Err.. how come? (newbie) it's scanning the index there. What's bad is
> that it's using Seq_scans on the "zone" table.

Code is now increased to Stat level of 100

----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=25113.04..30733.57 rows=1000 width=47) (actual time=19666.832..39961.032 rows=228 loops=1)
-> Hash Join (cost=25113.04..324620.08 rows=53288 width=47) (actual time=19666.826..39960.437 rows=228 loops=1)
Hash Cond: ((trz.number)::text = (drv.number)::text)
-> Seq Scan on zone trz (cost=0.00..234363.75 rows=6394431 width=39) (actual time=17.635..29164.929 rows=6222984 loops=1)
Filter: ((zn_2 <> 0::numeric) AND (zn_2 IS NOT NULL))
-> Hash (cost=24965.72..24965.72 rows=11785 width=24) (actual time=215.851..215.851 rows=12591 loops=1)
-> Bitmap Heap Scan on drv (cost=243.76..24965.72 rows=11785 width=24) (actual time=50.910..188.894 rows=12591 loops=1)
Recheck Cond: ((code)::text = 'NRN15'::text)
-> Bitmap Index Scan on idx_drv (cost=0.00..240.82 rows=11785 width=0) (actual time=49.180..49.180 rows=12591 loops=1)
Index Cond: ((code)::text = 'NRN15'::text)
Total runtime: 39961.703 ms

Does seem to be slightly better (from 43772ms)

trz.number stat level increased to 100 & code to 100

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=25113.04..30733.57 rows=1000 width=47) (actual time=22152.398..42430.820 rows=228 loops=1)
-> Hash Join (cost=25113.04..324620.08 rows=53288 width=47) (actual time=22152.392..42430.212 rows=228 loops=1)
Hash Cond: ((trz.number)::text = (drv.number)::text)
-> Seq Scan on zone trz (cost=0.00..234363.75 rows=6394431 width=39) (actual time=11.840..28808.222 rows=6222984 loops=1)
Filter: ((zn_2 <> 0::numeric) AND (zn_2 IS NOT NULL))
-> Hash (cost=24965.72..24965.72 rows=11785 width=24) (actual time=2646.652..2646.652 rows=12591 loops=1)
-> Bitmap Heap Scan on drv (cost=243.76..24965.72 rows=11785 width=24) (actual time=50.628..2600.132 rows=12591 loops=1)
Recheck Cond: ((code)::text = 'NRN15'::text)
-> Bitmap Index Scan on idx_drvl (cost=0.00..240.82 rows=11785 width=0) (actual time=38.436..38.436 rows=12591 loops=1)
Index Cond: ((code)::text = 'NRN15'::text)
Total runtime: 42431.358 ms

hmm..not much difference..

What else can be done?? Many Thanks..