On Mon, 2007-09-03 at 11:31 +0100, Gregory Stark wrote:
> "Ow Mun Heng" <Ow.Mun.Heng@wdc.com> writes:
> >
> > How can I persuade PG to use the index w/o resorting to setting seqscan
> > = false
>
> The usual knob to fiddle with is random_page_cost. If your database fits
> mostly in memory you may want to turn it down from the default of 4 to
> something closer to 1.
I tried down to 0.4 before it resorted to using the index. The DB
shouldn't fit into memory (I think) that table alone has ~8million rows
at ~1.5G size
> > -> Hash (cost=4414.39..4414.39 rows=1291 width=24) (actual time=171.911..171.911 rows=12591 loops=1)
>
> Ah, this is off by an order of magnitude, that's bad.
>
having said so, still don't understand why..
> > -> 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.
> Incidentally the way this is written makes me wonder what data type "code" is
> defined as.
code is defined as varchar(5) data type. I'm changing all of the normal
char(n) to varchar(n) columns..
BTW, thanks for helping. Not using seq scans does really make a huge
difference as you can clearly see from the timing.
Total runtime: 43772.045 ms
Total runtime: 553.964 ms