On Mon, 25 Apr 2005, Stephen Frost wrote:
* Bruce Momjian ([email protected]) wrote:Thanks, TODO item readded with a clearer description:
* Allow ORDER BY ... LIMIT 1 to select high/low value without sort or index using a sequential scan for highest/lowest values
Right now, if no index exists, ORDER BY ... LIMIT 1 requires we sort all values to return the high/low value. Instead The idea is to do a sequential scan to find the high/low value, thus avoiding the sort.
Could we take this perhaps a step further and consider things like 'LIMIT 10' and come up with an approximate point where the trade-off exists? Actually, thinking about this a minute more perhaps there isn't even a trade-off to be made... What you're suggesting is basically a size-of-1 temporary memory structure for the 'sort'. Isn't there already a memory structure used to perform the sorting though? Could it be adjusted such that it's of a fixed size when 'LIMIT' is given, as above?
Just some thoughts, while I think the specific 'LIMIT 1' case is probably pretty common I think the 'LIMIT 10' or 'LIMIT 50' (or however many you want to display on the webpage...) is a pretty common use case too and it sounds like we could improve those too with this mechanism.
It's a question of when to stop sorting, so, yes, it should be doable.
Thoughts?
Thanks,
Stephen
Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [email protected], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
