Re: limit and query planner - Mailing list pgsql-general

> On Jun 5, 2018, at 2:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> armand pirvu <armand.pirvu@gmail.com> writes:
>> My question is: I suspect the limit simply limits the fethching to the first n-records retrieved and has no
implicationswhatsoever on the planner, meaning the planner ignores it. Am I right or wrong ? 
>
> You're quite wrong.  The presence of a LIMIT causes the planner to prefer
> "fast start" plans, since it will then optimize on the basis of picking
> the lowest estimated cost to fetch the first N rows.  As an example,
> you're more likely to get an ordered indexscan than a seqscan-and-sort
> for small N, though there are many cases where seqscan-and-sort wins
> if the need is to fetch the whole table.
>
>             regards, tom lane



Thank you Tom


So since
select count(*) from sp_i2birst_reg_staging_test;
count
-------
 6860
and
select count(*) from sp_i2birst_reg_staging_test where evt_id = 'ACSF17'
and status=0 ;
count
-------
 4239

That means to me I fetch almost the whole table and then I fall in the case you described seqscan-and-sort wins over
indexscan. 

My statement was made because in the case of an index it gets used as long as the data returned back falls below 10%
(orso) from the total data in the table and in the case of the original query no matter how low I get the N still seq
scanbut I guess is again the above sescan-and-sort scenario (see below) 

create index fooidx on sp_i2birst_reg_staging_test (evt_id, status);
vacuum analyze sp_i2birst_reg_staging_test;

explain analyze select * from sp_i2birst_reg_staging_test where evt_id = 'ACSF17'
and status=1;
                                                                 QUERY PLAN
                    
 Index Scan using fooidx on sp_i2birst_reg_staging_test  (cost=0.28..202.91 rows=500 width=519) (actual
time=0.097..0.527rows=500 loops=1) 
   Index Cond: (((evt_id)::text = 'ACSF17'::text) AND (status = 1))
 Planning time: 1.024 ms
 Execution time: 0.766 ms
this gets 500 rows out of 6860

explain analyze select * from sp_i2birst_reg_staging_test where evt_id = 'ACSF17'
and status=1 limit 10;

                                                                   QUERY PLAN
                        
 Limit  (cost=0.28..4.33 rows=10 width=519) (actual time=0.073..0.105 rows=10 loops=1)
   ->  Index Scan using fooidx on sp_i2birst_reg_staging_test  (cost=0.28..202.91 rows=500 width=519) (actual
time=0.072..0.101rows=10 loops=1) 
         Index Cond: (((evt_id)::text = 'ACSF17'::text) AND (status = 1))
 Planning time: 0.280 ms
 Execution time: 0.173 ms

Back to the original

explain analyze select * from sp_i2birst_reg_staging_test where evt_id = 'ACSF17'
and status=0 limit 1 ;

                                                            QUERY PLAN
          
 Limit  (cost=0.00..0.13 rows=1 width=519) (actual time=0.021..0.021 rows=1 loops=1)
   ->  Seq Scan on sp_i2birst_reg_staging_test  (cost=0.00..548.40 rows=4239 width=519) (actual time=0.019..0.019
rows=1loops=1) 
         Filter: (((evt_id)::text = 'ACSF17'::text) AND (status = 0))
         Rows Removed by Filter: 1
 Planning time: 0.286 ms
 Execution time: 0.110 ms



— Armand