trigger-overhead

출처 : http://www.openscg.com/2014/05/trigger-overhead-part-2/

I found a bit more time dig into timing of triggers and their overhead so I wanted to see how much overhead the choice of procedural language affected performance. I followed the same testing methodology from my original trigger test. For this test I created an empty trigger in the following languages:

PL/pgSQL

CREATE FUNCTION empty_trigger() RETURNS trigger AS $$
BEGIN
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

C

#include "postgres.h"
#include "commands/trigger.h"
PG_MODULE_MAGIC;
Datum empty_c_trigger(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(empty_c_trigger);
Datum
empty_c_trigger(PG_FUNCTION_ARGS)
{
 TriggerData *tg;
 HeapTuple ret;
tg = (TriggerData *) (fcinfo->context);
 if (TRIGGER_FIRED_BY_UPDATE(tg->tg_event))
 ret = tg->tg_newtuple;
 else
 ret = tg->tg_trigtuple;
return PointerGetDatum(ret);
}

PL/Pythonu

CREATE FUNCTION empty_python_trigger() RETURNS trigger AS $$
return
$$ LANGUAGE plpythonu;

PL/Perl

CREATE FUNCTION empty_perl_trigger() RETURNS trigger AS $$
 return; 
$$ LANGUAGE plperl;

PL/TCL

CREATE FUNCTION empty_tcl_trigger() RETURNS trigger AS $$
 return [array get NEW]
$$ LANGUAGE pltcl;

PL/Java

package org.postgresql.pljava;
import java.sql.SQLException;
import java.sql.ResultSet;
import org.postgresql.pljava.TriggerData;
import org.postgresql.pljava.TriggerException;
public class TriggerTest {
 static void test(TriggerData td) throws SQLException {
 ResultSet _new = td.getNew();
 }
}

PL/v8

CREATE FUNCTION empty_v8_trigger() RETURNS trigger AS $$
 return NEW;
$$
LANGUAGE plv8;

PL/R

CREATE FUNCTION empty_r_trigger() RETURNS trigger AS $$
 return(pg.tg.new)
$$ LANGUAGE plr;

All of the triggers essentially return NEW so we’re basically measuring the overhead starting up the trigger function. I then timed inserting 100,000 rows with the triggers in place and compared them to inserting into a table without a trigger. Some of the timings that I found were obvious such as C being the fastest, but others were pretty surprising.

Some of the bigger things that I noticed that out of the 3 built-in higher level languages, Python has much less overhead than Perl and TCL.

The other notable point was how little overhead PL/Java had compared to the other languages. PL/Java only had more overhead than C, PL/pgSQL and PL/Python.

The moral of the story is that when writing triggers some choices matter a lot. If you’re writing a simple trigger that just ensures a column equals the current timestamp, don’t write in PL/v8 just because it cool. Use PL/pgSQL for the simple things and save the other languages for your more complex logic where the overhead of starting them up won’t be noticed.