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 <ca...@deccio.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
