Message-ID: <8306edcc-8458-44b3-485b-0725de3c7743@tuschehund.de>
Date: Fri, 14 Apr 2023 19:39:20 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101
 Thunderbird/102.10.0
Subject: Re: C trigger significantly slower than PL/pgSQL?
To: "pgsql-interfaces@postgresql.org" <pgsql-interfaces@postgresql.org>
References: <1595558642.2519076.1681378287741@webmail.strato.de>
 <1342580.1681400531@sss.pgh.pa.us>
From: Ernst-Georg Schmid <pgchem@tuschehund.de>
In-Reply-To: <1342580.1681400531@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/8306edcc-8458-44b3-485b-0725de3c7743%40tuschehund.de>
Precedence: bulk

 > 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