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