"Ow Mun Heng" <Ow.Mun.Heng@xxxxxxx> writes: > Same query, executed twice, once using seqscan enabled and the other > with it disabled. Difference is nearly night and day. > > > 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. Perhaps 2 or even 1.5 or so. But don't do it based on a single query under testing conditions, use a wide variety of queries under production conditions. > QUERY PLAN > -------------------------------------------------------------------------------------------------- > Limit (cost=4430.53..50173.70 rows=1000 width=47) (actual time=21832.092..43771.536 rows=228 loops=1) > -> Hash Join (cost=4430.53..260866.77 rows=5606 width=47) (actual time=21832.088..43770.927 rows=228 loops=1) The difference between the predicted and actual rows is suspicious. let's look lower down to see where it comes from. > Hash Cond: ((trz.number)::text = (drv.number)::text) > -> Seq Scan on zone trz (cost=0.00..233254.27 rows=6148222 width=39) (actual time=22.807..31891.591 rows=6181910 loops=1) > Filter: ((zn_2 <> 0::numeric) AND (zn_2 IS NOT NULL)) This part looks ok 615k versus 618k is pretty good. > -> 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. > -> 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. Incidentally the way this is written makes me wonder what data type "code" is defined as. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(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
