Обсуждение: C trigger significantly slower than PL/pgSQL?
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
On Wed, 12 Apr 2023 at 06:14, pgchem pgchem <pgchem@tuschehund.de> 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 regardsErnst-Georg
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
On Wed, 12 Apr 2023 at 07:33, pgchem pgchem <pgchem@tuschehund.de> wrote:
Hello Dave,> It would be infinitely easier to answer this question if you posted both functions and the plansbefore 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
Dave Cramer <davecramer@postgres.rocks> 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
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
pgchem pgchem <pgchem@tuschehund.de> 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
replicationworker'))::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
> 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
Ernst-Georg Schmid <pgchem@tuschehund.de> 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