pgsql: Re-add item with better description: > * Allow ORDER BY ...

momjian@svr1.postgresql.org (Bruce Momjian) writes:

Re-add item with better 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.

That's not much better, as it simply begs the question "why not use
MIN/MAX"?

I think the real point of Oleg's suggestion is a better way to do "top N"
queries. This does not get interesting unless N > 1.

regards, tom lane

Tom Lane wrote:

momjian@svr1.postgresql.org (Bruce Momjian) writes:

Re-add item with better 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.

That's not much better, as it simply begs the question "why not use
MIN/MAX"?

I think the real point of Oleg's suggestion is a better way to do "top N"
queries. This does not get interesting unless N > 1.

Good point, updated:

* Allow ORDER BY ... LIMIT # 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 # 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.
MIN/MAX already does this, but not for LIMIT > 1.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073