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@gmail.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