6

I have an un-optimized query which runs for a range of different execution times at different times of the day ranging from 1 minute to 14 hours. The CPU utilization, memory and other concurrent load on the database remaining the same, what could result in such variation? Note that auto vacuum process runs during midnight and performance improves considerably in the morning. My assertion is that due to table fragmentation, dead tuples and a lot of reads, the statistics on the same table change which generates a different execution plan. To prove this assertion, I'd like to get the query plan for the current query being run. Note that it is not possible for me to simply EXPLAIN the query before execution.

Mukul Gupta
  • 2,310
  • 3
  • 24
  • 39
  • 2
    https://www.postgresql.org/docs/current/static/auto-explain.html –  Aug 24 '17 at 13:14
  • AFAIU, this will require database restart which is also not an option. Moreover, this has an overhead of generating query plans for all long running queries and not just the concerned query. – Mukul Gupta Aug 24 '17 at 13:21
  • 1
    1: This does not require a restart, at most a reload. 2: PostgreSQL has to generate query plans for every query (unless it's prepared then it only makes the plan once) anyway. 3: The only overhead here is logging the query plan which is minimal. Just set the auto_explain.log_min_duration to simethung like 10000 so it has to be a 10 second query etc. – Scott Marlowe Aug 24 '17 at 14:58
  • 1
    Note you can load the extension, set auto_explain.log_min_duration until the bad query goes by, then set it back to -1 to turn it off. – Scott Marlowe Aug 24 '17 at 15:00
  • @ScottMarlowe Indeed, you are right about not need a restart. But how would one apply that for all sessions. Would that not need a restart? – Mukul Gupta Aug 25 '17 at 10:55

1 Answers1

3

Wait I got it working. In postgresql.conf there are TWO settings for preloading libraries. The first one, shared_preload_libraries, won't work without a restart. BUT the other one session_preload_libraries WILL. So, edit the postgresql.conf to have this line in it:

session_preload_libraries = 'auto_explain'

Then reload:

pg_ctl reload (or pg_ctlcluster 9.x main reload etc)

Then alter the database to turn it on:

alter database smarlowe set auto_explain.log_min_duration=1;

And then all new connections get auto_explained plans.

Change duration to the millisecond setting that makes most sense for you.

Scott Marlowe
  • 8,490
  • 3
  • 23
  • 21