Re: Incorrect result of bitmap heap scan.

We could not pin this down to a specific query, but here's one frequent
query that is running on the said table:

piers=> EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM events
WHERE priority = 'high' and ( events.fire_at <= now()  +
interval '1 hour' ) AND ( events.fire_at >= now() )
ORDER BY fire_at ASC
LIMIT 1000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2.80..2.81 rows=1 width=453) (actual
time=0.598..0.618 rows=222 loops=1)
   Buffers: shared hit=224
   ->  Sort  (cost=2.80..2.81 rows=1 width=453) (actual
time=0.598..0.606 rows=222 loops=1)
         Sort Key: fire_at
         Sort Method: quicksort  Memory: 241kB
         Buffers: shared hit=224
         ->  Bitmap Heap Scan on events  (cost=1.67..2.79
rows=1 width=453) (actual time=0.073..0.465 rows=222 loops=1)
               Recheck Cond: (((priority)::text = 'high'::text)
AND (fire_at <= (now() + '01:00:00'::interval)) AND (fire_at >=
now()))
               Heap Blocks: exact=219
               Buffers: shared hit=224
               ->  Bitmap Index Scan on
index_events_on_priority_fire_at  (cost=0.00..1.67 rows=1
width=0) (actual time=0.052..0.052 rows=222 loops=1)
                     Index Cond: (((queue)::text =
'high'::text) AND (fire_at <= (now() + '01:00:00'::interval))
AND (fire_at >= now()))
                     Buffers: shared hit=5
 Planning Time: 0.067 ms
 Execution Time: 0.646 ms
(15 rows)

Interestingly, before the upgrade we see that the majority of query
plans for this query are not executing any Bitmap Heap Scans:

---------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..2.78 rows=1 width=468) (actual
time=0.028..0.489 rows=217 loops=1)
   Buffers: shared hit=222
   ->  Index Scan using index_events_on_priority_fire_at on
events  (cost=0.56..2.78 rows=1 width=468) (actual
time=0.027..0.473 rows=217 loops=1)
         Index Cond: (((priority)::text = 'high'::text) AND
(fire_at <= (now() + '01:00:00'::interval)) AND (fire_at >= now()))
         Buffers: shared hit=222
 Planning:
   Buffers: shared hit=64
 Planning Time: 0.312 ms
 Execution Time: 0.512 ms
(9 rows)

Thanks in advance

On 9/16/25 3:25 PM, Andres Freund wrote:
> Hi,
>
> On September 16, 2025 7:57:54 AM EDT, "Core Studios Inc."<corestudiosinc(at)gmail(dot)com> wrote:
>> Hello,
>>
>> We noticed a sustained increased in IO Wait of read queries after upgrading from 13.13 to 13.21. Eventually, we narrowed it down to a spike in index_blocks_read of a certain table where Bitmap Heap Scans do happen.
>>
>> Do you think that this change (i.e. removing the optimization) could be what caused this regression?
> You're not providing enough details for us to answer that question. We'd need an explain verbose for the query.
>
> Greetings,
>
> Andres