Re: [HACKERS] Cached plans and statement generalization - Mailing list pgsql-hackers

On 02.08.2019 11:25, Daniel Migowski wrote:
> Am 01.08.2019 um 18:56 schrieb Konstantin Knizhnik:
>> I decided to implement your proposal. Much simple version of 
>> autoprepare patch is attached.
>> At my computer I got the following results:
>>
>>  pgbench -M simple -S         22495 TPS
>>  pgbench -M extended -S    47633 TPS
>>  pgbench -M prepared -S    47683 TPS
>>
>>
>> So autoprepare speedup execution of queries sent using extended 
>> protocol more than twice and it is almost the same as with explicitly 
>> prepared statements.
>> I failed to create regression test for it because I do not know how 
>> to force psql to use extended protocol. Any advice is welcome.
>
> I am very interested in such a patch, because currently I use the same 
> functionality within my JDBC driver and having this directly in 
> PostgreSQL would surely speed things up.
>
> 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.

> 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.



> Thanks,
>
> Daniel Migowski
>
>
>
>

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company