Re: [HACKERS] Cached plans and statement generalization

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
Cc: Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] Cached plans and statement generalization
Date: 2019-08-01 16:56:53
Message-ID: 6c91d4ba-1a77-ba64-39ee-fda26dcfc9bd@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 31.07.2019 19:56, Heikki Linnakangas wrote:
> On 09/07/2019 23:59, Konstantin Knizhnik wrote:
>> Fixed patch version of the path is attached.
>
> Much of the patch and the discussion has been around the raw parsing,
> and guessing which literals are actually parameters that have been
> inlined into the SQL text. Do we really need to do that, though? The
> documentation mentions pgbouncer and other connection poolers, where
> you don't have session state, as a use case for this. But such
> connection poolers could and should still be using the extended query
> protocol, with Parse+Bind messages and query parameters, even if they
> don't use named prepared statements. I'd want to encourage
> applications and middleware to use out-of-band query parameters, to
> avoid SQL injection attacks, regardless of whether they use prepared
> statements or cache query plans. So how about dropping all the raw
> parse tree stuff, and doing the automatic caching of plans based on
> the SQL string, somewhere in the exec_parse_message? Check the
> autoprepare cache in exec_parse_message(), if it was an "unnamed"
> prepared statement, i.e. if the prepared statement name is an empty
> string.
>
> (I'm actually not sure if exec_parse/bind_message is the right place
> for this, but I saw that your current patch did it in
> exec_simple_query, and exec_parse/bind_message are the equivalent of
> that for the extended query protocol).
>
> - Heikki

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.

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

Attachment Content-Type Size
autoprepare-extended-1.patch text/x-patch 23.1 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2019-08-01 17:06:35 Re: using explicit_bzero
Previous Message Alexey Kondratov 2019-08-01 16:53:09 Re: [Patch] pg_rewind: options to use restore_command from recovery.conf or command line