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
