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 B3A829DC869 for ; Fri, 23 Dec 2005 21:48:59 -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 79345-06 for ; Fri, 23 Dec 2005 21:49:02 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from web35504.mail.mud.yahoo.com (web35504.mail.mud.yahoo.com [66.163.179.128]) by postgresql.org (Postfix) with SMTP id CDEEB9DC832 for ; Fri, 23 Dec 2005 21:48:56 -0400 (AST) Received: (qmail 57709 invoked by uid 60001); 24 Dec 2005 01:49:00 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.br; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=CnhHO5gHSWJuDSOAgtww8YAg1wL6jAI5hbX5vVTzLXIVY7QIQrnHWePwlTcdvpPn9HdgOnSqAtGHxiSe1qMiGnYRfTZNWwWWR8LfPPdWe7Qv/bthsPIJub5TB1drTV9hkXD06JScEFm+0wvRTW+jFKlZNAQcDS7Ei7Ai7d2FKiM= ; Message-ID: <20051224014900.57703.qmail@web35504.mail.mud.yahoo.com> Received: from [201.14.172.40] by web35504.mail.mud.yahoo.com via HTTP; Sat, 24 Dec 2005 01:49:00 GMT Date: Sat, 24 Dec 2005 01:49:00 +0000 (GMT) From: Carlos Benkendorf Subject: Re: Order by behaviour To: pgsql-performance@postgresql.org In-Reply-To: <20074.1135353288@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-16810945-1135388940=:56834" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.36 required=5 tests=[AWL=-0.120, DNS_FROM_RFC_ABUSE=0.479, HTML_MESSAGE=0.001] X-Spam-Score: 0.36 X-Spam-Level: X-Archive-Number: 200512/593 X-Sequence-Number: 16414 --0-16810945-1135388940=:56834 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit YES.... it worked very nice.... Using UNION with 8.0.3: Append (cost=0.00..164840.70 rows=232632 width=892) (actual time=0.350..28529.895 rows=167711 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..2.91 rows=1 width=892) (actual time=0.098..0.098 rows=0 loops=1) -> Index Scan using pk_arript on arript (cost=0.00..2.90 rows=1 width=892) (actual time=0.094..0.094 rows=0 loops=1) Index Cond: ((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto = 0::numeric) AND (parcela >= 0::numeric)) -> Subquery Scan "*SELECT* 2" (cost=0.00..14.00 rows=12 width=892) (actual time=0.249..0.425 rows=2 loops=1) -> Index Scan using pk_arript on arript (cost=0.00..13.88 rows=12 width=892) (actual time=0.041..0.053 rows=2 loops=1) Index Cond: ((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto > 0::numeric)) -> Subquery Scan "*SELECT* 3" (cost=0.00..55949.61 rows=68413 width=892) (actual time=0.216..12324.475 rows=72697 loops=1) -> Index Scan using pk_arript on arript (cost=0.00..55265.48 rows=68413 width=892) (actual time=0.033..429.152 rows=72697 loops=1) Index Cond: ((anocalc = 2005::numeric) AND (cadastro > 19::numeric)) -> Subquery Scan "*SELECT* 4" (cost=0.00..108874.19 rows=164206 width=892) (actual time=0.297..16054.064 rows=95012 loops=1) -> Index Scan using pk_arript on arript (cost=0.00..107232.13 rows=164206 width=892) (actual time=0.046..485.430 rows=95012 loops=1) Index Cond: (anocalc > 2005::numeric) Total runtime: 28621.053 ms (14 rows) NOT SO GOOD! But using with 8.1: Append (cost=0.00..117433.94 rows=171823 width=897) (actual time=0.126..697.004 rows=167710 loops=1) -> Index Scan using pk_arript on arript (cost=0.00..2.81 rows=1 width=897) (actual time=0.083..0.083 rows=0 loops=1) Index Cond: ((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto = 0::numeric) AND (parcela >= 0::numeric)) -> Index Scan using pk_arript on arript (cost=0.00..12.05 rows=11 width=897) (actual time=0.039..0.050 rows=2 loops=1) Index Cond: ((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto > 0::numeric)) -> Index Scan using pk_arript on arript (cost=0.00..46950.74 rows=65125 width=897) (actual time=0.031..275.674 rows=72697 loops=1) Index Cond: ((anocalc = 2005::numeric) AND (cadastro > 19::numeric)) -> Index Scan using pk_arript on arript (cost=0.00..68750.11 rows=106686 width=897) (actual time=0.042..272.257 rows=95011 loops=1) Index Cond: (anocalc > 2005::numeric) Total runtime: 786.670 ms Using 8.1 and changing NUMERIC primary key columns to INTEGERs. Append (cost=0.00..107767.19 rows=159082 width=826) (actual time=0.091..487.802 rows=167710 loops=1) -> Index Scan using pk_arript on arript (cost=0.00..2.81 rows=1 width=826) (actual time=0.067..0.067 rows=0 loops=1) Index Cond: ((anocalc = 2005) AND (cadastro = 19) AND (codvencto = 0) AND (parcela >= 0)) -> Index Scan using pk_arript on arript (cost=0.00..11.21 rows=10 width=826) (actual time=0.020..0.026 rows=2 loops=1) Index Cond: ((anocalc = 2005) AND (cadastro = 19) AND (codvencto > 0)) -> Index Scan using pk_arript on arript (cost=0.00..44454.18 rows=62866 width=826) (actual time=0.012..157.058 rows=72697 loops=1) Index Cond: ((anocalc = 2005) AND (cadastro > 19)) -> Index Scan using pk_arript on arript (cost=0.00..61708.17 rows=96205 width=826) (actual time=0.044..183.768 rows=95011 loops=1) Index Cond: (anocalc > 2005) Total runtime: 571.221 ms (10 rows) Itīs faster than our currently SELECT without ORDER BY (1712.456 ms)... itīs wonderful... We are aware about the risks of not using the ORDER BY clause .. but itīs a managed risk... Thank very much all the people who helped to solve this problem, especially Tom Lane! Thanks a lot! Benkendorf Tom Lane escreveu: Carlos Benkendorf writes: > 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. It was pure luck that prior versions gave you the result you wanted --- as other people already noted, the ordering of results is never guaranteed unless you say ORDER BY. The way you phrased the query gave rise (before 8.1) to several independent index scans that just happened to yield non-overlapping, individually sorted, segments of the desired output, and so as long as the system executed those scans in the right order, you got your sorted result without explicitly asking for it. But the system wasn't aware that it was giving you any such thing, and certainly wasn't going out of its way to do so. In 8.1 we no longer generate that kind of plan --- OR'd index scans are handled via bitmap-scan plans now, which are generally a lot faster, but don't yield sorted output. You could probably kluge around it by switching to a UNION ALL query: SELECT * FROM iparq.ARRIPT where (ANOCALC = 2005 and CADASTRO = 19 and CODVENCTO = 00 and PARCELA >= 00 ) UNION ALL SELECT * FROM iparq.ARRIPT where (ANOCALC = 2005 and CADASTRO = 19 and CODVENCTO > 00 ) UNION ALL SELECT * FROM iparq.ARRIPT where (ANOCALC = 2005 and CADASTRO > 19 ) UNION ALL SELECT * FROM iparq.ARRIPT where (ANOCALC > 2005 ); Again, the system has no idea that it's giving you data in any useful overall order, so this technique might also break someday, but it's good for the time being. Of course, all of these are ugly, klugy solutions. The correct way to solve your problem would be with a row comparison: SELECT * FROM iparq.ARRIPT where (ANOCALC, CADASTRO, CODVENCTO, PARCELA) >= (2005, 19, 00, 00) ORDER BY ANOCALC, CADASTRO, CODVENCTO, PARCELA; Postgres doesn't currently support this (we take the syntax but don't implement it per SQL spec, and don't understand the connection to an index anyway :-() ... but sooner or later it'll get fixed. regards, tom lane --------------------------------- Yahoo! doce lar. Faįa do Yahoo! sua homepage. --0-16810945-1135388940=:56834 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
YES.... it worked very nice....
 
Using UNION with 8.0.3:
 
 Append  (cost=0.00..164840.70 rows=232632 width=892) (actual time=0.350..28529.895 rows=167711 loops=1)
   ->  Subquery Scan "*SELECT* 1"  (cost=0.00..2.91 rows=1 width=892) (actual time=0.098..0.098 rows=0 loops=1)
         ->  Index Scan using pk_arript on arript  (cost=0.00..2.90 rows=1 width=892) (actual time=0.094..0.094 rows=0 loops=1)
               Index Cond: ((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto = 0::numeric) AND (parcela >= 0::numeric))
   ->  Subquery Scan "*SELECT* 2"  (cost=0.00..14.00 rows=12 width=892) (actual time=0.249..0.425 rows=2 loops=1)
         ->  Index Sca n using pk_arript on arript  (cost=0.00..13.88 rows=12 width=892) (actual time=0.041..0.053 rows=2 loops=1)
               Index Cond: ((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto > 0::numeric))
   ->  Subquery Scan "*SELECT* 3"  (cost=0.00..55949.61 rows=68413 width=892) (actual time=0.216..12324.475 rows=72697 loops=1)
         ->  Index Scan using pk_arript on arript  (cost=0.00..55265.48 rows=68413 width=892) (actual time=0.033..429.152 rows=72697 loops=1)
               Index Cond: ((anocalc = 2005::numeric) AND (cadastro > 19::numeric))
   ->  Subquery Scan "*SELECT* 4"  (cost=0.00..108874.19 rows=164206 width=892) (actual time=0.297..16054.064 rows=95012 loops=1)
         ->  Index Scan using pk_arript on arript  (cost=0.00..107232.13 rows=164206 width=892) (actual time=0.046..485.430 rows=95012 loops=1)
               Index Cond: (anocalc > 2005::numeric)
 Total runtime: 28621.053 ms
(14 rows)
 
NOT SO GOOD!
 
But using with 8.1:
 
 Append  (cost=0.00..117433.94 rows=171823 width=897) (actual time=0.126..697.004 rows=167710 loops=1)
   ->  Index Scan using pk_arript on arript  (cost=0.00..2.81 rows=1 width=897) (actual time=0.083..0.083 rows=0 loops=1)
         Index Cond: ((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto = 0::numeric) AND (parcela >= 0::numeric))
   ->  Index Scan using pk_arript on arript  (cost=0.00..12.05 rows=11 width=897) (actual time=0.039..0.050 rows=2 loops=1)
         Index Cond: ((anocalc = 2005::numeric) AND (cadastro = 19::numeric) AND (codvencto > 0::numeric))
   ->  Index Scan using pk_arript on arript  (cost=0.00..46950.74 rows=65125 width=897) (actual time=0.031..275.674 rows=72697 loops=1)
         Index Cond: ((anocalc = 2005::numeric) AND (cadastro > 19::numeric))
   ->  Index Scan using pk_arript on arript  (cost=0.00..68750.11 rows=106686 width=897) (actual time=0.042..272.257 rows=95011 loops=1)
         Index Cond: (anocalc > 2005::numeric)
 Total runtime: 786.670 ms
Using 8.1 and changing NUMERIC primary key columns to INTEGERs.
 
 Append  (cost=0.00..107767 .19 rows=159082 width=826) (actual time=0.091..487.802 rows=167710 loops=1)
   ->  Index Scan using pk_arript on arript  (cost=0.00..2.81 rows=1 width=826) (actual time=0.067..0.067 rows=0 loops=1)
         Index Cond: ((anocalc = 2005) AND (cadastro = 19) AND (codvencto = 0) AND (parcela >= 0))
   ->  Index Scan using pk_arript on arript  (cost=0.00..11.21 rows=10 width=826) (actual time=0.020..0.026 rows=2 loops=1)
         Index Cond: ((anocalc = 2005) AND (cadastro = 19) AND (codvencto > 0))
   ->  Index Scan using pk_arript on arript  (cost=0.00..44454.18 rows=62866 width=826) (actual time=0.012..157.058 rows=72697 loops=1)
         Index Cond: ((anocalc = 2005) AND (cadastro > 19))
   ->  Index Scan using pk_arript on arript  (cost=0.00..61708.17 rows=96205 width=826) (actual time=0.044..183.768 rows=95011 loops=1)
         Index Cond: (anocalc > 2005)
 Total runtime: 571.221 ms
(10 rows)
 
Itīs faster than our currently SELECT without ORDER BY (1712.456 ms)... itīs wonderful...
 
We are aware about the risks of not using the ORDER BY clause .. but itīs a managed risk...
 
Thank very much all the people who helped to solve this problem, especially Tom Lane!
 
Thanks a lot!
 
Benkendorf

Tom Lane <tgl@sss.pgh.pa.us> escreveu:
Carlos Benkendorf writes:
> For some implementation reason in 8.0.3 the query is returni ng 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.

It was pure luck that prior versions gave you the result you wanted ---
as other people already noted, the ordering of results is never
guaranteed unless you say ORDER BY. The way you phrased the query
gave rise (before 8.1) to several independent index scans that just
happened to yield non-overlapping, individually sorted, segments of
the desired output, and so as long as the system executed those scans
in the right order, you got your sorted result without explicitly asking
for it. But the system wasn't aware that it was giving you any such
thing, and certainly wasn't going out of its way to do so.

In 8.1 we no longer generate that kind of plan --- OR'd index scans are
handled via bitmap-scan plans now, which are generally a lot faster,
but don't yield sorted output.

You could probably kluge around it by switching to a UNION ALL query:

SELECT * FROM iparq.ARRIPT where
(ANOCALC = 2005
and CADASTRO = 19
and CODVENCTO = 00
and PARCELA >= 00 )
UNION ALL
SELECT * FROM iparq.ARRIPT where
(ANOCALC = 2005
and CADASTRO = 19
and CODVENCTO > 00 )
UNION ALL
SELECT * FROM iparq.ARRIPT where
(ANOCALC = 2005
and CADASTRO > 19 )
UNION ALL
SELECT * FROM iparq.ARRIPT where
(ANOCALC > 2005 );

Again, the system has no idea that it's giving you data in any
useful overall order, so this technique might also break someday,
but it's good for the time being.

Of course, all of these are ugly, klugy solutions. The correct way
to solve your problem would be with a row comparison:

SELECT * FROM iparq.ARRIPT
where
(ANOCALC, CADASTRO, CODVENCTO, PARCELA) >= (2005, 19, 00, 00)
ORDER BY ANOCALC, CADASTRO, CODVENCTO, PARCELA;

Postgres doesn't currently suppo rt this (we take the syntax but don't
implement it per SQL spec, and don't understand the connection to an
index anyway :-() ... but sooner or later it'll get fixed.

regards, tom lane

Yahoo! doce lar. Faįa do Yahoo! sua homepage. --0-16810945-1135388940=:56834--