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

Log Message:
-----------
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.
>

Modified Files:
--------------
    pgsql/doc:
        TODO (r1.1515 -> r1.1516)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/TODO.diff?r1=1.1515&r2=1.1516)
    pgsql/doc/src/FAQ:
        TODO.html (r1.22 -> r1.23)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/FAQ/TODO.html.diff?r1=1.22&r2=1.23)

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