On Fri, Dec 21, 2012 at 11:25 AM, Charles Gomes <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?"