Re: [HACKERS] Cached plans and statement generalization

Am 02.08.2019 um 10:57 schrieb Konstantin Knizhnik:
> On 02.08.2019 11:25, Daniel Migowski wrote:
>>
>> I have two suggestions however:
>>
>> 1. Please allow to gather information about the autoprepared 
>> statements by returning them in pg_prepared_statements view. I would 
>> love to monitor usage of them as well as the memory consumption that 
>> occurs. I suggested a patch to display that in 
>> https://www.postgresql.org/message-id/41ED3F5450C90F4D8381BC4D8DF6BBDCF02E10B4@EXCHANGESERVER.ikoffice.de 
>>
>>
> Sorry, but there is pg_autoprepared_statements view. I think that it 
> will be better to distinguish explicitly and implicitly prepared 
> statements, will not it?
> Do you want to add more information to this view? Right now it shows 
> query string, types of parameters and number of this query execution.
My sorry, I didn't notice it in the patch. If there is another view for 
those that's perfectly fine.
>> 2. Please not only use a LRU list, but maybe something which would 
>> prefer statements that get reused at all. We often create ad hoc 
>> statements with parameters which don't really need to be kept. Maybe 
>> I can suggest an implementation of an LRU list where a reusal of a 
>> statement not only pulls it to the top of the list but also increases 
>> a reuse counter. When then a statement would drop off the end of the 
>> list one checks if the reusal count is non-zero, and only really 
>> drops it if the resual count is zero. Else the reusal count is 
>> decremented (or halved) and the element is also placed at the start 
>> of the LRU list, so it is kept a bit longer.
>>
> There are many variations of LRU and alternatives: clock, segmented 
> LRU, ...
> I agree that classical LRU may be not the best replacement policy for 
> autoprepare.
> Application is either use static queries, either constructing them 
> dynamically. It will be better to distinguish queries executed at 
> least twice from one-shot queries.
> So may be SLRU will be the best choice. But actually situation you 
> have described (We often create ad hoc statements with parameters 
> which don't really need to be kept)
> is not applicable to atutoprepare. Autoprepare deals only with 
> statements which are actually executed.
>
> We have another patch which limits number of stored prepared plans to 
> avoid memory overflow (in case of using stored procedures which 
> implicitly prepare all issued queries).
> Here choice of replacement policy is more critical.

Alright, just wanted to have something better than a LRU, so it is not a 
stepback from the implementation in the JDBC driver for us.

Kind regards,
Daniel Migowski