Re: limit and query planner

> On Jun 5, 2018, at 2:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> armand pirvu <armand(dot)pirvu(at)gmail(dot)com> writes:
>> My question is: I suspect the limit simply limits the fethching to the first n-records retrieved and has no implications whatsoever 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% (or so) 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 scan but 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.527 rows=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.101 rows=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=1 loops=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