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 034DE9DC805 for ; Fri, 23 Dec 2005 08:34:39 -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 62341-02 for ; Fri, 23 Dec 2005 08:34:41 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from web35507.mail.mud.yahoo.com (web35507.mail.mud.yahoo.com [66.163.179.131]) by postgresql.org (Postfix) with SMTP id 5535B9DC808 for ; Fri, 23 Dec 2005 08:34:36 -0400 (AST) Received: (qmail 69720 invoked by uid 60001); 23 Dec 2005 12:34:39 -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=1DAlfeFGHxMxxGvUYYNwO9CFCQdfX4AFftfzuxz5+Q7msLLP4vlPwKzTvTp/gbbmwH+bSEoaIccPYrKXQyqKON+VEGdvP0VrelUrqhxjE2BIUxdWdv2qIbS1OnM4A+mFMNANJZQg94VcNqJJ4MSeMluRxGYKPnbhkC+zIm1INrs= ; Message-ID: <20051223123439.69718.qmail@web35507.mail.mud.yahoo.com> Received: from [201.2.222.140] by web35507.mail.mud.yahoo.com via HTTP; Fri, 23 Dec 2005 12:34:39 GMT Date: Fri, 23 Dec 2005 12:34:39 +0000 (GMT) From: Carlos Benkendorf Subject: Order by behaviour To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-456538433-1135341279=:59996" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.432 required=5 tests=[AWL=-0.048, DNS_FROM_RFC_ABUSE=0.479, HTML_MESSAGE=0.001] X-Spam-Score: 0.432 X-Spam-Level: X-Archive-Number: 200512/581 X-Sequence-Number: 16402 --0-456538433-1135341279=:59996 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi, We have more than 200 customers running 8.0.3 and two weeks ago started migration project to 8.1.1.After the first migration to 8.1.1 we had to return back to 8.0.3 because some applications were not working right. Our user told me that records are not returning more in the correct order, so I started logging and saw that the select clause wasn´t not used with the ORDER BY clause. It seemed a simple problem to be solved. I asked the programmers that they should add the ORDER BY clause if they need the rows in a certain order and they told me they could not do it because it will cost too much and the response time is bigger than not using ORDER BY. I disagreed with them because there was an index with the same order needed for the order by. Before starting a figth we decided to explain analyze both select types and discover who was right. For my surprise the select with order by was really more expensive than the select without the order by. I will not bet any more...;-) For some implementation reason in 8.0.3 the query is returning the rows in the correct order even without the order by but in 8.1.1 probably the implementation changed and the rows are not returning in the correct order. We need the 8.1 for other reasons but this order by behavior stopped the migration project. Some friends of the list tried to help us and I did some configuration changes like increased work_mem and changed the primary columns from numeric types to smallint/integer/bigint but even so the runtime and costs are far from the ones from the selects without the ORDER BY clause. What I can not understand is why the planner is not using the same retrieving method with the order by clause as without the order by clause. All the rows are retrieved in the correct order in both methods but one is much cheaper (without order by) than the other (with order by). Should not the planner choice that one? Can someone explain me why the planner is not choosing the same method used with the selects without the order by clause instead of using a sort that is much more expensive? 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-456538433-1135341279=:59996 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
Hi,
 
We have more than 200 customers running 8.0.3 and two weeks ago started migration project to 8.1.1.After the first migration to 8.1.1 we had to return back to 8.0.3 because some applications were not working right.
 
Our user told me that records are not returning more in the correct order, so I started logging and saw that the select clause wasn´t not used with the ORDER BY clause. It seemed a simple problem to be solved.
 
I asked the programmers that they should add the ORDER BY clause if they need the rows in a certain order and they told me they could not do it because it will cost too much and the response time is bigger than not using ORDER BY. I disagreed with them because there was an index with the same order needed for the order by. Before starting a figth we decided to explain analyze both select types and discover who was right. For my surprise the sele ct with order by was really more expensive than the select without the order by. I will not bet any more...;-)
 
For some implementation reason in 8.0.3 the query is returning the rows in the correct order even without the order by but in 8.1.1 probably the implementation changed and the rows are not returning in the correct order.
 
We need the 8.1 for other reasons but this order by behavior stopped the migration project.
 
Some friends of the list tried to help us and I did some configuration changes like increased work_mem and changed the primary columns from numeric types to smallint/integer/bigint but even so the runtime and costs are far from the ones from the selects without the ORDER BY clause.
 
What I can not understand is why the planner is not using the same retrieving method with the order by clause as without the order by clause. All the rows are retriev ed in the correct order in both methods but one is much cheaper (without order by) than the other (with order by). Should not the planner choice that one?
 
Can someone explain me why the planner is not choosing the same method used with the selects without the order by clause instead of using a sort that is much more expensive?
 
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: (((anocal c = 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, codven cto, 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)
    "iarcha ve05" 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-456538433-1135341279=:59996--