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.

  1. FrankFrank05-22-2014

    What is the configuration setting for shared_preload_libraries? I have no idea if it would make a difference, but it worth to take look at it.

    • Jim MlodgenskiJim Mlodgenski05-23-2014

      I tried setting shared_prelaod_libraries for PL/R and it made no difference. Since the test opened a single connection and then inserted 100,000 the overhead of loading the library on the first insert quickly becomes insignificant in relation to the time of the entire test.

      • RanjeetRanjeet07-30-2014

        at 200 or even less I will be happy. I have to be happy now or else the whegit is just going to come back again like it alywas does. People think because i’m a Christian I should automatically be happy and have it all together, but the truth is that I don’t and that is why I do need God in my life so that He can help heal all the past hurts and wounds I have in my life. http://gdwlemczfti.com [url=http://opzrfwxfzkf.com]opzrfwxfzkf[/url] [link=http://jxcldb.com]jxcldb[/link]

  2. viagraviagra06-27-2014

    Hello!

  3. onlineonline06-28-2014

    Hello!

  4. cialiscialis06-30-2014

    Hello!

  5. dosagedosage07-02-2014

    Hello!

  6. viagra_dosageviagra_dosage07-05-2014

    Hello!

  7. viagra_priceviagra_price07-05-2014

    Hello!

    • SonaSona07-30-2014

      I’m afraid this won’t work, as the 9.1 pakacges from experimental are built against the Debian unstable libraries. I suggest you either build the source yourself (apt-get source -b postgresql-9.1) or wait until 9.1 final is released, at which point it will go into unstable and squeeze-backports.

  8. Hello!

    • XuefXuef07-31-2014

      If the person was your fnried, they would probably be happy to hear from you. It would be a pleasant surprise to reconnect.If they don’t reply, it could be the wrong email address or an email address that they no longer use.I use it every day.

  9. cialis_pricecialis_price07-16-2014

    Hello!

  10. generic_cialisgeneric_cialis07-17-2014

    Hello!

    • KarlaKarla07-30-2014

      It took me a while to figure this out, but serions (in particular) who are afraid of using computers are my very favorite clients! I love to work with someone who doesn’t understand what computers are or how they work, what the Internet is and why it is so wonderful and scary at the same time, and how they can use both and be as safe as possible at the same time. I love showing them that the computer can be a gateway to their kids and grandkids, can help them maintain their finances, and educate them on things they never dreamed of before.The reason I prefer serions is that they are of a generation that had no exposure to personal computers through their entire public or private school careers. Many of them never used a computer at work except for work-related stuff (a technical term stuff ), and they just never thought about or were exposed to the benefits of all of this in that environment.I have an 84-year-old client who, last Christmas, got an iTouch as a gift from her grandkids. They told her all about it when she opened it up, but she had no clue what to do with it or how to get it to work. She brought it to me and I worked with her for a couple of hours, showed her iTunes and we ripped a couple of CDs. I remember pulling up to her house last March and seeing her walking through the neighborhood all wired up, listening to Beethoven’s 5th with a smile on her face.That was pretty cool! http://kbolca.com [url=http://rimykg.com]rimykg[/url] [link=http://gnsilu.com]gnsilu[/link]

  11. viagra_onlineviagra_online07-18-2014

    Hello!

    • MichaelMichael07-30-2014

      Depression is difficult you shuold really check a few more more forums for people dealing with it successfully (www.dailystrength.org has a few good ones I think.)Exercise works well against depression maybe take up an activity together like walking or hiking?Good luck. http://heqwxxggp.com [url=http://txjxqnlawle.com]txjxqnlawle[/url] [link=http://zfdwomntg.com]zfdwomntg[/link]

    • scene havingscene having07-31-2014

      Heck yeah bay-bee keep them coming!

  12. MohanMohan07-30-2014

    Managing conflict is about cntinnuiog a conversation to the end. Which is just about arriving at a new view of reality. If you believe that reality is knowable in the first place! And then a new conversation begins at that point, a better one. ne that is about how to solve the problem, instead of being part of it.Making something personal is just a conversation stopper. To manage the unstopping effectively is to talk about the possible reasons the conversation in question is becoming to uncomfortable to continue. Or why it is being stopped? Change is uncomfortable for people. To change a sacred perspective and see a different view is very scary. Some views have their own built in security systems, that throw out an emotional pain when new opinions are simply introduced. It serves us best to examine those systems to see if they truly are in our own best interest. Why are we scared? That is a great conversation in it self! http://pavheoqbfp.com [url=http://wnighhvxk.com]wnighhvxk[/url] [link=http://myxmygqe.com]myxmygqe[/link]

Leave a Reply

Please type the characters of this captcha image in the input box

Please write the answer to the math question of this captcha image in the input box

/* ]]> */