---------------------------------------- > From: charle...@outlook.com > To: cbbro...@gmail.com > CC: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Writing Trigger Functions in C > Date: Fri, 21 Dec 2012 12:27:26 -0500 > > ________________________________ > > 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
I've to have add 2 weeks of data at a time, therefore I had to keep two weeks of case statements Replaced the short trigger function to: CREATE OR REPLACE FUNCTION quotes_insert_trigger() RETURNS trigger AS $$ DECLARE r_date text; BEGIN r_date = to_char(new.received_time, 'YYYY_MM_DD'); case r_date when '2012_09_10' then insert into quotes_2012_09_10 values (NEW.*) using new; return; when '2012_09_11' then insert into quotes_2012_09_11 values (NEW.*) using new; return; when '2012_09_12' then insert into quotes_2012_09_12 values (NEW.*) using new; return; when '2012_09_13' then insert into quotes_2012_09_13 values (NEW.*) using new; return; when '2012_09_14' then insert into quotes_2012_09_14 values (NEW.*) using new; return; when '2012_09_15' then insert into quotes_2012_09_15 values (NEW.*) using new; return; when '2012_09_16' then insert into quotes_2012_09_16 values (NEW.*) using new; return; when '2012_09_17' then insert into quotes_2012_09_17 values (NEW.*) using new; return; when '2012_09_18' then insert into quotes_2012_09_18 values (NEW.*) using new; return; when '2012_09_19' then insert into quotes_2012_09_19 values (NEW.*) using new; return; when '2012_09_20' then insert into quotes_2012_09_20 values (NEW.*) using new; return; when '2012_09_21' then insert into quotes_2012_09_21 values (NEW.*) using new; return; when '2012_09_22' then insert into quotes_2012_09_22 values (NEW.*) using new; return; when '2012_09_23' then insert into quotes_2012_09_23 values (NEW.*) using new; return; when '2012_09_24' then insert into quotes_2012_09_24 values (NEW.*) using new; return; end case RETURN NULL; END; $$ LANGUAGE plpgsql; And I had no performance improvements at all. Took the same time as with the previous EXECUTE statement; I don't see what am I doing wrong. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
