pg-query-rewrite

PostgreSQL Query Rewrite Patterns

This skill guides the agent through analyzing SQL queries and applying common restructuring patterns to improve performance, using pgtuner-mcp execution plan analysis tools.

When to Use This Skill

Use this skill when the user:

• Has a specific slow query and wants it rewritten for better performance
• Asks "how can I make this query faster?"
• Reports that adding indexes didn't help (query structure is the issue)
• Has queries with subqueries, CTEs, OR conditions, or NOT IN that could be improved
• Asks about query optimization patterns or best practices
• Has queries with SELECT * or inefficient pagination

MCP Resources Available

• pgtuner://docs/tools -- Reference for analyze_query parameters and options
• pgtuner://query/{query_hash}/stats -- Get statistics for a specific query by its queryid
• pgtuner://table/{schema}/{table_name}/indexes -- Check available indexes on involved tables

Related MCP Prompt

This skill is closely related to the query_tuning MCP Prompt, which provides a structured query optimization workflow.

Prerequisites

• The pgtuner-mcp server must be connected with a valid DATABASE_URI
• Required: pg_stat_statements extension (for identifying slow queries to rewrite)
• The user should provide the specific SQL query to optimize

Agent Decision Tree

Start: User provides a slow query
  |
  +--> Step 1: Analyze the execution plan
  |
  +--> Look at the plan for these patterns:
       |
       +--> SubPlan / Correlated Subquery?
       |    --> Apply Pattern 1: Subquery to JOIN
       |
       +--> CTE Scan with many rows materialized?
       |    --> Apply Pattern 2: CTE Materialization Control
       |
       +--> BitmapOr / multiple OR conditions?
       |    --> Apply Pattern 3: OR to UNION ALL
       |
       +--> NOT IN with possible NULLs?
       |    --> Apply Pattern 4: NOT IN to NOT EXISTS
       |
       +--> Seq Scan on query with OFFSET + LIMIT?
       |    --> Apply Pattern 5: Keyset Pagination
       |
       +--> Many columns fetched but few used?
       |    --> Apply Pattern 6: Eliminate SELECT *
       |
       +--> Sort on non-indexed column?
       |    --> Recommend index (redirect to pg-index-optimization)
       |
       +--> No obvious rewrite opportunity?
            --> Check statistics freshness (ANALYZE table)
            --> Check index recommendations
            --> Consider configuration tuning (work_mem, etc.)