Re: Instances where enable_seqscan = false is good

"Ow Mun Heng" <Ow(dot)Mun(dot)Heng(at)wdc(dot)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=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