X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id BE1569DC9A2 for ; Wed, 21 Dec 2005 14:16:04 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 85041-09 for ; Wed, 21 Dec 2005 14:16:03 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from web35508.mail.mud.yahoo.com (web35508.mail.mud.yahoo.com [66.163.179.132]) by postgresql.org (Postfix) with SMTP id B4D459DC84A for ; Wed, 21 Dec 2005 14:16:01 -0400 (AST) Received: (qmail 45241 invoked by uid 60001); 21 Dec 2005 18:16:01 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.br; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=yOM+EgGq/TAypYsMmiLNNd5kg2Sj9Dq0rTMBr4GB6Ca5yEg9JqXuPP4xjbozikl8ERY709cHQ2wmvY+Nm3uAruCbggi+jUHMX+FojOVFI7ylAksoUJ764xxXg2HevFlqizBoIvQHjgsg8GE7OxPa9GJTeosGB1SZIlay5H7ulpY= ; Message-ID: <20051221181601.45239.qmail@web35508.mail.mud.yahoo.com> Received: from [201.3.198.4] by web35508.mail.mud.yahoo.com via HTTP; Wed, 21 Dec 2005 18:16:01 GMT Date: Wed, 21 Dec 2005 18:16:01 +0000 (GMT) From: Carlos Benkendorf Subject: ORDER BY costs To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-150303133-1135188961=:45028" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.42 required=5 tests=[AWL=-0.060, DNS_FROM_RFC_ABUSE=0.479, HTML_MESSAGE=0.001] X-Spam-Score: 0.42 X-Spam-Level: X-Archive-Number: 200512/538 X-Sequence-Number: 16359 --0-150303133-1135188961=:45028 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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. --0-150303133-1135188961=:45028 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
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. --0-150303133-1135188961=:45028--