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

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

EXPLAIN

> 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.

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

Regards,
Chris.

--
Chris Miles
http://chrismiles.info/