Content-Type: multipart/alternative;
 boundary="----sinikael-?=_1-16201367573870.026728409517000618"
From: Alex <cdalxndr@yahoo.com>
Subject: Re: 15x slower PreparedStatement vs raw query
To: Rick Otten <rottenwindfish@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>, "pgsql-performa."
 <pgsql-performance@postgresql.org>
User-Agent: Desktop
Message-Id: <6cd0e931a59e1.4c58a67a1e5f6@yahoo.com>
References: <3f8c88d8a6ef9.d82fa04cf610d.ref@yahoo.com>
 <3f8c88d8a6ef9.d82fa04cf610d@yahoo.com>
 <20210503201811.GA27406@telsasoft.com>
 <49daf090ac5b1.96e1ada6021ee@yahoo.com>
 <CAMAYy4JhWn+FhwhzHUWKiDy8r62Bw4eT5QVq=kQDYyjJheV4ag@mail.gmail.com>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 04 May 2021 13:59:16 +0000
MIME-Version: 1.0
Content-Length: 5521
Archived-At: 
 <https://www.postgresql.org/message-id/6cd0e931a59e1.4c58a67a1e5f6%40yahoo.com>
Precedence: bulk

------sinikael-?=_1-16201367573870.026728409517000618
Content-Type: text/plain; format=flowed
Content-Transfer-Encoding: 7bit

"Powerful general purpose relational database" but not smart... 
I propose a feature to use information from previously executed queries to 
adjust the query plan time accordingly.
Reusing the same generic plan may and will lead to very long execution 
times.
Rick Otten wrote:
On Tue, May 4, 2021 at 6:05 AM Alex <cdalxndr@yahoo.com 
<mailto:cdalxndr@yahoo.com> > wrote:
Shouldn't this process be automatic based on some heuristics?
Saving 10ms planning but costing 14s execution is catastrophic.
For example, using some statistics to limit planner time to some percent of 
of previous executions. 
This way, if query is fast, planning is fast, but if query is slow, more 
planning can save huge execution time.
This is a better general usage option and should be enabled by default, and 
users who want fast planning should set the variable to use the generic 
plan.
"fast" and "slow" are relative things. There are many queries that I would 
be overjoyed with if they completed in 5 _minutes_. And others where they 
have to complete within 100ms or something is really wrong. We don't really 
know what the execution time is until the query actually executes. Planning 
is a guess for the best approach.
Another factor is whether the data is in cache or out on disk. Sometimes 
you don't really know until you try to go get it. That can significantly 
change query performance and plans - especially if some of the tables in a 
query with a lot of joins are in cache and some aren't and maybe some have 
to be swapped out to pick up others.
If you are running the same dozen queries with different but similarly 
scoped parameters over and over, one would hope that the system would 
slowly tune itself to be highly optimized for those dozen queries. That is 
a pretty narrow use case for a powerful general purpose relational database 
though.

------sinikael-?=_1-16201367573870.026728409517000618
Content-Type: text/html; format=flowed
Content-Transfer-Encoding: 7bit

<html><head></head><body><div>
                    
     
     
       
         <meta charset="utf-8">
       
       
         <div id="compose-body-wrapper" dir="auto"><div 
dir="auto">"Powerful general purpose relational database" but not 
smart...&nbsp;</div><div dir="auto"><br></div><div dir="auto">I propose a 
feature to use information from previously executed queries to adjust the 
query plan time accordingly.</div><div dir="auto">Reusing the same generic 
plan may and will lead to very long execution times.</div><div 
dir="auto"><br></div><div dir="auto" 
id="tmjah_g_1299"><p><br></p></div><br></div><div class="replyHeader" 
dir="auto">Rick Otten wrote:</div><br><br><div><blockquote 
cite="mid:CAMAYy4JhWn+FhwhzHUWKiDy8r62Bw4eT5QVq=kQDYyjJheV4ag@mail.gmail.com" 
type="cite" style="margin:0 0 0 .8ex;border-left:1px #ccc 
solid;padding-left:1ex"><div dir="ltr"><div dir="ltr"><br></div><br><div 
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, May 4, 2021 
at 6:05 AM Alex &lt;<a 
href="mailto:cdalxndr@yahoo.com">cdalxndr@yahoo.com</a>&gt; 
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
                     
      
      
        
          
        
        
          <div id="gmail-m_7228320884962311160compose-body-wrapper" 
dir="auto"><div dir="auto">Shouldn't this process be automatic based on 
some 
heuristics?</div><div dir="auto"><br></div><div dir="auto"> </div><div 
dir="auto">Saving 10ms planning but costing 14s execution is 
catastrophic.</div><div dir="auto"><br></div><div dir="auto">For example, 
using some statistics to limit planner time to some percent of of previous 
executions.&nbsp;</div><div dir="auto">This way, if query is fast, planning 

is fast, but if query is slow, more planning can save huge execution 
time.</div><div dir="auto">This is a better general usage option and should 

be enabled by default, and users who want fast planning should set the 
variable to use the generic plan.</div><div dir="auto" 
id="gmail-m_7228320884962311160tmjah_g_1299"><p><br></p></div></div></blockquote><div><br></div><div>"fast" 
and "slow" are relative things.&nbsp; There are many queries that I would 
be overjoyed with if they completed in 5 _minutes_.&nbsp; And others where 
they have to complete within 100ms or something is really wrong.&nbsp; We 
don't really know what the execution time is until the query actually 
executes.&nbsp; Planning is a guess for the best 
approach.</div><div><br></div><div>Another factor is whether the data is in 
cache or out on disk.&nbsp; Sometimes you don't really know until you try 
to go get it.&nbsp; That can significantly change query performance and 
plans - especially if some of the tables in a query with a lot of joins are 
in cache and some aren't and maybe some have to be swapped out to pick up 
others.</div><div><br></div><div>If you are running the same dozen queries 
with different but similarly scoped parameters over and over, one would 
hope that the system would slowly tune itself to be highly optimized for 
those dozen queries.&nbsp; That is a pretty narrow use case for a powerful 
general purpose relational database 
though.</div><div><br></div></div></div></blockquote></div>
       
     
   
                   </div></body></html>
------sinikael-?=_1-16201367573870.026728409517000618--