Re: Instances where enable_seqscan = false is good - Mailing list pgsql-general

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.Mun.Heng@wdc.com> writes:

> > >                ->  Bitmap Heap Scan on drv  (cost=30.44..4414.39 rows=1291 width=24) (actual time=62.980..142.594
rows=12591loops=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.199rows=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=6222984loops=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=12591loops=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.180rows=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=6222984loops=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=12591loops=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.436rows=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..