SEO

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.

I think whatever the reasons for the different query plans are (and if that
can be fixed) - you CANNOT assume that data comes in sorted order when you do
not use order by. Thats what every database does this way. So, use order by,
or you'll be in trouble sooner or later.

Best regards,
Mario Weilguni


Am Freitag, 23. Dezember 2005 13:34 schrieb Carlos Benkendorf:
> 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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

We agree completely with you and that is what we are doing right now.

But what I would like is really understand the reason for this behavior and be sure we can not do anything more to improve the runtimes.

Benkendorf


Mario Weilguni <mweilguni@sime.com> escreveu:
I think whatever the reasons for the different query plans are (and if that
can be fixed) - you CANNOT assume that data comes in sorted order when you do
not use order by. Thats what every database does this way. So, use order by,
or you'll be in trouble sooner or later.

Best regards,
Mario Weilguni


Am Freitag, 23. Dezember 2005 13:34 schrieb Carlos Benkendorf:
> 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.




---------------------------------
Yahoo! doce lar. Faça do Yahoo! sua homepage.

Carlos Benkendorf wrote:

> 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?

Maybe your table in the old database is clustered on an index that
covers all ordered columns? Then, a sequential fetch of all rows would
probably return them ordered. But there still should be no guarantee for
this because postgres might first return the rows that are already in
memory.

Just making wild guesses.

--
Krešimir Tonković
Z-el d.o.o.
Industrijska cesta 28, 10360 Sesvete, Croatia
Tel: +385 1 2022 758
Fax: +385 1 2022 741
Web: www.chipoteka.hr
e-mail: z-el.tonkovic@chipoteka.hr




---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Carlos Benkendorf <carlosbenkendorf@yahoo.com.br> 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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 <tgl@sss.pgh.pa.us> 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.