Re: Writing Trigger Functions in C

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