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.

auto_explain.sample_rate controls how many of the qualifying queries (those exceeding log_min_duration) will be analyzed and logged.
This parameter directly affects how reliably Query Advisor AI captures execution plans.

---

📊 Understanding auto_explain.sample_rate

sample_rate accepts a value between 0 and 1:

• 1 → 100% of qualifying queries are analyzed
• 0.1 → 10% of qualifying queries are analyzed
• 0.01 → 1% of qualifying queries are analyzed

---

🟢 Deep Analysis / Testing (Setup Validation)

ALTER SYSTEM SET auto_explain.sample_rate TO '1';

Use this when:

• You want to guarantee that a specific slow query is captured
• You are validating Query Advisor AI setup
• You are running controlled performance analysis

This ensures that every qualifying query is logged and available for AI-based analysis.

⚠️ Not recommended for high-traffic production systems for extended periods.

---

🟡 Moderate Production Traffic

ALTER SYSTEM SET auto_explain.sample_rate TO '0.1';

Use this when:

• Traffic is moderate
• You want balanced visibility
• You need meaningful insights without excessive log growth

---

🔴 Heavy Production Systems (High QPS)

ALTER SYSTEM SET auto_explain.sample_rate TO '0.01';

Recommended for:

• High-throughput systems
• Latency-sensitive environments
• Large-scale production workloads

In heavy production systems, this value must be kept low to prevent:

• Excessive log volume
• Increased I/O pressure
• Additional overhead on busy database servers

---

🎯 Best Practice Recommendation

• During onboarding or troubleshooting → use sample_rate = 1
• For normal production monitoring → reduce to 0.01 – 0.05
• Always adjust based on workload intensity

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!