In Postgres pg_stat_statements huge difference between max and mean time

Question

In Postgres 14 in pg_stat_statements, why is there a huge time difference between max_exec_time and mean_exec_time?

Could you please help?

Answer 1

That means that the execution time varies considerably. To see if that is just a single outlier or a regular occurrence, see if stddev_exec_time is high or not.

It can mean several things:

• Perhaps execution sometimes took long because the statement was stuck behind a lock. If that is a rare occurrence, it may me the odd ALTER TABLE statement.

• Perhaps the execution time varies depending on a query parameter. It may be fast for rare values and slow for frequent ones. Test with different parameters!

• Perhaps execution time varies depending on how much of the data happens to be cached in RAM. Look at a high "buffers" footprint in the EXPLAIN (ANALYZE, BUFFERS) output.