From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
To: pgsql-hackers@postgresql.org
Subject: Re: Cached plans and statement generalization
Date: Mon, 24 Apr 2017 14:04:42 +0300
Message-ID: <7d90eab7-bccf-119e-3e61-c779a9b51197@postgrespro.ru> (raw)
In-Reply-To: <CAPpHfduYkxZ9ONZgeCs_Znw1pfTSMXX17oLwJR2iex4-GfaQxQ@mail.gmail.com>
References: <8e76d8fc-8b8c-14bd-d4d1-e9cf193a74f5@postgrespro.ru>
<CAPpHfduYkxZ9ONZgeCs_Znw1pfTSMXX17oLwJR2iex4-GfaQxQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 24.04.2017 13:24, Alexander Korotkov wrote:
> Hi, Konstantin!
>
> On Mon, Apr 24, 2017 at 11:46 AM, Konstantin Knizhnik
> <k.knizhnik@postgrespro.ru <mailto:k.knizhnik@postgrespro.ru>> wrote:
>
> There were a lot of discussions about query plan caching in
> hackers mailing list, but I failed to find some clear answer for
> my question and the current consensus on this question in Postgres
> community. As far as I understand current state is the following:
> 1. We have per-connection prepared statements.
> 2. Queries executed inside plpgsql code are implicitly prepared.
>
> It is not always possible or convenient to use prepared statements.
> For example, if pgbouncer is used to perform connection pooling.
> Another use case (which is actually the problem I am trying to
> solve now) is partitioning.
> Efficient execution of query to partitioned table requires
> hardcoded value for partitioning key.
> Only in this case optimizer will be able to construct efficient
> query plan which access only affected tables (partitions).
>
> My small benchmark for distributed partitioned table based on
> pg_pathman + postgres_fdw shows 3 times degrade of performance in
> case of using prepared statements.
> But without prepared statements substantial amount of time is
> spent in query compilation and planning. I was be able to speed up
> benchmark more than two time by
> sending prepared queries directly to the remote nodes.
>
>
> I don't think it's correct to ask PostgreSQL hackers about problem
> which arises with pg_pathman while pg_pathman is an extension
> supported by Postgres Pro.
> Since we have declarative partitioning committed to 10, I think that
> community should address this issue in the context of declarative
> partitioning.
> However, it's unlikely we can spot this issue with declarative
> partitioning because it still uses very inefficient constraint
> exclusion mechanism. Thus, issues you are writing about would become
> visible on declarative partitioning only when constraint exclusion
> would be replaced with something more efficient.
>
> Long story short, could you reproduce this issue without pg_pathman?
>
Sorry, I have mentioned pg_pathman just as example.
The same problems takes place with partitioning based on standard
Postgres inheritance mechanism (when I manually create derived tables
and specify constraints for them).
I didn't test yet declarative partitioning committed to 10, but I expect
the that it will also suffer from this problem (because is based on
inheritance).
But as I wrote, I think that the problem with plan caching is wider and
is not bounded just to partitioning.
> ------
> Alexander Korotkov
> Postgres Professional: http://www.postgrespro.com
> <http://www.postgrespro.com/;
> The Russian Postgres Company
>
>
--
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
Subject: Re: Cached plans and statement generalization
In-Reply-To: <7d90eab7-bccf-119e-3e61-c779a9b51197@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