On Wednesday, January 2, 2013, Tom Lane wrote:
Jeff Janes <jeff.janes@gmail.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 generic
> plan. Whatever savings the custom plan may have are clearly less than the
> 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 was introduced)
JJ /usr/local/pgsql_REL9_1_7/
Time: 64252.6907920837 ms
JJ origin/
Time: 186657.824039459 ms
JJ origin_reduce_copies/
Time: 185370.236873627 ms
JJ origin_one_shot/
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.[012] 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);
BEGIN
tablename = 'foo_' || new.partition;
EXECUTE 'INSERT INTO '|| tablename ||' VALUES (($1).*)' USING NEW ;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER foo_insert_trigger
BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE foo_insert_trigger();
Cheers,
Jeff