Re: Cached plans and statement generalization

On 24.04.2017 13:24, Alexander Korotkov wrote:
> Hi, Konstantin!
>
> On Mon, Apr 24, 2017 at 11:46 AM, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru <mailto:k(dot)knizhnik(at)postgrespro(dot)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