While we are discussing speeding up the sort algorithms
that PostgreSQL uses, we should not forget about the real
problem: thet sorting is _always_ done after fetching the
whole set selected. In other words the optimiser does not
optimise sorting.
It is a real showstopper for interactive use (ODBC, probably
JDBC) as you often want to have a way to traverse the dataset
interactively, using the dataset sorted by primary key for this.
Even worse, MS Access and Delph both do it as a default when
using their ready-made components to access a table. So opening
a table from Access using PostODBC will first query the whole table,
write it all out to a bunch of temp* and psort* files, create a new
temp* file and only then give the first few rows of it back.
And as it seems it leaks a lot of memory in the process.
What makes it even worse is the fact that the whole process is
repeated when moving backwards on the dataset. It gets a real PITA
with a dataset that is little bigger (even a few thousand rows), as
it is not only slow but can cause all kinds of unwanted effects,
like filling up the disk and crashing the database , especially
in case of multiple users.
What it should do, IMHO, is to optimise the query so that it will
traverse the table by the primary key (ok, unique btree index) and
return just the few rows wanted. For the beginning it would be a big
improvement if it did so for at least single-table queries sorted by
a column having a btree index.
Cheers,
Hannu Krosing
------------------------------
