I am trying to optimize queries from my fastify(node) backend, using the auto_explain module provided by PostgreSQL 10. However, no query information is being generated for queries coming in through my service, despite seeing them come through in the logs.
I enabled the module by running the following psql commands in my container:
LOAD 'auto_explain';
SET auto_explain.log_min_duration=0;
SET auto_explain.log_analyze=true;
SET auto_explain.log_verbose = true;
SET auto_explain.log_nested_statements = true;
I've also set log_statement = 'all' in postgresql.conf.
This seems to work because if I run a simple select through psql, I see something similar to:
postgresql-1 | 2024-04-26 22:21:54.630 UTC [384] LOG: statement: select * from user;
postgresql-1 | 2024-04-26 22:21:54.631 UTC [384] LOG: duration: 0.200 ms plan:
postgresql-1 | Query Text: select * from user;
postgresql-1 | Seq Scan on user (cost=0.00..1.03 rows=3 width=1367) (actual time=0.016..0.022 rows=3 loops=1)
postgresql-1 | Output: (a bunch of columns...)
However, I get no plan information when I trigger a query from my backend service (using node-postgres and @types/pg). I see a LOG: execute <unnamed>: entry that shows my parameterized query and a DETAILS entry that shows the parameters with their values for the query.
Would appreciate any information on enabling this module properly for my queries.
What you show is only setting it up for one session.
To activate it for everyone, you should configure it in your postgresql.conf file, like you did for log_statement. To do that, you put put the extension name in shared_preload_libraries (rather than using the LOAD command) while setting the other settings in the usual way for postgresql.conf.