Subject: C trigger significantly slower than PL/pgSQL?

From: pgchem pgchem <[email-masked]>
Date: Wed, 12 Apr 2023 09:35:10 +0200 (CEST)

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

---

From: Dave Cramer <[email-masked]>
Date: Wed, 12 Apr 2023 07:05:14 -0400

On Wed, 12 Apr 2023 at 06:14, pgchem pgchem <[email-masked]> 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

---

From: pgchem pgchem <[email-masked]>
Date: Wed, 12 Apr 2023 13:33:42 +0200 (CEST)

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

---

From: Dave Cramer <[email-masked]>
Date: Wed, 12 Apr 2023 08:03:41 -0400

On Wed, 12 Apr 2023 at 07:33, pgchem pgchem <[email-masked]> wrote:

> 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

---

From: Tom Lane <[email-masked]>
Date: Wed, 12 Apr 2023 08:19:15 -0400

> 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