pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Daniel Migowski <dmigowski@ikoffice.de>
To: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: [HACKERS] Cached plans and statement generalization
Date: Fri, 2 Aug 2019 11:01:47 +0200
Message-ID: <b68e36eb-a0ea-3b28-62c4-a72f06fe4dfb@ikoffice.de> (raw)
In-Reply-To: <bd7fce7b-7734-d72e-1809-2d0d073e4034@postgrespro.ru>
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>

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






view thread (107+ messages)  latest in thread

Message-ID: <b68e36eb-a0ea-3b28-62c4-a72f06fe4dfb@ikoffice.de>
Permalink:  ../b68e36eb-a0ea-3b28-62c4-a72f06fe4dfb@ikoffice.de/
Also on:    postgresql.org/message-id/b68e36eb-a0ea-3b28-62c4-a72f06fe4dfb@ikoffice.de

 · 

reply
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: dmigowski@ikoffice.de, k.knizhnik@postgrespro.ru, pgsql-hackers@lists.postgresql.org
  Subject: Re: [HACKERS] Cached plans and statement generalization
  In-Reply-To: <b68e36eb-a0ea-3b28-62c4-a72f06fe4dfb@ikoffice.de>

* 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