Re: Strange limit and offset behaviour.... - Mailing list pgsql-general

From Bjørn T Johansen
Subject Re: Strange limit and offset behaviour....
Date
Msg-id 20090207225719.07a4240d@pennywise.havleik.no
Whole thread Raw
In response to Re: Strange limit and offset behaviour....  ("Adam Rich" <adam.r@sbcglobal.net>)
Responses Re: Strange limit and offset behaviour....
List pgsql-general
On Sat, 7 Feb 2009 15:49:49 -0600
"Adam Rich" <adam.r@sbcglobal.net> wrote:

> >
> > I have the following sql:
> >
> > SELECT * from table order by dato asc limit 20 offset 0
> >
> >
> > This gives me different rows than the 20 first rows when running the
> > following sql:
> >
> > SELECT * from table order by dato asc
> >
> >
> > Shouldn't the 20 first rows in the second sql statment be the same 20
> > rows that is returned in the first statement
> > or am I missing something?
> >
>
> Not necessarily.  In your example query, if "dato" was not a unique
> column, and there were some duplicates, the "top 20" values is not
> a defined set.  Adding the offset clause might cause a different
> query plan, resulting in a different ordering of the duplicate values.
>
>
I found out that if I created an index on the dato field, then I got the same 20 rows... Does that make sense or is
it just a coincedense?

BTJ

pgsql-general by date:

Previous
From: "Adam Rich"
Date:
Subject: Re: Strange limit and offset behaviour....
Next
From: Grzegorz Jaśkiewicz
Date:
Subject: Re: Strange limit and offset behaviour....