On Mon, 2007-09-03 at 11:31 +0100, Gregory Stark wrote: > "Ow Mun Heng" <Ow.Mun.Heng@xxxxxxx> 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=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. > 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
