|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|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
On 01.08.2019 19:56, Konstantin Knizhnik wrote:
> 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
>> (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.
Slightly improved and rebased version of the patch.
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
|Next Message||Robert Haas||2019-08-05 17:06:02||Re: [PATCH] Stop ALTER SYSTEM from making bad assumptions|
|Previous Message||Andres Freund||2019-08-05 16:53:37||Re: [PATCH] Stop ALTER SYSTEM from making bad assumptions|