X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E15CED1D492 for ; Thu, 22 Jan 2004 10:47:57 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 25166-08 for ; Thu, 22 Jan 2004 06:47:28 -0400 (AST) Received: from mail.unisoftbg.com (mail.unisoftbg.com [194.12.229.207]) by svr1.postgresql.org (Postfix) with ESMTP id 50F2BD1D285 for ; Thu, 22 Jan 2004 06:47:18 -0400 (AST) Received: (qmail 26217 invoked by uid 507); 22 Jan 2004 11:51:51 -0000 Received: from unknown (HELO t1.unisoftbg.com) (pginfo%t1.unisoftbg.com@194.12.229.193) by 0 with SMTP; 22 Jan 2004 11:51:51 -0000 Message-ID: <400F9955.3C0FD784@t1.unisoftbg.com> Date: Thu, 22 Jan 2004 10:35:17 +0100 From: pginfo X-Mailer: Mozilla 4.03 [en] (Win95; I) MIME-Version: 1.0 To: "pgsql-performance@postgresql.org" Subject: Trigger performance Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/215 X-Sequence-Number: 5389 Hi, I need to speed up the triggers that we are using and I began to make some tests to compare the "C" and pgSQL trigger performance. I try to write two identical test triggers (sorry I do not know very good the pgsql C interface and I got one of examples and werite it) and attached it on insert of my test table. After it I try to insert in thi stable ~ 160 K rows and compared the speeds. I was supprised that the pgsql trigger take ~8 sec. to insert this rows and the "C" trigger take ~ 17 sec. This are my triggers: CREATE OR REPLACE FUNCTION trig1_t() RETURNS trigger AS ' DECLARE my_rec RECORD; BEGIN select into my_rec count(*) from ttest; RETURN NEW; END; ' LANGUAGE 'plpgsql' VOLATILE; and this writen in "C": #include "postgres.h" #include "executor/spi.h" /* this is what you need to work with SPI */ #include "commands/trigger.h" /* ... and triggers */ extern Datum trigf(PG_FUNCTION_ARGS); PG_FUNCTION_INFO_V1(trigf); Datum trigf(PG_FUNCTION_ARGS) { TriggerData *trigdata = (TriggerData *) fcinfo->context; TupleDesc tupdesc; HeapTuple rettuple; char *when; bool checknull = false; bool isnull; int ret, i; /* make sure it's called as a trigger at all */ if (!CALLED_AS_TRIGGER(fcinfo)) elog(ERROR, "trigf: not called by trigger manager"); /* tuple to return to executor */ if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) rettuple = trigdata->tg_newtuple; else rettuple = trigdata->tg_trigtuple; /* check for null values */ if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event) && TRIGGER_FIRED_BEFORE(trigdata->tg_event)) checknull = true; if (TRIGGER_FIRED_BEFORE(trigdata->tg_event)) when = "before"; else when = "after "; tupdesc = trigdata->tg_relation->rd_att; /* connect to SPI manager */ if ((ret = SPI_connect()) < 0) elog(INFO, "trigf (fired %s): SPI_connect returned %d", when, ret); /* get number of rows in table */ ret = SPI_exec("SELECT count(*) FROM ttest", 0); if (ret < 0) elog(NOTICE, "trigf (fired %s): SPI_exec returned %d", when, ret); SPI_finish(); if (checknull) { SPI_getbinval(rettuple, tupdesc, 1, &isnull); if (isnull) rettuple = NULL; } return PointerGetDatum(rettuple); } My question: Can I do the "C" trigger to be faster that the pgSQL? regards, ivan.