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."<[email protected]> 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
