Message-ID: <2f2bde53-972d-402c-9da3-c5467cca00bf@gmail.com>
Date: Fri, 18 Jul 2025 11:31:01 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: track generic and custom plans in pg_stat_statements
To: Sami Imseih <samimseih@gmail.com>
Cc: Greg Sabino Mullane <htamfids@gmail.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>,
 Nikolay Samokhvalov <nik@postgres.ai>,
 Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
References: 
 <CAA5RZ0uFw8Y9GCFvafhC=OA8NnMqVZyzXPfv_EePOt+iv1T-qQ@mail.gmail.com>
 <CAA5RZ0sxV3APLzmnH1Jt0ehmjzYAStNqcZeEAma_D0VkmjkX8w@mail.gmail.com>
 <1d667f28-c739-417b-b024-7f5c1004a008@tantorlabs.com>
 <CAA5RZ0sUcVngZHLG_Q_N=OD9QhC+SJf0dx7HsPX9U4SJQF20gA@mail.gmail.com>
 <CAA5RZ0t5w8mx5hb9KEFv29FFK+AScOSUuXE4raQ_pHQTFQUotw@mail.gmail.com>
 <CAM527d9jo-EPHwVwGym3fxmiebtKhhM9q8F5WGsK7S4XCsMGrA@mail.gmail.com>
 <CAA5RZ0vGBFHvGb4iZdmSADuybkHGNrxVHknTGaEhS2=2LcM56g@mail.gmail.com>
 <CAA5RZ0tpO_LwFNW3UJGLfK2_BNS+bgKKrKS10M1saOoGW42sKw@mail.gmail.com>
 <60f09467-bfe6-49bc-a47a-fe887050a23a@tantorlabs.com>
 <CAA5RZ0v4Lqc3KOzFCOJd0hjBpF10W=-E6xn0z_HizZY9uxPR0w@mail.gmail.com>
 <CAA5RZ0vK_fah+ZS00hdg9uH2J_zBgV75x6ojYrc6+OJudnbKtQ@mail.gmail.com>
 <5f775987-8a93-452d-bb3f-a1de53836406@gmail.com>
 <CAA5RZ0uxcVu9MPXQc6qJ5mtYhH=_7b7jjaDnvg6Sft8jJ1NbXw@mail.gmail.com>
 <56d31e81-79d8-40bd-b957-0d6dc7c46589@gmail.com>
 <CAA5RZ0tdc4HeHdFRvVPWBdXP1JwCSjPPgiQyFk8fwhv=4fKmjw@mail.gmail.com>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAA5RZ0tdc4HeHdFRvVPWBdXP1JwCSjPPgiQyFk8fwhv=4fKmjw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/2f2bde53-972d-402c-9da3-c5467cca00bf%40gmail.com>
Precedence: bulk

On 17/7/2025 20:19, Sami Imseih wrote:
> Thanks for the clarification. I see what you're getting at now.
Thanks for reading this!
> 
> You're suggesting adding CachedPlanSource to QueryDesc instead of
> CachedPlan. This would allow extensions to access the statistics and cost
> information from the CachedPlanSource, which would help tools like
> pg_stat_statements track planning data, as we are trying to do with this
> patch. It could also support other use cases, such as allowing extensions to
> modify the costs in order to force a generic or custom plan. I had not
> considered that second use case, but if there is a good case for it, I am not
> opposed.
Hmm, I don't propose modifying costs. The focus is on resetting the plan 
cache decision that PostgreSQL has made in automatic mode. During the 
DBMS operation, various factors may cause a generic plan to be 
suboptimal or make it more desirable as well. Discussions from 2010 to 
2013 indicate that the community recognised the problem and discovered 
an approach based on execution time and real efforts rather than a 
cost-based method. While I doubt it could be ideal as a core solution, 
an extension may potentially do it for the sake of TPS maximisation. 
What we need is a way to access the plan cache entry.
> 
> Adding CachedPlanSource to QueryDesc seems doable. However, Michael
> previously objected to adding CachedPlan to QueryDesc. Is there any
> similar hesitation about including CachedPlanSource?
I agree that we should investigate further to find the most optimal 
solution. Personally, I'm open to including an internal reference to a 
plan cache entry within the QueryDesc, as long as the plan has its roots 
there.

> Andrei, do we actually need access to CachedPlanSource::cplan? For
> tracking the plan cache mode in pg_stat_statements, it be sufficient
> to add a new boolean field such as is_last_plan_generic to
> CachedPlanSource. Do you have another use case you have in mind
> that would require a cplan field that references either the generic or
> custom plan?
I'm not entirely sure. I followed your idea of referencing the entire 
list of planned statements during the execution of a single statement. 
The is_last_plan_generic field may be sufficient at first glance.

-- 
regards, Andrei Lepikhov