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='') and pcpar 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
>
> 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='') and pcpar 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

Well it seems to be off with the number of records, but not in the
direction that would generally make index scan better.  How big
is the table and what does vacuum verbose give?  I don't understand why it
isn't doing a sort and scan on pcbsref, how many of the rows have
pcbsref='test'?

In general, an index on (pcbsref, pcseqnbr) might give the best results
(as it could do that check and the ordering with one index).

> > Also, what version are you running?
>
> Sorry forgot to mention it is: 7.2.3

You should probably move to at least 7.2.4, IIRC there was a serious
bug fixed.

> So why do I have to force seqscan off to get better behaviour?
> This wouldn't be practical to do within our code.

That was more to get its idea for the cost of the index scan.
As a short term thing, if you havent played with random_page_cost,
I'd suggest lowering it from the default 4 to say 3 or 2 and see
what plan it gives you.