From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: Daniel Migowski <dmigowski@ikoffice.de>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: [HACKERS] Cached plans and statement generalization
Date: Fri, 2 Aug 2019 11:57:31 +0300
Message-ID: <bd7fce7b-7734-d72e-1809-2d0d073e4034@postgrespro.ru> (raw)
In-Reply-To: <3ab03f54-b2bd-4b19-d8fb-19579df67caa@ikoffice.de>
References: <CA+TgmoZAHc4wqL3HvVmTd87s=bobp_8_7R9H=B_kH6q4GcA=Yg@mail.gmail.com>
<CA+q6zcWKsaiFz6oWA1JXdJWNwSSk+wUyuOVO2nryCCSxrr3KRw@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>
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.ikoffi...
>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: k.knizhnik@postgrespro.ru, dmigowski@ikoffice.de, pgsql-hackers@lists.postgresql.org
Subject: Re: [HACKERS] Cached plans and statement generalization
In-Reply-To: <bd7fce7b-7734-d72e-1809-2d0d073e4034@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox