Re: Order by behaviour

[Carlos Benkendorf <carlosbenkendorf@xxxxxxxxxxxx>]

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@xxxxxxxxxxxxx> 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.