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/
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
