PostgreSQL: Limit in query makes it not use index

Question

I have a large table with BRIN index, and if I do query with limit it ignores the index and go for sequence scan, without it it uses index (I tried it several times with same results)

explain (analyze,verbose,buffers,timing,costs) 
select *
from testj.cdc_s5_gpps_ind
where id_transformace = 1293
limit 100

Limit  (cost=0.00..349.26 rows=100 width=207) (actual time=28927.179..28927.214 rows=100 loops=1)
  Output: id, date_key_trainjr...
  Buffers: shared hit=225 read=1680241
  ->  Seq Scan on testj.cdc_s5_gpps_ind  (cost=0.00..3894204.10 rows=1114998 width=207) (actual time=28927.175..28927.202 rows=100 loops=1)
        Output: id, date_key_trainjr...
        Filter: (cdc_s5_gpps_ind.id_transformace = 1293)
        Rows Removed by Filter: 59204140
        Buffers: shared hit=225 read=1680241
Planning Time: 0.149 ms
Execution Time: 28927.255 ms

explain (analyze,verbose,buffers,timing,costs) 
select *
from testj.cdc_s5_gpps_ind
where id_transformace = 1293

Bitmap Heap Scan on testj.cdc_s5_gpps_ind  (cost=324.36..979783.34 rows=1114998 width=207) (actual time=110.103..467.008 rows=1073725 loops=1)
  Output: id, date_key_trainjr...
  Recheck Cond: (cdc_s5_gpps_ind.id_transformace = 1293)
  Rows Removed by Index Recheck: 11663
  Heap Blocks: lossy=32000
  Buffers: shared hit=32056
  ->  Bitmap Index Scan on gpps_brin_index  (cost=0.00..45.61 rows=1120373 width=0) (actual time=2.326..2.326 rows=320000 loops=1)
        Index Cond: (cdc_s5_gpps_ind.id_transformace = 1293)
        Buffers: shared hit=56
Planning Time: 1.343 ms
JIT:
  Functions: 2
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 0.540 ms, Inlining 32.246 ms, Optimization 44.423 ms, Emission 22.524 ms, Total 99.732 ms
Execution Time: 537.627 ms

Is there a reason for this behavior?

PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit

Answer 1

There is a very simple (which is not to say good) reason for this. The planner assumes rows with id_transformace = 1293 are evenly distributed throughout the table, and so it will be able to collect 100 of them very quickly with a seq scan and then stop early. But this assumption is very wrong, and needs to go through a big chunk of the table to find 100 qualifying rows.

This assumption is not based on any statistics gathered on the table, so increasing the statistics target will not help. And extended statistics will not help either, as it only offers statistic between columns, not between a column and the physical ordering.

There are no good clean ways to solve this purely on the stock server side. One work-around is to set enable_seqscan=off before running the query, then reset afterwords. Another would be to add ORDER BY random() to your query, that way the planner knows it can't stop early. Or maybe the extension pg_hint_plan could help, I've never used it.

You might get it to change the plan by tweaking your some of your *_cost parameters, but that would likely make other things worse. Seeing the output of the EXPLAIN (ANALYZE, BUFFERS) of the LIMITed query run with enable_seqscan=off could inform that decision.