"Ow Mun Heng" <Ow.Mun.Heng@wdc.com> 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=6181910loops=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=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.
Incidentally the way this is written makes me wonder what data type "code" is
defined as.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com