Re: This table won't use INDEX until I DUMP/RESTORE it ?

On Thu, 21 Aug 2003, Chris Miles wrote:

>
> Stephan Szabo wrote:
> > On Tue, 19 Aug 2003, Chris Miles wrote:
> >>I have a DB that appears to perform badly.  A test of one table
> >>with one of the typical queries gives me a query plan indicating
> >>a Seq Scan;
> >
> > What does it give if you set enable_seqscan=off; before the explain?
>
> ok, with enable_seqscan=off it gives an index scan for the explain.
>
> > And what does explain analyze give both with seqscan disabled and enabled?
>
> test=# set enable_seqscan=on;
> SET VARIABLE
> test=# EXPLAIN ANALYSE select pcref,pcseqnbr from catrecrel where pcbsref='test'  and (pccaref is null or pccaref='')
andpcpar is null order by pcseqnbr ; 
> NOTICE:  QUERY PLAN:
>
> Sort  (cost=38288.75..38288.75 rows=4 width=58) (actual time=7271.47..7272.59 rows=743 loops=1)
>   ->  Seq Scan on catrecrel  (cost=0.00..38288.70 rows=4 width=58) (actual time=0.10..7266.19 rows=743 loops=1)
> Total runtime: 7273.92 msec

Note that Postgresql "thinks" the cost of a seq scan is about 38288
here...

> EXPLAIN
> test=# set enable_seqscan=off;
> SET VARIABLE
> test=# EXPLAIN ANALYSE select pcref,pcseqnbr from catrecrel where pcbsref='test'  and (pccaref is null or pccaref='')
andpcpar is null order by pcseqnbr ; 
> NOTICE:  QUERY PLAN:
>
> Index Scan using ind_pcseqnbr on catrecrel  (cost=0.00..38390.48 rows=4 width=58) (actual time=0.28..229.19 rows=743
loops=1)
> Total runtime: 230.53 msec

and here is "thinks" the cost of an index scan is 38390.

however, given that the seq scan is using 7.2 seconds, and the index scan
is .23 seconds, it would appear the query analyzer is making a bad choice.

> > Also, what version are you running?
>
> Sorry forgot to mention it is: 7.2.3
>
> So why do I have to force seqscan off to get better behaviour?
> This wouldn't be practical to do within our code.

No, enable_seqscan=off is a kind of hammer to the forebrain method of
forcing postgresql to do what you want.  It is not something you should
have to use in production, just troubleshooting.

> Is a newer version, such as 7.3.4, much smarter with query planning?

7.3.4 was mostly bug fixes, but there might be a tweak on the planner
somewhere.

More importantly, the problem here is likely that your machine has very
fast random I/O and more memory than postgresql realizes, so it is making
choices as though it were on a slower machine (I/O wise) with less memory.

You should set effective_cache_size appropriately (very approximate
method, take the size of the buffer + kernel cache and divide by 8192 for
effective_cache_size.)

Then you might want to play around with random_page_cost and the
cpu*tuple*cost variables in postgresql.conf.

Note that you can set these from a psql session for testing to see when
they kick in.  On fast boxes with lots of ram, it's not uncommon for
random page cost to need a setting of between 1 and 2 to get the planner
to behave properly.  Going any lower than 1 means that likely the cpu*cost
vars aren't set right, or that you've found a corner case.

Your query doesn't really look like a corner case, more like the planner
is just missing the index scan by a few points, so in this case, adjusting
the cpu*cost vars and lowering random_page_cost should do it.