LIMIT not showing all results

[Casey Deccio]

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

Re: [External] LIMIT not showing all results

[Casey Deccio]

> On Mar 5, 2019, at 9:15 AM, Vijaykumar Jain  wrote:
> 
> Can you run both the queries with
> “explain analyze select ” and paste the output.

dnsviz=> explain analyze select id,name,date from analysis where name = 'foo' 
order by date desc limit 20;   

 QUERY PLAN 
   


   
---
--  

   
 Limit  (cost=0.57..81.74 rows=20 width=31) (actual time=0.540..0.541 rows=1 
loops=1)
  
   ->  Index Scan Backward using analysis_name_date_key on analysis  
(cost=0.57..7760.25 rows=1912 width=31) (actual 
time=0.539..0.540 rows=1 loops=1)   

   
 Index Cond: ((name)::text = 'foo'::text)
 Planning time: 6.728 ms
 Execution time: 0.587 ms
(5 rows)

dnsviz=> explain analyze select id,name,date from analysis where name = 'foo' 
order by date desc;  

   QUERY PLAN   
   


   
---|
-   

   
 Sort  (cost=7720.96..7725.74 rows=1912 width=31) (actual time=687.953..688.460 
rows=4965 loops=1)  
   
   Sort Key: date   

   
   Sort Method: quicksort  Memory: 580kB

   
   ->  Bitmap Heap Scan on analysis  (cost=75.39..7616.75 rows=1912 width=31) 
(actual time=8.600..677.916 rows=4965 loops=1) 
 Recheck Cond: ((name)::text = 'foo'::text) 

   
 Heap Blocks: exact=4943

   
 ->  Bitmap Index Scan on analysis_name_cache_group_id_key  
(cost=0.00..74.91 rows=1912 width=0) (actual time=7.028..7.028 rows=4965 loop
s=1)

   
   Index Cond: ((name)::text = 'foo'::text)
 Planning time: 0.222 ms
 Execution time: 688.897 ms
(10 rows)

Thanks,
Casey

Re: LIMIT not showing all results

[Casey Deccio]

> On Mar 5, 2019, at 10:37 AM, Andreas Kretschmer  
> wrote:
> 
> no, but you can set enable_indexscan to off and maybe also enable_bitmapscan 
> to off to force the planner to choose a seq-scan.
> I'm sure in this case you will get a correct result.

So this (setting enable_indexscan to off) might be a short-term fix, while 
reindexing is the more robust fix?

Casey

Re: [External] LIMIT not showing all results

[Casey Deccio]

> On Mar 5, 2019, at 11:55 AM, Tom Lane  wrote:
> 
> Matthew Pounsett  writes:
>> On Tue, 5 Mar 2019 at 12:54, Tom Lane  wrote:
>>> Given that (a) this was triggered by a server migration and (b)
>>> the leading column of the index looks like it's probably varchar,
>>> I'm suspicious that the new server has different collation behavior.
> 
>> The migration in question was an rsync from a Debian 9 box
>> running 9.4.19-0+deb8u1 to a FreeBSD 11 box  running 9.4.20.
> 
> Yeah, that would fit the theory :-(.  Debian would be using glibc
> and FreeBSD would not be.  If you were using C collation in the
> database, you'd be all right because that's standardized, but I'll
> bet you were using something else.  What does psql \l show for the
> "collate" setting of this database?  (Or, if by chance you had an
> explicit COLLATE setting on the column in question, what's that?)
> 
> In any case, you should be reindexing any indexes on textual columns
> that were not using "C" collation; none of them can be trusted.
> The system catalogs should be OK.

Many thanks to you and everyone who helped with this issue, with informative 
and actionable advice.  Reindexing worked like a champ, and we seem to be back 
in business.

Casey