On Wednesday, January 2, 2013, Tom Lane wrote:
> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> > Using a RULE-based partitioning instead with row by row insertion, the
> > plancache changes slowed it down by 300%, and this patch doesn't change
> > that. But that seems to be down to the insertion getting planned
> > repeatedly, because it decides the custom plan is cheaper than the
> > plan. Whatever savings the custom plan may have are clearly less than
> > cost of doing the planning repeatedly.
> 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.
Right, it is not related to the dynamic SQL, but is to the plan-cache.
> Test cases are way more interesting than hand-wavy complaints.
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.
"origin" is from a few days ago, "origin_reduce_copies" is Heikki's patch,
and "origin_one_shot" is your now-committed patch. (unshown are
e6faf910d75027 and e6faf910d75027_prev, but that is where the regression
Time: 64252.6907920837 ms
Time: 186657.824039459 ms
Time: 185370.236873627 ms
Time: 189104.484081268 ms
The root problem is that it thinks the generic plan costs about 50% more
than the custom one. I don't know why it thinks that, or how much it is
worth chasing it down.
On the other hand, your patch does fix almost all of the 9.2.
regression of using the following dynamic SQL trigger (instead of RULES) to
load into the same test case.
CREATE OR REPLACE FUNCTION foo_insert_trigger()
RETURNS trigger AS $$
DECLARE tablename varchar(24);
tablename = 'foo_' || new.partition;
EXECUTE 'INSERT INTO '|| tablename ||' VALUES (($1).*)' USING NEW ;
CREATE TRIGGER foo_insert_trigger
BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE foo_insert_trigger();
Description: text/x-perl (1.6 KB)
In response to
pgsql-hackers by date
|Next:||From: Tatsuo Ishii||Date: 2013-01-06 02:03:18|
|Subject: Re: too much pgbench init output|
|Previous:||From: Tom Lane||Date: 2013-01-05 22:18:16|
|Subject: Re: [PERFORM] Slow query: bitmap scan troubles|