Enabling Query Advisor AI

The Query Advisor AI feature helps you automatically analyze slow-running queries and receive optimization insights.
It explains why a query might be slow, whether it can be rewritten for better performance, or if an index could improve execution time.

Once activated, Query Advisor AI examines each slow query and provides actionable recommendations — giving you a complete understanding of what’s happening behind the scenes.

This feature is optional, but to enable it, you must first configure PostgreSQL to load the auto_explain extension.

---

⚙️ Step 1: Add auto_explain to shared_preload_libraries

The auto_explain module must be loaded at server startup.
Depending on your PostgreSQL setup, there are two ways to configure it:

• For a standard PostgreSQL installation

Edit your postgresql.conf file and modify or add the following line:

shared_preload_libraries = 'auto_explain'

Save the file, then restart PostgreSQL for the change to take effect.

---

• For Patroni-managed clusters

Run the following command to edit the Patroni cluster configuration:

patronictl edit-config

Then, under the postgresql.parameters section, add or update:

postgresql:
  parameters:
    shared_preload_libraries: auto_explain

Save and exit the editor.

⚙️ Step 2: Configure Query Advisor AI Parameters

Once auto_explain is loaded, configure it to log detailed information for Query Advisor AI.

ALTER SYSTEM SET auto_explain.log_format TO 'json';
ALTER SYSTEM SET auto_explain.log_min_duration TO '1000';          -- Log queries slower than 1 second
ALTER SYSTEM SET auto_explain.log_buffers TO 'on';                 -- Include buffer usage
ALTER SYSTEM SET auto_explain.log_timing TO 'off';                 -- Reduce overhead
ALTER SYSTEM SET auto_explain.log_triggers TO 'on';                -- Log trigger execution
ALTER SYSTEM SET auto_explain.log_verbose TO 'on';                 -- Include verbose plan details
ALTER SYSTEM SET auto_explain.log_nested_statements TO 'on';       -- Include nested statements
ALTER SYSTEM SET auto_explain.sample_rate TO '1';                  -- Analyze 100% of qualifying queries

These parameters define how Query Advisor AI collects and interprets query execution data. Since these are system-level parameters, a PostgreSQL restart is required after running them.

🔄 Step 3: Restart PostgreSQL

You need to restart PostgreSQL (not just reload) to activate the auto_explain module.

# For standalone PostgreSQL

sudo systemctl restart postgresql

# For Patroni-managed clusters

patronictl restart '<cluster_name>'

🧩 Step 4: Verify Configuration

After restart, confirm that auto_explain has been successfully added to shared_preload_libraries:

SELECT setting, pending_restart
FROM pg_settings
WHERE name = 'shared_preload_libraries';

After the restart, verify that all auto_explain parameters have been correctly applied.

SELECT name, setting
FROM pg_settings
WHERE name IN (
  'auto_explain.log_format',
  'auto_explain.log_min_duration',
  'auto_explain.log_buffers',
  'auto_explain.log_timing',
  'auto_explain.log_triggers',
  'auto_explain.log_verbose',
  'auto_explain.log_nested_statements',
  'auto_explain.sample_rate'
)
ORDER BY name;

✅ Step 5: You're Ready!

After completing these steps, Query Advisor AI will automatically start analyzing slow queries and provide insights such as:

Execution plan inefficiencies

Missing index suggestions

Query rewriting opportunities

Buffer and trigger behavior insights

You can now leverage Query Advisor AI to continuously improve your database performance with intelligent, AI-powered recommendations.

---

📚 Further Reading

For more details, please refer to the official PostgreSQL documentation:
https://www.postgresql.org/docs/current/auto-explain.html

---

We're almost ready. Now we can add our database.

So let's jump on to the next step!