Re: [External] LIMIT not showing all results

[Casey Deccio]

> On Mar 5, 2019, at 9:15 AM, Vijaykumar Jain <vj...@opentable.com> 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