Getting Started with Query Advisor

Query Advisor automatically analyzes Postgres query plans to detect optimization opportunities. This guide covers the requirements and setup needed to start using Query Advisor effectively.

What you get with Query Advisor

Query Advisor provides continuous, automated query optimization insights for your Postgres databases:

• Automated analysis: Continuously examines EXPLAIN plans to identify performance anti-patterns
• Query-centric optimization: Optimizes specific queries through rewrites and planner hints, complementing Index Advisor's schema-level recommendations
• Deterministic algorithms: Uses purpose-built logic based on Postgres planner behavior
• Workbooks integration: Seamlessly integrates with pganalyze Workbooks for systematic testing and benchmarking
• Tuning based on best practices: Provides multiple parameter sets to test to ensure optimizations don't regress other input parameters

Query Advisor identifies the optimization opportunities - you maintain control over applying changes to production.

Supported optimization insights

Query Advisor detects common Postgres performance issues including inefficient nested loops and suboptimal index usage due to ORDER BY clauses. More optimization patterns are continuously being added.

See the complete list and detailed explanations in Supported Insights.

Requirements for using Query Advisor

Query Advisor requires the pganalyze collector to be installed and configured, with additional requirements noted below.

EXPLAIN Plan Collection

Query Advisor analyzes Postgres EXPLAIN plans, which can be collected through:

Automated analysis of slow queries with auto_explain (recommended):

• auto_explain extension enabled. Refer to the setup guide for platform-specific instructions.
• auto_explain.log_analyze enabled to capture row mis-estimates.
• auto_explain.log_format set to json (other formats are currently not supported).

With auto_explain configured correctly, you will get proactive Query Advisor insights into query plans with potential optimizations as they occur.

Manual collection:

Alternatively, you can create a pganalyze Workbook. Query Advisor analysis runs automatically on all EXPLAIN plans uploaded to workbooks.

Collector Workflow

For the best Query Advisor experience we also recommend enabling the collector workflow in pganalyze Workbooks for seamless testing.

Testing and validation

Query Advisor integrates with pganalyze Workbooks for systematic testing of query rewrites. The recommended workflow is:

1. Record baseline performance of a query to establish reference metrics
2. Apply insights using suggested query rewrites to create variants
3. Benchmark results to measure performance improvements
4. Validate across different parameter sets to ensure consistent optimization
5. Deploy to production with confidence based on systematic testing

How to navigate Query Advisor

Once Query Advisor is configured and collecting EXPLAIN plans, optimization insights appear in several locations throughout pganalyze:

Query Advisor Landing Page

Access the dedicated Query Advisor page to view all detected optimization opportunities for a database, organized by potential impact based on query runtime.

As pganalyze collects EXPLAIN plans through auto_explain, Query Advisor automatically analyzes them and flags problematic patterns daily. On the "Workbooks with Insights" tab you can also see insights detected in plans uploaded to pganalyze Workbooks.

Issue Detail Page

Each optimization opportunity detected in Automated EXPLAIN data by Query Advisor has a dedicated detail page that provides comprehensive analysis and actionable solutions.

The Issue Detail Page shows:

• Detailed Problem Analysis and Solution: Detailed explanation of the pathological pattern detected, and guidance for implementing the suggested optimizations.
• Query Rewrite Preview: Side-by-side comparison of original and optimized queries with highlighted changes. For some rewrites you may see instructions instead of a preview.
• EXPLAIN Plan: Visual representation of query execution plans with Query Advisor insights clearly highlighted.
• Workbooks integration: Direct link to create a workbook for validating a query rewrite, or reference to an existing workbook that was previously created.

Workbooks Integration

When creating workbooks or analyzing queries, Query Advisor automatically assesses plans and provides optimization suggestions you can apply directly in the workbook editor.

Query Detail Views

Individual query performance pages display Query Advisor insights alongside other metrics.

This integrated approach ensures that optimization opportunities are visible wherever you're working with query performance data.

Next steps

• Follow the complete optimization workflow - See Query Advisor in action from detection to resolution
• Learn about specific insights - Understand the optimization patterns Query Advisor detects
• Configure alerts - Get notified when new optimization opportunities are discovered
• Explore pganalyze Workbooks - Learn about the testing and benchmarking platform