Re: dynamic SQL - possible performance regression in 9.2

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Wednesday, January 2, 2013, Tom Lane wrote:
>> That scenario doesn't sound like it has anything to do with the one being
>> discussed in this thread. But what do you mean by "rule-based
>> partitioning" exactly? A rule per se wouldn't result in a cached plan
>> at all, let alone one with parameters, which would be necessary to
>> trigger any use of the custom-cached-plan code path.

> Sorry, when exiled to the hinterlands I have more time to test various
> things but not a good enough connectivity to describe them well. I'm
> attaching the test case to load 1e5 rows into a very skinny table with 100
> partitions using rules.

Ah. I see what's going on: the generic plan has got 100 separate
subplans that look like

Insert on foo_10 (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: ($2 = 10)

Insert on foo_11 (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: ($2 = 11)

Insert on foo_12 (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: ($2 = 12)

Insert on foo_13 (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: ($2 = 13)

while a custom plan simplifies that to something like

Insert on foo_10 (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false

Insert on foo_11 (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)

Insert on foo_12 (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false

Insert on foo_13 (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
One-Time Filter: false

(here foo_11 is the actual target). This is indeed a bit faster than
the generic plan, and would be more so if we tried harder to get rid of
no-op subplans entirely ... but it's not enough faster to justify the
extra planning time.

In the particular case at hand, we're getting estimated costs of about
1.01 for a custom plan versus 1.51 for the generic plan. So I think
that whether the 50% ratio is accurate is a bit beside the point --- the
real point is that we're only saving half a cost unit of estimated cost.
So that suggests that choose_custom_plan should impose not only a
minimum fractional savings but also a minimum absolute savings to
justify selecting the custom plan approach. I tried this and verified
that it fixed the problem:

diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/planc
index cbc7c49..1973ed3 100644
*** a/src/backend/utils/cache/plancache.c
--- b/src/backend/utils/cache/plancache.c
*************** choose_custom_plan(CachedPlanSource *pla
*** 990,995 ****
--- 990,997 ----
*/
if (plansource->generic_cost < avg_custom_cost * 1.1)
return false;
+ if (plansource->generic_cost < avg_custom_cost + 10)
+ return false;

return true;
}

but of course it's hard to say what that cutoff number ought to be.

Back during the development of the plancache patch we speculated about
how we might take cost-of-planning into account in deciding whether to
use a custom plan or not. The root problem is that we've got no data
about how much work the planner does for a given query and how that
compares to estimated-cost numbers. There was some argument that we
could just take gettimeofday() measurements and drive it off that, but
I don't care for that too much; for one thing it would make the behavior
unstable with variations in system load.

Anyway the bottom line is that we never went back to do the research
about what the policy followed by choose_custom_plan ought to be.
It's probably time to think about that, or at least find a better
stopgap solution than what's in there.

regards, tom lane