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?
------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
