Skip site navigation (1) Skip section navigation (2)

Re: Searching for the cause of a bad plan

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Csaba Nagy <nagy(at)ecircle-ag(dot)com>, postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Searching for the cause of a bad plan
Date: 2007-09-27 15:24:37
Message-ID: 1190906677.4194.78.camel@ebony.site (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

pgsql-performance by date

Next:From: Kevin KempterDate: 2007-09-27 17:42:06
Subject: Tuning for warm standby
Previous:From: Csaba NagyDate: 2007-09-27 15:16:06
Subject: Re: Searching for the cause of a bad plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group