public inbox for [email protected]
help / color / mirror / Atom feedC trigger significantly slower than PL/pgSQL?
9+ messages / 4 participants
[nested] [flat]
* C trigger significantly slower than PL/pgSQL?
@ 2023-04-12 07:35 pgchem pgchem <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: pgchem pgchem @ 2023-04-12 07:35 UTC (permalink / raw)
To: pgsql-interfaces
Hello all,
I have a functionally identical trigger function in PL/pgSQL and C. Now, that the C version is about 3x _slower_ (~1500 vs. ~4500 TPS in pgbench) than PL/pgSQL comes somewhat unexpected.
It can very well be, that I made a mistake on the C side, but before everything else, I'd like to ask if this may be expected behavior. Is it plausible that PL/pgSQL is so much faster than C when used in a trigger function?
best regards
Ernst-Georg
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: C trigger significantly slower than PL/pgSQL?
@ 2023-04-12 11:05 Dave Cramer <[email protected]>
parent: pgchem pgchem <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Dave Cramer @ 2023-04-12 11:05 UTC (permalink / raw)
To: pgchem pgchem <[email protected]>; +Cc: pgsql-interfaces
On Wed, 12 Apr 2023 at 06:14, pgchem pgchem <[email protected]> wrote:
> Hello all,
>
> I have a functionally identical trigger function in PL/pgSQL and C. Now,
> that the C version is about 3x _slower_ (~1500 vs. ~4500 TPS in pgbench)
> than PL/pgSQL comes somewhat unexpected.
>
> It can very well be, that I made a mistake on the C side, but before
> everything else, I'd like to ask if this may be expected behavior. Is it
> plausible that PL/pgSQL is so much faster than C when used in a trigger
> function?
>
It would be infinitely easier to answer this question if you posted both
functions and the plans
Dave Cramer
www.postgres.rocks
> best regards
>
> Ernst-Georg
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: C trigger significantly slower than PL/pgSQL?
@ 2023-04-12 11:33 pgchem pgchem <[email protected]>
parent: Dave Cramer <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: pgchem pgchem @ 2023-04-12 11:33 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: pgsql-interfaces
Hello Dave,
> It would be infinitely easier to answer this question if you posted both functions and the plans
before posting lengthy code to the list, I just wanted to verify beforehand that this is not a well known issue.
best regards
Ernst-Georg
>
> >
> >
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: C trigger significantly slower than PL/pgSQL?
@ 2023-04-12 12:03 Dave Cramer <[email protected]>
parent: pgchem pgchem <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Dave Cramer @ 2023-04-12 12:03 UTC (permalink / raw)
To: pgchem pgchem <[email protected]>; +Cc: pgsql-interfaces
On Wed, 12 Apr 2023 at 07:33, pgchem pgchem <[email protected]> wrote:
> Hello Dave,
>
> > It would be infinitely easier to answer this question if you posted both
> functions and the plans
>
> before posting lengthy code to the list, I just wanted to verify
> beforehand that this is not a well known issue.
>
Fair. So to answer your question. The C function should be faster.
Dave Cramer
www.postgres.rocks
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: C trigger significantly slower than PL/pgSQL?
@ 2023-04-12 12:19 Tom Lane <[email protected]>
parent: Dave Cramer <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2023-04-12 12:19 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: pgchem pgchem <[email protected]>; pgsql-interfaces
Dave Cramer <[email protected]> writes:
> Fair. So to answer your question. The C function should be faster.
If we exclude basic coding errors (i.e. not really "equivalent"
processing) then a possible theory is that plpgsql is being careful
to cache a query plan that your C code is causing to be recomputed
each time. But yeah, all else being equal plpgsql should be slower.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: C trigger significantly slower than PL/pgSQL?
@ 2023-04-13 09:31 pgchem pgchem <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: pgchem pgchem @ 2023-04-13 09:31 UTC (permalink / raw)
To: pgsql-interfaces
Hello all,
as requested, here is the C code:
#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"
#include "commands/trigger.h"
#include "utils/rel.h"
#include "utils/fmgrprotos.h"
PG_MODULE_MAGIC;
#ifdef _WIN32
extern PGDLLEXPORT Datum tf_break_cycle(PG_FUNCTION_ARGS);
#else
Datum tf_break_cycle_c(PG_FUNCTION_ARGS);
#endif
PG_FUNCTION_INFO_V1(tf_break_cycle);
Datum
tf_break_cycle(PG_FUNCTION_ARGS)
{
TriggerData* trigdata = (TriggerData*)fcinfo->context;
TupleDesc tup_desc;
HeapTuple rettuple = NULL;
bool is_replicated, is_local, isnull_is_replicated, isnull_is_local, null_flag = false;
int ret, col_num;
Datum change_val;
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "tf_break_cycle: not called by trigger manager");
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) {
rettuple = trigdata->tg_newtuple;
}
else {
rettuple = trigdata->tg_trigtuple;
}
if (TRIGGER_FIRED_AFTER(trigdata->tg_event))
elog(ERROR, "tf_break_cycle must not fire AFTER");
tup_desc = trigdata->tg_relation->rd_att;
if ((ret = SPI_connect()) < 0)
elog(ERROR, "tf_break_cycle: SPI_connect returned %d", ret);
ret = SPI_execute("SELECT pg_backend_pid() = ANY((SELECT pid FROM pg_stat_activity WHERE backend_type = 'logical replication worker'))::boolean AS is_replicated", true, 1);
if (ret < 0)
elog(ERROR, "tf_break_cycle: SPI_execute returned %d", ret);
is_replicated = DatumGetBool(SPI_getbinval(SPI_tuptable->vals[0],
SPI_tuptable->tupdesc,
1,
&isnull_is_replicated));
col_num = SPI_fnumber(tup_desc, "is_local");
is_local = DatumGetBool(SPI_getbinval(rettuple,
tup_desc,
col_num,
&isnull_is_local));
if (is_replicated) {
if (!is_local) {
rettuple = NULL;
}
else {
change_val = BoolGetDatum(false);
rettuple = heap_modify_tuple_by_cols(rettuple, tup_desc,
1, &col_num, &change_val, &null_flag);
//rettuple = SPI_modifytuple(trigdata->tg_relation, rettuple, 1, &col_num, &change_val, &null_flag);
}
}
SPI_finish();
return PointerGetDatum(rettuple);
}
If I use SPI_modifytuple() or heap_modify_tuple_by_cols() makes no difference.
And the PL/pgSQL version:
CREATE OR REPLACE FUNCTION traktor.tf_break_cycle()
RETURNS trigger
LANGUAGE plpgsql
STRICT
AS $function$
declare
begin
if pg_backend_pid() = ANY((select pid from pg_stat_activity where backend_type = 'logical replication worker')) then
if not NEW.is_local then
return null;
else
NEW.is_local = false;
end if;
end if;
return NEW;
end;
$function$;
PostgreSQL 15.2 on Windows 11 64 Bit, Compiler is Visual Studio Community 2022 17.5.3 64 Bit.
best regards,
Ernst-Georg
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: C trigger significantly slower than PL/pgSQL?
@ 2023-04-13 15:42 Tom Lane <[email protected]>
parent: pgchem pgchem <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2023-04-13 15:42 UTC (permalink / raw)
To: pgchem pgchem <[email protected]>; +Cc: pgsql-interfaces
pgchem pgchem <[email protected]> writes:
> as requested, here is the C code:
> ...
> ret = SPI_execute("SELECT pg_backend_pid() = ANY((SELECT pid FROM pg_stat_activity WHERE backend_type = 'logical replication worker'))::boolean AS is_replicated", true, 1);
Yeah, there's your problem. Each time through the trigger, that query
is being parsed, planned, and executed from scratch. plpgsql knows
how to cache the planned statement, so it's doing less work per-call.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: C trigger significantly slower than PL/pgSQL?
@ 2023-04-14 17:39 Ernst-Georg Schmid <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Ernst-Georg Schmid @ 2023-04-14 17:39 UTC (permalink / raw)
To: pgsql-interfaces
> Yeah, there's your problem. Each time through the trigger, that query
> is being parsed, planned, and executed from scratch. plpgsql knows
> how to cache the planned statement, so it's doing less work per-call.
Hello again,
thank you for your help.
I have now used SPI_prepare() and SPI_keepplan() to cache the plan in a
static variable - and now it performs exactly like the PL/pgSQL version.
Which also probably means that either the Trigger is so trivial that C
makes no difference, or the observed TPS limit comes from somewhere else.
I have still a follow-up question, though. Since I'm not calling
SPI_freeplan(), the cached plan lives forever, right? Which is no
problem, since the trigger does the same statement over and over. But
does this generate a memory leak? Or is the saved plan tied to the
session and is deallocated when the session ends?
best regards
Ernst-Georg
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: C trigger significantly slower than PL/pgSQL?
@ 2023-04-15 17:31 Tom Lane <[email protected]>
parent: Ernst-Georg Schmid <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2023-04-15 17:31 UTC (permalink / raw)
To: Ernst-Georg Schmid <[email protected]>; +Cc: pgsql-interfaces
Ernst-Georg Schmid <[email protected]> writes:
> I have still a follow-up question, though. Since I'm not calling
> SPI_freeplan(), the cached plan lives forever, right?
Till the per-session server process exits. I wouldn't call it
a leak as long as you can reuse the plan in later calls in that
session; a static variable's scope is fine for that.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2023-04-15 17:31 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-12 07:35 C trigger significantly slower than PL/pgSQL? pgchem pgchem <[email protected]>
2023-04-12 11:05 ` Dave Cramer <[email protected]>
2023-04-12 11:33 ` pgchem pgchem <[email protected]>
2023-04-12 12:03 ` Dave Cramer <[email protected]>
2023-04-12 12:19 ` Tom Lane <[email protected]>
2023-04-13 09:31 Re: C trigger significantly slower than PL/pgSQL? pgchem pgchem <[email protected]>
2023-04-13 15:42 ` Tom Lane <[email protected]>
2023-04-14 17:39 ` Ernst-Georg Schmid <[email protected]>
2023-04-15 17:31 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox