Michael Nacos <m.nacos@gmail.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