Re: Why is this query touching 4gb of buffers? - Mailing list pgsql-general

hubert depesz lubaczewski <depesz@depesz.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.567rows=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.438rows=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