public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: [email protected]
Cc: PostgreSQL General <[email protected]>
Subject: Re: Why is this query touching 4gb of buffers?
Date: Fri, 24 Oct 2025 08:54:06 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
hubert depesz lubaczewski <[email protected]> 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
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Why is this query touching 4gb of buffers?
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox