When postgresql implements the following query:
Select * from <table> where <condition> order by <ordinal expression>
limit <X>
It appears to do a select, then a sort, then return the top X rows.
This works fine for small results but not for tables with tens of
millions of rows and queries that may return tens of thousands or even
hundreds of thousands of rows.
The sort is superfluous and incredibly expensive.
What should be done on this query is to do the select saving X rows
in a save-bucket that is ranked by the ordinal expression.
In fact, I currently find it far faster to do a simple select with no
limit or ordering and then in an external application do a single pass
over the resultset using an efficient save-bucket to keep the X
highest ranked rows.
Is there any way to get postgresql to perform this query efficiently
-- single-pass with ranking and an efficient save-bucket, no sorting,
or are there any plans for such an extension?
Thanks
Eric