Skip to content

Performance Intelligence

Jump to bottom
Temp edited this page Oct 3, 2025 · 1 revision

Performance Intelligence Tools

Query optimization, workload analysis, and index tuning for maximum PostgreSQL performance.

📊 Overview

6 specialized performance tools for comprehensive query optimization:

Tool Purpose Key Feature
get_top_queries Identify slow and resource-heavy queries pg_stat_statements
explain_query Analyze query execution plans EXPLAIN ANALYZE
index_usage_stats Analyze index effectiveness Index hit ratios
suggest_indexes AI-powered index recommendations DTA algorithm
hypothetical_index_test Test indexes without creating HypoPG
workload_analysis Comprehensive workload insights Multi-metric analysis

Requirements: pg_stat_statements extension (optional: hypopg for hypothetical indexes)

🔧 Tool Details

get_top_queries

Find the slowest and most resource-intensive queries.

Parameters:

  • sort_by (string, required): total_time, mean_time, calls, rows
  • limit (integer, optional): Number of results (default: 10)

Example:

# Find slowest queries by total time
result = get_top_queries(sort_by="total_time", limit=10)

# Find most frequently called queries
result = get_top_queries(sort_by="calls", limit=20)

Use Cases:

  • Performance troubleshooting
  • Query optimization prioritization
  • Application profiling

explain_query

Get detailed execution plans with timing and cost analysis.

Parameters:

  • sql (string, required): Query to explain
  • params (list, optional): Parameter values for parameterized queries
  • analyze (boolean, optional): Run EXPLAIN ANALYZE (default: False)
  • buffers (boolean, optional): Include buffer usage stats
  • format (string, optional): text, json, xml, yaml

Example:

result = explain_query(
    sql="SELECT * FROM orders WHERE customer_id = %s",
    params=[12345],
    analyze=True,
    buffers=True,
    format="text"
)

Use Cases:

  • Query optimization
  • Index effectiveness validation
  • Execution plan analysis

index_usage_stats

Analyze how effectively indexes are being used.

Parameters:

  • schema (string, optional): Limit to specific schema
  • include_unused (boolean, optional): Include unused indexes

Returns:

  • Index hit ratios
  • Unused indexes
  • Missing index opportunities

Example:

result = index_usage_stats(
    schema="public",
    include_unused=True
)

Use Cases:

  • Index maintenance
  • Performance tuning
  • Storage optimization

suggest_indexes

AI-powered index recommendations using DTA (Database Tuning Advisor) algorithm.

Parameters:

  • workload_queries (list, optional): Queries to analyze
  • use_query_log (boolean, optional): Use pg_stat_statements data
  • limit (integer, optional): Maximum recommendations

Returns:

  • Recommended indexes with impact estimates
  • CREATE INDEX statements
  • Cost/benefit analysis

Example:

result = suggest_indexes(
    use_query_log=True,
    limit=5
)

Use Cases:

  • Performance optimization
  • Index strategy planning
  • Workload analysis

hypothetical_index_test

Test index performance without actually creating indexes (requires HypoPG).

Parameters:

  • table_name (string, required): Target table
  • column_names (list, required): Columns for index
  • test_queries (list, required): Queries to test

Returns:

  • Performance improvements
  • Index size estimates
  • Cost comparison

Example:

result = hypothetical_index_test(
    table_name="orders",
    column_names=["customer_id", "order_date"],
    test_queries=[
        "SELECT * FROM orders WHERE customer_id = 123",
        "SELECT * FROM orders WHERE order_date > '2025-01-01'"
    ]
)

Use Cases:

  • Zero-risk index testing
  • Index design validation
  • Performance forecasting

workload_analysis

Comprehensive analysis of database workload patterns.

Parameters:

  • time_range_hours (integer, optional): Analysis period
  • include_query_patterns (boolean, optional): Pattern analysis
  • include_lock_stats (boolean, optional): Lock contention analysis

Returns:

  • Query type distribution (SELECT/INSERT/UPDATE/DELETE)
  • Peak load times
  • Resource bottlenecks
  • Lock contention hotspots

Example:

result = workload_analysis(
    time_range_hours=24,
    include_query_patterns=True,
    include_lock_stats=True
)

Use Cases:

  • Capacity planning
  • Performance baseline
  • Architecture optimization

🎯 Optimization Workflow

Step 1: Identify Slow Queries

# Find the slowest queries
slow_queries = get_top_queries(sort_by="total_time", limit=10)

# Find high-frequency queries
frequent_queries = get_top_queries(sort_by="calls", limit=10)

Step 2: Analyze Query Plans

# Get execution plan for slow query
plan = explain_query(
    sql=slow_queries["queries"][0]["query"],
    analyze=True,
    buffers=True
)

Step 3: Get Index Recommendations

# AI-powered suggestions
recommendations = suggest_indexes(
    use_query_log=True,
    limit=5
)

Step 4: Test Hypothetical Indexes

# Test without creating
for rec in recommendations["recommendations"]:
    test = hypothetical_index_test(
        table_name=rec["table_name"],
        column_names=rec["columns"],
        test_queries=[slow_query]
    )

Step 5: Analyze Workload

# Overall workload insights
workload = workload_analysis(
    time_range_hours=24,
    include_query_patterns=True
)

📊 Performance Monitoring

Daily Health Check

# 1. Top queries by total time
top_total = get_top_queries(sort_by="total_time", limit=5)

# 2. Index usage stats
indexes = index_usage_stats(include_unused=True)

# 3. Check for unused indexes
if indexes["unused_indexes"]:
    print("Unused indexes found:", indexes["unused_indexes"])

Weekly Optimization

# 1. Workload analysis
workload = workload_analysis(time_range_hours=168)  # 7 days

# 2. Get recommendations
recommendations = suggest_indexes(use_query_log=True, limit=10)

# 3. Test top recommendations
for rec in recommendations["recommendations"][:3]:
    test = hypothetical_index_test(
        table_name=rec["table_name"],
        column_names=rec["columns"],
        test_queries=rec["sample_queries"]
    )

🚀 Best Practices

1. Always Use EXPLAIN ANALYZE

# ✅ Good: Analyze real execution
explain_query(sql="...", analyze=True, buffers=True)

# ❌ Avoid: Planning estimates only
explain_query(sql="...", analyze=False)

2. Test Before Creating Indexes

# ✅ Good: Test with HypoPG first
hypothetical_index_test(...)

# ❌ Avoid: Creating indexes blindly

3. Monitor Index Usage

# Regular cleanup
stats = index_usage_stats(include_unused=True)
# Review stats["unused_indexes"] for removal

4. Focus on High-Impact Queries

# Prioritize by total time (cumulative impact)
get_top_queries(sort_by="total_time", limit=5)

# Not just slow individual queries

📚 Related Documentation

See Home for more tool categories.

Clone this wiki locally