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(at)gmail(dot)com
> To: charlesrg(at)outlook(dot)com
> CC: pgsql-hackers(at)postgresql(dot)org
>
> On Fri, Dec 21, 2012 at 11:25 AM, Charles Gomes
> <charlesrg(at)outlook(dot)com<mailto:charlesrg(at)outlook(dot)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.