Re: [PERFORM] Order by behaviour

2005-12-23 Thread Carlos Benkendorf
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 S

Re: [PERFORM] Order by behaviour

2005-12-23 Thread Tom Lane
Carlos Benkendorf <[EMAIL PROTECTED]> 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

Re: [PERFORM] Order by behaviour

2005-12-23 Thread Guido Neitzer
On 23.12.2005, at 15:35 Uhr, Carlos Benkendorf wrote: I appreciate your suggestion but I think I´m misunderstanding something, the select statement should return at about 150.000 rows, why 5 rows? I have looked at the wrong lines of the explain ... statement. Sorry, my fault. With that ma

Re: [PERFORM] Order by behaviour

2005-12-23 Thread Kresimir Tonkovic
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 returni

Re: [PERFORM] Order by behaviour

2005-12-23 Thread Carlos Benkendorf
>If you can't force PostgreSQL to perform better on the ordered query, >what about retrieving only the primary keys for the rows you want >unordered in a subquery and using an "where primaryKey in (...) order >by ..." statement with ordering the five rows?   I appreciate your suggestion but I thi

Re: [PERFORM] Order by behaviour

2005-12-23 Thread Guido Neitzer
On 23.12.2005, at 13:34 Uhr, Carlos Benkendorf wrote: 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. You will never

Re: [PERFORM] Order by behaviour

2005-12-23 Thread Carlos Benkendorf
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.   BenkendorfMario Weilguni <[EMAIL PROTECTED]> escreveu: I think whatever the reas

Re: [PERFORM] Order by behaviour

2005-12-23 Thread Mario Weilguni
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, M

[PERFORM] Order by behaviour

2005-12-23 Thread 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 orde