Re: Why is this query touching 4gb of buffers?

hubert depesz lubaczewski <depesz(at)depesz(dot)com> writes:
> First run of the query generated:
> ...
> -> Index Scan using index_some_table_pending on some_table (cost=0.42..178322.57 rows=611988 width=16) (actual time=27962.567..27962.567 rows=0 loops=1)
> Index Cond: (send_at <= '2025-10-23 12:35:48'::timestamp without time zone)
> Buffers: shared hit=4624 read=117838 dirtied=486

> Then, immediately I reran it, without reindex, without analyze, without anything. And I got:

> -> Index Scan using index_some_table_pending on some_table (cost=0.42..178328.27 rows=612009 width=16) (actual time=0.438..0.438 rows=0 loops=1)
> Index Cond: (send_at <= '2025-10-23 12:35:48'::timestamp without time zone)
> Buffers: shared hit=424

> Time is irrelevant, the point is that we are going down from ~120k buffers
> "touched" to 424 buffers. What is going on?

The first execution probably had to set hint bits on a whole lot
of recently-deleted rows.

regards, tom lane