pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger performance
6+ messages / 3 participants
[nested] [flat]
* Trigger performance
@ 2004-01-22 09:35 pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 6+ messages in thread
From: pginfo @ 2004-01-22 09:35 UTC (permalink / raw)
To: pgsql-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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Trigger performance
@ 2004-01-22 15:31 pginfo <pginfo@t1.unisoftbg.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: pginfo @ 2004-01-22 15:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Trigger performance
@ 2004-01-22 15:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2004-01-22 15:37 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Trigger performance
@ 2004-01-22 16:05 pginfo <pginfo@t1.unisoftbg.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: pginfo @ 2004-01-22 16:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Trigger performance
@ 2004-01-22 17:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: pginfo <pginfo@t1.unisoftbg.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2004-01-22 17:10 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Trigger performance
@ 2004-01-23 07:35 Pavel Stehule <stehule@kix.fsv.cvut.cz>
parent: pginfo <pginfo@t1.unisoftbg.com>
1 sibling, 0 replies; 6+ messages in thread
From: Pavel Stehule @ 2004-01-23 07:35 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-01-23 07:35 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-22 09:35 Trigger performance pginfo <pginfo@t1.unisoftbg.com>
2004-01-22 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-22 15:31 ` pginfo <pginfo@t1.unisoftbg.com>
2004-01-22 17:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-22 16:05 ` pginfo <pginfo@t1.unisoftbg.com>
2004-01-23 07:35 ` Pavel Stehule <stehule@kix.fsv.cvut.cz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox