How to detect performance issues in function working on temporary tables? I have a function which creates many temporary tables and performs inserts, updates, deletes and selects on them.
Some columns are textual and I have a lot of SIMILAR TO operations going on
The function is taking too long to execute and I don't know how to detect the problem, because I can't do an explain analyze on temporary tables to detect expensive operations.
What could possibly be done? Any suggestion would be appreciated.
You can use the auto_explain extension to explain slow queries automatically from their native environment.  It will have no trouble seeing the temp tables.
You could manually load the extension if you are a superuser.  But this module is useful enough I just always put it (as well as pg_stat_statements) into shared_preload_libraries in postgresql.conf, that way it is always available.  The syntax for activating it in a specific client connection would be then be set auto_explain.log_min_duration = '150ms'; and set auto_explain.log_analyze=on;.  The output goes to the database server's log file.  You can also have it sent to the client, by doing set client_min_messages TO log;, but I don't know what C# will do with such messages once it receives them through that channel.