Search Postgresql Archives

Re: Instances where enable_seqscan = false is good

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxx> 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..

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux