Horton

Thread

  1. Re: Controlling Reuslts with Limit

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-02-23T21:18:16Z

    > Hi,
>  I was reading through Bruce's on line . I found follwing bit unclear...
> 
> "Notice that each query uses ORDER BY . Although this clause is not required,
> LIMIT without ORDER BY returns random rows from the query, which would be
> useless. "

It means there is no guarantee which rows will be returned.  You may get
the rows you want, or you may not.  Without the ORDER BY, the backend
can return any five rows it wishes.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  2. Re: Controlling Reuslts with Limit

    Bryan White <bryan@arcamax.com> — 2001-02-23T21:24:14Z

    
> Hi,
>  I was reading through Bruce's on line . I found follwing bit unclear...
>
> "Notice that each query uses ORDER BY . Although this clause is not
required,
> LIMIT without ORDER BY returns random rows from the query, which would be
> useless. "
>
> When I run a query several time  I get the same results as given
...
>  I just want to know what exatly --"LIMIT without ORDER BY returns random
rows
> from the query" --means

I don't think it is actually random.  It just that the order is not defined
and other events may change the order.  I believe that without an ORDER BY
or other clauses that cause an index to be used that the database tends to
return rows in the order stored on disk.  This order tends to be the order
in which rows were added.  My observation is this ordering is faily stable
and it seems to survive a database reload.  Just don't rely on it.  There is
a CLUSTER command to change the physical ordering.

  3. Re: Controlling Reuslts with Limit

    Jie Liang <jliang@ipinc.com> — 2001-02-23T21:27:18Z

    My understanding:
because you return a subset instead of a single value,
so between 2 select ... limit ... queries.
if you delete a record(say song_id=947) then insert it again.
then results are different.
So for a multiple users db, you should use oder by when you use limit.


Jie LIANG

St. Bernard Software
Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.stbernard.com
www.ipinc.com

On Sat, 24 Feb 2001, Najm Hashmi wrote:

> Hi,
>  I was reading through Bruce's on line . I found follwing bit unclear...
> 
> "Notice that each query uses ORDER BY . Although this clause is not required,
> LIMIT without ORDER BY returns random rows from the query, which would be
> useless. "
> 
> When I run a query several time  I get the same results as given
> flipr=# select song_id from songs  limit 5;
>  song_id
> ---------
>      945
>      946
>      947
>      948
>      949
> (5 rows)
> 
> flipr=# select song_id from songs  limit 5;
>  song_id
> ---------
>      945
>      946
>      947
>      948
>      949
> (5 rows)
> 
> flipr=# select song_id from songs  limit 5;
>  song_id
> ---------
>      945
>      946
>      947
>      948
>      949
> (5 rows)
> 
> flipr=# select song_id from songs  limit 5;
>  song_id
> ---------
>      945
>      946
>      947
>      948
>      949
> (5 rows)
> 
> flipr=# select song_id from songs  limit 5;
>  song_id
> ---------
>      945
>      946
>      947
>      948
>      949
> (5 rows)
> 
> flipr=# select song_id from songs  limit 5;
>  song_id
> ---------
>      945
>      946
>      947
>      948
>      949
>  I just want to know what exatly --"LIMIT without ORDER BY returns random rows
> from the query" --means
> Regards
>

  4. Re: Controlling Reuslts with Limit

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-23T21:39:27Z

    Najm Hashmi <najm@mondo-live.com> writes:
>  I just want to know what exatly --"LIMIT without ORDER BY returns random rows
> from the query" --means

It means the results aren't guaranteed.  It doesn't mean that the exact
same query run under the exact same conditions by the exact same version
of Postgres won't return the same results every time.  Especially not
one that's too simple to have more than one possible execution plan...

			regards, tom lane

  5. Re: Controlling Reuslts with Limit

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-02-23T21:46:50Z

    It returns the first five rows it finds.  Running the same
query over again if there are no updates is safe, but if the
table is updated there is the possibility it would find a different
five rows.  If the query would do a seq scan and you updated
a row, the rows would be in a different order in the heap file and so
you'd get a different ordering of rows...

On Sat, 24 Feb 2001, Najm Hashmi wrote:

> Hi,
>  I was reading through Bruce's on line . I found follwing bit unclear...
> 
> "Notice that each query uses ORDER BY . Although this clause is not required,
> LIMIT without ORDER BY returns random rows from the query, which would be
> useless. "
> 
> When I run a query several time  I get the same results as given
> flipr=# select song_id from songs  limit 5;
>  song_id
> ---------
>      945
>      946
>      947
>      948
>      949
> (5 rows)
> ...

  6. Re: Controlling Reuslts with Limit

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-02-23T21:59:09Z

    > I don't think it is actually random.  It just that the order is not defined
> and other events may change the order.  I believe that without an ORDER BY
> or other clauses that cause an index to be used that the database tends to
> return rows in the order stored on disk.  This order tends to be the order
> in which rows were added.  My observation is this ordering is faily stable
> and it seems to survive a database reload.  Just don't rely on it.  There is
> a CLUSTER command to change the physical ordering.

Yes, usually it is the heap order, but if you do "col > 12" you may get
it in index order by the column indexes, or you may not, depending on
the constant, the size of the table, vacuum, vacuum analyze, etc.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  7. Controlling Reuslts with Limit

    Najm Hashmi <najm@mondo-live.com> — 2001-02-24T21:09:06Z

    Hi,
 I was reading through Bruce's on line . I found follwing bit unclear...

"Notice that each query uses ORDER BY . Although this clause is not required,
LIMIT without ORDER BY returns random rows from the query, which would be
useless. "

When I run a query several time  I get the same results as given
flipr=# select song_id from songs  limit 5;
 song_id
---------
     945
     946
     947
     948
     949
(5 rows)

flipr=# select song_id from songs  limit 5;
 song_id
---------
     945
     946
     947
     948
     949
(5 rows)

flipr=# select song_id from songs  limit 5;
 song_id
---------
     945
     946
     947
     948
     949
(5 rows)

flipr=# select song_id from songs  limit 5;
 song_id
---------
     945
     946
     947
     948
     949
(5 rows)

flipr=# select song_id from songs  limit 5;
 song_id
---------
     945
     946
     947
     948
     949
(5 rows)

flipr=# select song_id from songs  limit 5;
 song_id
---------
     945
     946
     947
     948
     949
 I just want to know what exatly --"LIMIT without ORDER BY returns random rows
from the query" --means
Regards