Re: dynamic SQL - possible performance regression in 9.2
От | Tom Lane |
---|---|
Тема | Re: dynamic SQL - possible performance regression in 9.2 |
Дата | |
Msg-id | 25760.1357800208@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | dynamic SQL - possible performance regression in 9.2 (Jeff Janes <jeff.janes@gmail.com>) |
Список | pgsql-hackers |
Jeff Janes <jeff.janes@gmail.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-TimeFilter: ($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.01rows=1 width=0) One-Time Filter: ($2 = 13) while a custom plan simplifies that to something likeInsert on foo_10 (cost=0.00..0.01 rows=1 width=0) -> Result (cost=0.00..0.01rows=1 width=0) One-Time Filter: falseInsert 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=1width=0) One-Time Filter: falseInsert on foo_13 (cost=0.00..0.01 rows=1 width=0) -> Result (cost=0.00..0.01rows=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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Pavan DeolaseeДата:
Сообщение: Re: 9.2.1 & index-only scans : abnormal heap fetches after VACUUM FULL