From: | Vijaykumar Jain <vjain(at)opentable(dot)com> |
---|---|
To: | Casey Deccio <casey(at)deccio(dot)net> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: [External] LIMIT not showing all results |
Date: | 2019-03-05 16:15:55 |
Message-ID: | CAE7uO5jf+EytMXTv2Qg=Bg=NfLkbaJqO6PRtEr28R8sAh695mw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Can you run both the queries with
“explain analyze select ....” and paste the output.
On Tue, 5 Mar 2019 at 9:41 PM Casey Deccio <casey(at)deccio(dot)net> wrote:
> Okay, the subject is a little misleading because of course LIMIT isn't
> supposed to all results, but I've got an issue where LIMIT isn't showing
> the number of results I would expect. For example:
>
> mydb=> select id,name,date from analysis where name = 'foo' order by date
> desc limit 3;
> id | name | date
> -----------+-------------+------------------------
> 195898786 | foo | 2019-03-05 06:45:29+00
> (1 row)
>
> mydb=> select id,name,date from analysis where name = 'foo' order by date
> desc limit 20;
> id | name | date
> -----------+-------------+------------------------
> 195898786 | foo | 2019-03-05 06:45:29+00
>
>
> But when I query without limit (or even with "limit all"), there are more:
>
> mydb=> select id,name,date from analysis where name = 'foo' order by date
> desc;
> id | name | date
> -----------+-------------+------------------------
> 195898786 | foo | 2019-03-05 06:45:29+00
> 195842671 | foo | 2019-01-24 14:31:45+00
> 195667475 | foo | 2018-12-30 23:40:11+00
> 195256709 | foo | 2018-10-29 18:33:07+00
> ...
> (Many more rows)
>
> psql version 9.6.11.
> Server version 9.4.20.
>
> We just migrated the database from one server to another (as far as I
> know, there was no version change), and it worked previously.
>
> Any ideas?
>
> Many thanks in advance.
> Casey
>
--
Regards,
Vijay
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Munro | 2019-03-05 16:20:07 | Re: Slave server sometimes locks up |
Previous Message | Casey Deccio | 2019-03-05 16:10:50 | LIMIT not showing all results |