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@xxxxxxxxxxxx
