Postgresql – How to detect performance issues in function working on temporary tables

postgresqlpostgresql-performance

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.

Best Answer

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.