Message-ID: <ed9e8643-a7ed-48b7-abea-6eb74e71e043@gmail.com>
Date: Thu, 18 Sep 2025 13:55:02 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Why isn't PG using an index-only scan?
To: David Rowley <dgrowleyml@gmail.com>
Cc: Jean-Christophe BOGGIO <postgresql@thefreecat.org>,
 "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
References: <8f25c08c-acb2-47ca-b8c1-6664fc31a361@thefreecat.org>
 <CAApHDvoUdvw68oHnfACQE=u8XPJ+Te49dZsfC2yW+DVfzdT2+Q@mail.gmail.com>
 <32dc1fe9-175a-4894-b4c4-c6cdc6a76a16@gmail.com>
 <CAApHDvodjGfmN8_na70Bn5OVPO1-gQo64s6n=HUxqnUUQFsUZA@mail.gmail.com>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAApHDvodjGfmN8_na70Bn5OVPO1-gQo64s6n=HUxqnUUQFsUZA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/ed9e8643-a7ed-48b7-abea-6eb74e71e043%40gmail.com>
Precedence: bulk

On 18/9/2025 13:35, David Rowley wrote:
> On Thu, 18 Sept 2025 at 19:55, Andrei Lepikhov <lepihov@gmail.com> wrote:
>> Imagine if we had a hook within the ExecProcNode. In that scenario, we
>> could create a trivial extension that would stop the query after, let's
>> say, 10 minutes of execution and display the current state. This would
>> give us more reliable data on estimation and the state of the plan tree.
> 
> I recall something along those lines existing once in the extension
> world. Or maybe just from a previous employer. I don't recall many of
> the details. Maybe something like a function you pass an SQL string
> and if you cancelled the query it reported the EXPLAIN ANALYZE done so
> far. I assume it must have done something like LOG it as it couldn't
> have shown the EXPLAIN as query results on cancel.
It looks like a makeshift solution. By implementing a callback, we could 
elevate 'interrupter' to a first-class feature, enabling us to monitor 
the state of the entire query tree (it is especially cool in EXPLAIN 
ANALYZE mode when we may be OK with a partial result).
What's more interesting if Robert's work on nodes' extensibility 
successes, it enables modules to save planning decisions in the plan. 
Thereby, the in-executor callback provides a means to kick off the query 
planned with incorrect estimations (e.g., too many spilled tuples).

Perhaps we should start working on introducing this type of callback/ hook?

-- 
regards, Andrei Lepikhov