Re: trigger SPI_exec count argument

Michael Nacos <m(dot)nacos(at)gmail(dot)com> writes:
> I have been trying to improve the performance of a C trigger only to notice
> that the real bottleneck was the SPI execution of dynamic SQL statements. I
> had been using SPI_exec(sql,0) until I tried SPI_exec(sql,1), since I am
> targeting exactly one row each time for writing. This simple change has made
> a huge difference (2s --> 60ms in some cases), now the trigger execution
> time is consistent with the response times I get if I run the same SQL
> statements by hand in psql. I am happy my code performs better, but is this
> something you would expect?

With no details that's an unanswerable question.

SPI_exec doesn't appear to consider the count while forming the query
plan, which was my first thought. But I have seen queries in which
the first row is returned quickly but searching for additional rows
takes a long time, even if there aren't any additional rows. It's
not clear though why that wouldn't apply to hand execution. Have
you tried comparing EXPLAIN ANALYZE outputs?

regards, tom lane