Your main example seems to focus on a large table where a key
column has
constrained values. This case is interesting in proportion to the
number of possible values. If I have billions of rows, each
having one
of only two values, I can think of a trivial and very fast method of
returning the table "sorted" by that key: make two sequential passes,
returning the first value on the first pass and the second value
on the
second pass. This will be faster than the method you propose.
1= No that was not my main example. It was the simplest example
used to
frame the later more complicated examples. Please don't get hung
up on it.
2= You are incorrect. Since IO is the most expensive operation we
can do,
any method that makes two passes through the data at top scanning
speed
will take at least 2x as long as any method that only takes one
such pass.
You do not get the point.
As the time you get the sorted references to the tuples, you need to
fetch the tuples themself, check their visbility, etc. and returns
them to the client.
So,
if there is only 2 values in the column of big table that is larger
than available RAM,
two seq scans of the table without any sorting
is the fastest solution.
Cordialement,
Jean-Gérard Pailloncy
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
