Thread: Trigger performance
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.
pginfo <pginfo@t1.unisoftbg.com> writes:
> I was supprised that the pgsql trigger take ~8 sec. to insert this rows
> and the "C" trigger take ~ 17 sec.
The reason is that plpgsql caches the plan for the invoked SELECT,
whereas the way you coded the C function, it's re-planning that SELECT
on every call.
regards, tom lane
Hi, thanks for the answer. It is very interest, because I readet many times that if I write the trigger in "C" it will work faster. In wich case will this trigger work faster if write it in "C"? In all my triggres I have "select ...." or "insert into mytable select ..." or "update mytable set ...where...". I need this info because I have a table with ~1.5 M rows and if I start to update 300 K from this rows it takes ~ 2h. If I remove the trigger for this table all the time is ~ 1 min. regards, ivan. Tom Lane wrote: > pginfo <pginfo@t1.unisoftbg.com> writes: > > I was supprised that the pgsql trigger take ~8 sec. to insert this rows > > and the "C" trigger take ~ 17 sec. > > The reason is that plpgsql caches the plan for the invoked SELECT, > whereas the way you coded the C function, it's re-planning that SELECT > on every call. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
pginfo <pginfo@t1.unisoftbg.com> writes:
> In wich case will this trigger work faster if write it in "C"?
Given that the dominant part of the time will be spent down inside SPI
in either case, I doubt you will be able to see much difference. You
need to think about how to optimize the invoked query, not waste your
time recoding the wrapper around it.
regards, tom lane
Ok, thanks. I will do it. regards, ivan. Tom Lane wrote: > pginfo <pginfo@t1.unisoftbg.com> writes: > > In wich case will this trigger work faster if write it in "C"? > > Given that the dominant part of the time will be spent down inside SPI > in either case, I doubt you will be able to see much difference. You > need to think about how to optimize the invoked query, not waste your > time recoding the wrapper around it. > > regards, tom lane
Hello try prepared statements, PQexecPrepared http://developer.postgresql.org/docs/postgres/libpq-exec.html Regards Pavel Stehule On Thu, 22 Jan 2004, pginfo wrote: > Hi, > > thanks for the answer. > It is very interest, because I readet many times that if I write the trigger > in "C" it will work faster. > In wich case will this trigger work faster if write it in "C"? > In all my triggres I have "select ...." or "insert into mytable select ..." > or "update mytable set ...where...". > I need this info because I have a table with ~1.5 M rows and if I start to > update 300 K from this rows it takes ~ 2h. > If I remove the trigger for this table all the time is ~ 1 min. > > regards, > ivan. > > Tom Lane wrote: > > > pginfo <pginfo@t1.unisoftbg.com> writes: > > > I was supprised that the pgsql trigger take ~8 sec. to insert this rows > > > and the "C" trigger take ~ 17 sec. > > > > The reason is that plpgsql caches the plan for the invoked SELECT, > > whereas the way you coded the C function, it's re-planning that SELECT > > on every call. > > > > regards, tom lane > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >