Subject: Re: Cached plans and statement generalization
To: pgsql-hackers@postgresql.org
References: <8e76d8fc-8b8c-14bd-d4d1-e9cf193a74f5@postgrespro.ru>
 <CAPpHfduYkxZ9ONZgeCs_Znw1pfTSMXX17oLwJR2iex4-GfaQxQ@mail.gmail.com>
From: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Message-ID: <7d90eab7-bccf-119e-3e61-c779a9b51197@postgrespro.ru>
Date: Mon, 24 Apr 2017 14:04:42 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.8.0
MIME-Version: 1.0
In-Reply-To: 
 <CAPpHfduYkxZ9ONZgeCs_Znw1pfTSMXX17oLwJR2iex4-GfaQxQ@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------34564A416FBD016715220F7C"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is a multi-part message in MIME format.
--------------34564A416FBD016715220F7C
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit


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


--------------34564A416FBD016715220F7C
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <br>
    <br>
    <div class="moz-cite-prefix">On 24.04.2017 13:24, Alexander Korotkov
      wrote:<br>
    </div>
    <blockquote
cite="mid:CAPpHfduYkxZ9ONZgeCs_Znw1pfTSMXX17oLwJR2iex4-GfaQxQ@mail.gmail.com"
      type="cite">
      <div dir="ltr">Hi, Konstantin!<br>
        <div class="gmail_extra">
          <div>
            <div class="gmail_signature"><br>
            </div>
          </div>
          <div class="gmail_quote">On Mon, Apr 24, 2017 at 11:46 AM,
            Konstantin Knizhnik <span dir="ltr">&lt;<a
                moz-do-not-send="true"
                href="mailto:k.knizhnik@postgrespro.ru" target="_blank">k.knizhnik@postgrespro.ru</a>&gt;</span>
            wrote:<br>
            <blockquote class="gmail_quote" style="margin:0px 0px 0px
              0.8ex;border-left:1px solid
              rgb(204,204,204);padding-left:1ex">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:<br>
              1. We have per-connection prepared statements.<br>
              2. Queries executed inside plpgsql code are implicitly
              prepared.<br>
              <br>
              It is not always possible or convenient to use prepared
              statements.<br>
              For example, if pgbouncer is used to perform connection
              pooling.<br>
              Another use case (which is actually the problem I am
              trying to solve now) is partitioning.<br>
              Efficient execution of query to partitioned table requires
              hardcoded value for partitioning key.<br>
              Only in this case optimizer will be able to construct
              efficient query plan which access only affected tables
              (partitions).<br>
              <br>
              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.<br>
              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<br>
              sending prepared queries directly to the remote nodes.<br>
            </blockquote>
            <div><br>
            </div>
            <div>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.</div>
            <div>Since we have declarative partitioning committed to 10,
              I think that community should address this issue in the
              context of declarative partitioning.</div>
            <div>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.</div>
            <div><br>
            </div>
            <div>Long story short, could you reproduce this issue
              without pg_pathman?</div>
            <div><br>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
     <br>
    Sorry, I have mentioned pg_pathman just as example.  <br>
    The same problems takes place with partitioning based on standard
    Postgres inheritance mechanism (when I manually create derived
    tables and specify constraints for them).<br>
    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).<br>
    But as I wrote, I think that the problem with plan caching is wider
    and is not bounded just to partitioning.<br>
     <br>
    <br>
    <br>
    <blockquote
cite="mid:CAPpHfduYkxZ9ONZgeCs_Znw1pfTSMXX17oLwJR2iex4-GfaQxQ@mail.gmail.com"
      type="cite">
      <div dir="ltr">
        <div class="gmail_extra">
          <div class="gmail_quote">
            <div>
              <div>
                <div class="gmail_signature">------<br>
                  Alexander Korotkov<br>
                  Postgres Professional: <a moz-do-not-send="true"
                    href="http://www.postgrespro.com/" target="_blank">http://www.postgrespro.com</a><br>
                  The Russian Postgres Company</div>
              </div>
            </div>
            <div><br>
            </div>
            <div><br>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    <pre class="moz-signature" cols="72">-- 
Konstantin Knizhnik
Postgres Professional: <a class="moz-txt-link-freetext" href="http://www.postgrespro.com">http://www.postgrespro.com</a>
The Russian Postgres Company </pre>
  </body>
</html>

--------------34564A416FBD016715220F7C--