Run “EXPLAIN (ANALYZE, BUFFERS)” on a PostgreSQL query with a long run time in Puppet Enterprise

A PostgreSQL query with a long run time (over five minutes) causes performance issues such as high CPU utilization in Puppet Enterprise.

Version and installation information

PE version: All supported

Solution

To find the cause of a long run time in a query, such as how it was executed, how much time was spent on each sub-query, and how much memory or disk input/output was used, run the query with EXPLAIN (ANALYZE, BUFFERS). Your query should look like this:

cat <<'EOF' | runuser -u pe-postgres -- /opt/puppetlabs/server/bin/psql -d pe-puppetdb -f - > /tmp/pdb_query_explain.txt 
EXPLAIN (ANALYZE, BUFFERS) {{YOUR QUERY HERE}}; 
EOF 

Run the command from the primary server replacing {{YOUR QUERY HERE}}with the query you want to run. Make sure to remove the brackets from your query.

To convert the output to a table, use https://explain.depesz.com/.