ORDER BY costs

Hi,

We´ve a SELECT that even without ORDER BY is returning the rows in the order that we liked but when we add the ORDER BY clause the runtime and costs are much bigger.

We have to use ORDER BY otherwise in some future postgresql version probably it will not return in the correct order anymore.

But if we use ORDER BY it´s too much expensive... is there a way to have the same costs and runtime but with the ORDER BY clause?

Why is not the planner using the access plan builded for the "without order by" select even if we use the order by clause? The results are both the same...

Postgresql version: 8.0.3

Without order by:
explain analyze
SELECT * FROM iparq.ARRIPT
where
(ANOCALC = 2005
and CADASTRO = 19
and CODVENCTO = 00
and PARCELA >= 00 )
or
(ANOCALC = 2005
and CADASTRO = 19
and CODVENCTO > 00 )
or
(ANOCALC = 2005
and CADASTRO > 19 )
or
(ANOCALC > 2005 );
Index Scan using pk_arript, pk_arript, pk_arript, pk_arript on arript (cost=0.00..122255.35 rows=146602 width=897) (actual time=9.303..1609.987 rows=167710 loops=1)
Index Cond: (((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto = 0::numeric) AND (parcela >= 0::numeric)) OR ((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto > 0::numeric)) OR ((anocalc = 2005::numeric) AND (cadastro > 19::numeric)) OR (anocalc > 2005::numeric))
Total runtime: 1712.456 ms
(3 rows)


With order by:
explain analyze
SELECT * FROM iparq.ARRIPT
where
(ANOCALC = 2005
and CADASTRO = 19
and CODVENCTO = 00
and PARCELA >= 00 )
or
(ANOCALC = 2005
and CADASTRO = 19
and CODVENCTO > 00 )
or
(ANOCALC = 2005
and CADASTRO > 19 )
or
(ANOCALC > 2005 )
order by ANOCALC asc, CADASTRO asc, CODVENCTO asc, PARCELA asc;
Sort (cost=201296.59..201663.10 rows=146602 width=897) (actual time=9752.555..10342.363 rows=167710 loops=1)
Sort Key: anocalc, cadastro, codvencto, parcela
-> Index Scan using pk_arript, pk_arript, pk_arript, pk_arript on arript (cost=0.00..122255.35 rows=146602 width=897) (actual time=0.402..1425.085 rows=167710 loops=1)
Index Cond: (((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto = 0::numeric) AND (parcela >= 0::numeric)) OR ((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto > 0::numeric)) OR ((anocalc = 2005::numeric) AND (cadastro > 19::numeric)) OR (anocalc > 2005::numeric))
Total runtime: 10568.290 ms
(5 rows)

Table definition:
Table "iparq.arript"
Column | Type | Modifiers
-------------------+-----------------------+-----------
anocalc | numeric(4,0) | not null
cadastro | numeric(8,0) | not null
codvencto | numeric(2,0) | not null
parcela | numeric(2,0) | not null
inscimob | character varying(18) | not null
codvencto2 | numeric(2,0) | not null
parcela2 | numeric(2,0) | not null
codpropr | numeric(10,0) | not null
dtaven | numeric(8,0) | not null
anocalc2 | numeric(4,0) |
...
...
Indexes:
"pk_arript" PRIMARY KEY, btree (anocalc, cadastro, codvencto, parcela)
"iarchave04" UNIQUE, btree (cadastro, anocalc, codvencto, parcela)
"iarchave02" btree (inscimob, anocalc, codvencto2, parcela2)
"iarchave03" btree (codpropr, dtaven)
"iarchave05" btree (anocalc, inscimob, codvencto2, parcela2)

Best regards and thank you very much in advance,

Carlos Benkendorf

---------------------------------
Yahoo! doce lar. Faça do Yahoo! sua homepage.