From: Carlos Benkendorf <carlosbenkendorf@yahoo.com.br>
To: pgsql-performance@postgresql.org
Subject: ORDER BY costs
Date: Wed, 21 Dec 2005 18:16:01 +0000 (GMT)
Message-ID: <20051221181601.45239.qmail@web35508.mail.mud.yahoo.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: carlosbenkendorf@yahoo.com.br
Subject: Re: ORDER BY costs
In-Reply-To: <20051221181601.45239.qmail@web35508.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox