| Lists: | pgsql-interfaces |
|---|
| From: | pgchem pgchem <pgchem(at)tuschehund(dot)de> |
|---|---|
| To: | "pgsql-interfaces(at)postgresql(dot)org" <pgsql-interfaces(at)postgresql(dot)org> |
| Subject: | C trigger significantly slower than PL/pgSQL? |
| Date: | 2023-04-12 07:35:10 |
| Message-ID: | 607407366.2321178.1681284910101@webmail.strato.de |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | 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
| From: | Dave Cramer <davecramer(at)postgres(dot)rocks> |
|---|---|
| To: | pgchem pgchem <pgchem(at)tuschehund(dot)de> |
| Cc: | "pgsql-interfaces(at)postgresql(dot)org" <pgsql-interfaces(at)postgresql(dot)org> |
| Subject: | Re: C trigger significantly slower than PL/pgSQL? |
| Date: | 2023-04-12 11:05:14 |
| Message-ID: | CADK3HHJ0HtYy=6ErV69v9Emj2VsEkpJwvvcE-Rx9n1sDAAGrfw@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-interfaces |
On Wed, 12 Apr 2023 at 06:14, pgchem pgchem <pgchem(at)tuschehund(dot)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 regards
>
> Ernst-Georg
>
>
| From: | pgchem pgchem <pgchem(at)tuschehund(dot)de> |
|---|---|
| To: | Dave Cramer <davecramer(at)postgres(dot)rocks> |
| Cc: | "pgsql-interfaces(at)postgresql(dot)org" <pgsql-interfaces(at)postgresql(dot)org> |
| Subject: | Re: C trigger significantly slower than PL/pgSQL? |
| Date: | 2023-04-12 11:33:42 |
| Message-ID: | 538907970.2383682.1681299222931@webmail.strato.de |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | Postg범퍼카 토토SQL : |
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 <davecramer(at)postgres(dot)rocks> |
|---|---|
| To: | pgchem pgchem <pgchem(at)tuschehund(dot)de> |
| Cc: | "pgsql-interfaces(at)postgresql(dot)org" <pgsql-interfaces(at)postgresql(dot)org> |
| Subject: | Re: C trigger significantly slower than PL/pgSQL? |
| Date: | 2023-04-12 12:03:41 |
| Message-ID: | CADK3HH+_Vi4uoRaQmDSNPoZfAvm8A_NLQ87=EaXv1DxxXYrmsQ@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-interfaces |
On Wed, 12 Apr 2023 at 07:33, pgchem pgchem <pgchem(at)tuschehund(dot)de> 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
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Dave Cramer <davecramer(at)postgres(dot)rocks> |
| Cc: | pgchem pgchem <pgchem(at)tuschehund(dot)de>, "pgsql-interfaces(at)postgresql(dot)org" <pgsql-interfaces(at)postgresql(dot)org> |
| Subject: | Re: C trigger significantly slower than PL/pgSQL? |
| Date: | 2023-04-12 12:19:15 |
| Message-ID: | 826406.1681301955@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-interfaces |
Dave Cramer <davecramer(at)postgres(dot)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