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

help / color / mirror / Atom feed
From: Daniel Migowski <dmigowski@ikoffice.de>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: [HACKERS] Cached plans and statement generalization
Date: Fri, 2 Aug 2019 10:25:30 +0200
Message-ID: <3ab03f54-b2bd-4b19-d8fb-19579df67caa@ikoffice.de> (raw)
In-Reply-To: <6c91d4ba-1a77-ba64-39ee-fda26dcfc9bd@postgrespro.ru>
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>

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

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.

Thanks,

Daniel Migowski







view thread (107+ messages)  latest in thread

Message-ID: <3ab03f54-b2bd-4b19-d8fb-19579df67caa@ikoffice.de>
Permalink:  ../3ab03f54-b2bd-4b19-d8fb-19579df67caa@ikoffice.de/
Also on:    postgresql.org/message-id/3ab03f54-b2bd-4b19-d8fb-19579df67caa@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, pgsql-hackers@lists.postgresql.org
  Subject: Re: [HACKERS] Cached plans and statement generalization
  In-Reply-To: <3ab03f54-b2bd-4b19-d8fb-19579df67caa@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