Re: Writing Trigger Functions in C

________________________________
> Date: Fri, 21 Dec 2012 11:56:25 -0500
> Subject: Re: [HACKERS] Writing Trigger Functions in C
> From: cbbrowne@gmail.com
> To: charlesrg@outlook.com
> CC: pgsql-hackers@postgresql.org
>
> On Fri, Dec 21, 2012 at 11:25 AM, Charles Gomes
> <charlesrg@outlook.com<mailto:charlesrg@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.