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