Re: Writing Trigger Functions in C - Mailing list pgsql-hackers

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

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
     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?"