Subject: Re: [HACKERS] Cached plans and statement generalization
To: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>,
	<pgsql-hackers@lists.postgresql.org>
References: 
 <CA+TgmoZAHc4wqL3HvVmTd87s=bobp_8_7R9H=B_kH6q4GcA=Yg@mail.gmail.com>
 <EDA4195584F5064680D8130B1CA91C453D0182@G01JPEXMBYT04>
 <6ea1db86-b300-b57c-0f9d-a55dd3966175@postgrespro.ru>
 <9A6E5062D5D4DB458C80C2B2920BD71D6820CB@g01jpexmbkw23>
 <554d0730-f6fc-d1be-fe78-e63973ec621f@postgrespro.ru>
 <35be0423-36bc-cadc-bef8-02d475751f7d@postgrespro.ru>
 <7603a101-6b79-2328-2b4b-3bc3217992e5@postgrespro.ru>
 <CA+hUKGJK2GZewMumkXtPjyATMu2tyEmg53R23WCETY=y48RxQw@mail.gmail.com>
 <f71ca2cb-b2c0-d6ca-4532-822b25f1f81e@postgrespro.ru>
 <CA+hUKGKuxM94eVyBii=0e1bWpa-A05f2L6H-JSij0H5o7t+ZqA@mail.gmail.com>
 <3304c34b-4a9c-909c-4ee1-d8e7f6dcb2de@postgrespro.ru>
 <CA+hUKGL9_ohxMBq04N8qwn-vEaaan2N-06FaXsTz9+seDddN3g@mail.gmail.com>
 <66f47595-1b93-ef67-fdd6-c5414e0f94c3@postgrespro.ru>
 <e9cce21f-942f-31f4-54e4-ace7f2de1a5f@iki.fi>
 <6c91d4ba-1a77-ba64-39ee-fda26dcfc9bd@postgrespro.ru>
 <3ab03f54-b2bd-4b19-d8fb-19579df67caa@ikoffice.de>
 <bd7fce7b-7734-d72e-1809-2d0d073e4034@postgrespro.ru>
From: Daniel Migowski <dmigowski@ikoffice.de>
Message-ID: <b68e36eb-a0ea-3b28-62c4-a72f06fe4dfb@ikoffice.de>
Date: Fri, 2 Aug 2019 11:01:47 +0200
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:60.0) Gecko/20100101
 Thunderbird/60.8.0
MIME-Version: 1.0
In-Reply-To: <bd7fce7b-7734-d72e-1809-2d0d073e4034@postgrespro.ru>
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk

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