ORDER BY costs

[Carlos Benkendorf <carlosbenkendorf@xxxxxxxxxxxx>]

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
a nd 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 COD VENCTO =  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.