From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Csaba Nagy <nagy@ecircle-ag.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Searching for the cause of a bad plan
Date: Thu, 27 Sep 2007 16:24:37 +0100
Message-ID: <1190906677.4194.78.camel@ebony.site> (raw)
In-Reply-To: <29186.1190904025@sss.pgh.pa.us>
References: <1190369024.4661.173.camel@PCD12478>
<6175.1190390913@sss.pgh.pa.us>
<1190396352.4202.109.camel@ebony.site>
<13644.1190397220@sss.pgh.pa.us>
<1190399879.4202.127.camel@ebony.site>
<27857.1190417434@sss.pgh.pa.us>
<1190640429.4181.165.camel@ebony.site>
<1190818357.5430.29.camel@PCD12478>
<21582.1190820137@sss.pgh.pa.us>
<1190894004.5430.46.camel@PCD12478>
<29186.1190904025@sss.pgh.pa.us>
On Thu, 2007-09-27 at 10:40 -0400, Tom Lane wrote:
> And yet there's another trap here: if the parameter you passed in
> chanced to be one of the very common values, a plan that was optimized
> for a small number of matches would perform terribly.
I wonder could we move prepare_threshold onto the server? (from JDBC).
When we prepare a statement, we record the current setting of the
prepare_threshold parameter into the plan for that statement. Valid
settings are -1, 0 or more.
If the setting is -1 then we always re-plan the statement.
If the setting is 0 then we plan the statement generically.
If the setting is > 0 then we plan the statement according to the
specific parameter value and then decrement the prepare count, so
eventually the executions will hit 0 (above).
That would also simplify JDBC and allow the functionality from other
clients, as well as from within PLs.
We could also force the plan to be -1 when the planning involves
something that would force it to be a one-time plan, e.g. constraint
exclusion. (So we could then get rid of that parameter).
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
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-performance@postgresql.org
Cc: simon@2ndquadrant.com, tgl@sss.pgh.pa.us, nagy@ecircle-ag.com
Subject: Re: Searching for the cause of a bad plan
In-Reply-To: <1190906677.4194.78.camel@ebony.site>
* 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