Message-ID: <43AC0A33.3060806@chipoteka.hr>
Date: Fri, 23 Dec 2005 15:31:15 +0100
From: Kresimir Tonkovic <z-el.tonkovic@chipoteka.hr>
User-Agent: Debian Thunderbird 1.0.2 (X11/20050817)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Order by behaviour
References: <20051223123439.69718.qmail@web35507.mail.mud.yahoo.com>
In-Reply-To: <20051223123439.69718.qmail@web35507.mail.mud.yahoo.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

Carlos Benkendorf wrote:

> Hi,
> =20
> We have more than 200 customers running 8.0.3 and two weeks ago=20
> started migration project to 8.1.1.After the first migration to 8.1.1=20
> we had to return back to 8.0.3 because some applications were not=20
> working right.
> =20
> Our user told me that records are not returning more in the correct=20
> order, so I started logging and saw that the select clause wasn=C2=B4t =
not=20
> used with the ORDER BY clause. It seemed a simple problem to be solved.=

> =20
> I asked the programmers that they should add the ORDER BY clause if=20
> they need the rows in a certain order and they told me they could not=20
> do it because it will cost too much and the response time is bigger=20
> than not using ORDER BY. I disagreed with them because there was an=20
> index with the same order needed for the order by. Before starting a=20
> 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=20
> expensive than the select without the order by. I will not bet any=20
> more...;-)
> =20
> For some implementation reason in 8.0.3 the query is returning the=20
> rows in the correct order even without the order by but in 8.1.1=20
> probably the implementation changed and the rows are not returning in=20
> the correct order.
> =20
> We need the 8.1 for other reasons but this order by behavior stopped=20
> the migration project.
> =20
> 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=20
> numeric types to smallint/integer/bigint but even so the runtime and=20
> costs are far from the ones from the selects without the ORDER BY claus=
e.
> =20
> What I can not understand is why the planner is not using the same=20
> retrieving method with the order by clause as without the order by=20
> clause. All the rows are retriev ed in the correct order in both=20
> methods but one is much cheaper (without order by) than the other=20
> (with order by). Should not the planner choice that one?
> =20
> 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=20
> sort that is much more expensive?

Maybe your table in the old database is clustered on an index that=20
covers all ordered columns? Then, a sequential fetch of all rows would=20
probably return them ordered. But there still should be no guarantee for =

this because postgres might first return the rows that are already in=20
memory.

Just making wild guesses.

--=20
Kre=C5=A1imir Tonkovi=C4=87
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