________________________________ > Date: Fri, 21 Dec 2012 11:56:25 -0500 > Subject: Re: [HACKERS] Writing Trigger Functions in C > From: cbbro...@gmail.com > To: charle...@outlook.com > CC: pgsql-hackers@postgresql.org > > On Fri, Dec 21, 2012 at 11:25 AM, Charles Gomes > <charle...@outlook.com<mailto:charle...@outlook.com>> wrote: > > > > Hello guys, > > > > I've been finding performance issues when using a trigger to modify > inserts on a partitioned table. > > If using the trigger the total time goes from 1 Hour to 4 hours. > > > > The trigger is pretty simple: > > > > CREATE OR REPLACE FUNCTION quotes_insert_trigger() > > RETURNS trigger AS $ > > BEGIN > > EXECUTE 'INSERT INTO quotes_'|| > to_char(new.received_time,'YYYY_MM_DD') ||' VALUES (($1).*)' USING NEW > ; > > RETURN NULL; > > END; > > $ > > LANGUAGE plpgsql; > > > > I've seen that some of you guys have worked on writing triggers in C. > > > > Does anyone have had an experience writing a trigger for partitioning > in C ? > > I'd want to be very careful about assuming that implementing the > trigger function in C > would necessarily improve performance. It's pretty likely that it > wouldn't help much, > as a fair bit of the cost of firing a trigger have to do with figuring > out which function to > call, marshalling arguments, and calling the function, none of which would > magically disappear by virtue of implementing in C. > > A *major* cost that your existing implementation has is that it's re-planning > the queries for every single invocation. This is an old, old problem > from the > Lisp days, "EVAL considered evil" > <http://stackoverflow.com/questions/2571401/why-exactly-is-eval-evil> > > The EXECUTE winds up replanning queries every time the trigger fires. > > If you can instead enumerate the partitions explicitly, putting them > into (say) a > CASE clause, the planner could generate the plan once, rather than a million > times, which would be a HUGE savings, vastly greater than you could > expect from > recoding into C. > > The function might look more like: > > create or replace function quotes_insert_trigger () returns trigger as $$ > declare > c_rt text; > begin > c_rt := to_char(new.received_time, 'YYYY_MM_DD'); > case c_rt > when '2012_03_01' then > insert into 2012_03_01 values (NEW.*) using new; > when '2012_03_02' then > insert into 2012_03_02 values (NEW.*) using new; > else > raise exception 'Need a new partition function for %', c_rt; > end case; > end $$ language plpgsql; > > You'd periodically need to change the function to reflect the existing set of > partitions, but that's cheaper than creating a new partition. > > The case statement gets more expensive (in effect O(n) on the number of > partitions, n) as the number of partitions increases. You could split > the date into pieces (e.g. - years, months, days) to diminish that cost. > > But at any rate, this should be *way* faster than what you're running now, > and not at any heinous change in development costs (as would likely > be the case reimplementing using SPI). > -- > When confronted by a difficult problem, solve it by reducing it to the > question, "How would the Lone Ranger handle this?"
I will change and implement it this way, I was not aware of such optimization. Will post back after my benchmark runs. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
