title: PostgreSQL: Limit in query makes it not use index
body: <p>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)</p>
  
  <pre><code>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
    -&gt;  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
    -&gt;  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
  </code></pre>
  
  <p>Is there a reason for this behavior?</p>
  
  <blockquote>
    <p>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</p>
  </blockquote>