TOOL API

prepare_query_tuning

<use_case> This tool helps developers improve PostgreSQL query performance for slow queries or DML statements by analyzing execution plans and suggesting optimizations. The tool will: 1. Create a temporary branch for testing optimizations and remember the branch ID 2. Extract and analyze the current query execution plan 3. Extract all fully qualified table names (`schema.table`) referenced in the plan 4. Gather detailed schema information for each referenced table using `describe_table_schema` 5. Suggest and implement improvements like: - Adding or modifying indexes based on table schemas and query patterns - Query structure modifications - Identifying potential performance bottlenecks 6. Apply the changes to the temporary branch using `run_sql` 7. Compare performance before and after changes (but ONLY on the temporary branch passing branch ID to all tools) 8. Continue with next steps using `complete_query_tuning` tool (on `main` branch) Project ID and database name will be automatically extracted from your request. The temporary branch ID will be added when invoking other tools. Default database is `neondb` if not specified. <important_notes> This tool is part of the query tuning workflow. Any suggested changes (like creating indexes) must first be applied to the temporary branch using the `run_sql` tool. And then to the main branch using the `complete_query_tuning` tool, NOT the `prepare_database_migration` tool. To apply using the `complete_query_tuning` tool, you must pass the `tuning_id`, NOT the temporary branch ID to it. </important_notes> </use_case> <workflow> 1. Creates a temporary branch 2. Analyzes current query performance and extracts table information 3. Implements and tests improvements (using tool `run_sql` for schema modifications and `explain_sql_statement` for performance analysis, but ONLY on the temporary branch created in step 1 passing the same branch ID to all tools) 4. Returns tuning details for verification </workflow> <important_notes> After executing this tool, you MUST: 1. Review the suggested changes 2. Verify the performance improvements on temporary branch - by applying the changes with `run_sql` and running `explain_sql_statement` again) 3. Decide whether to keep or discard the changes 4. Use `complete_query_tuning` tool to apply or discard changes to the main branch DO NOT use `prepare_database_migration` tool for applying query tuning changes. Always use `complete_query_tuning` to ensure changes are properly tracked and applied. Note: - Some operations like creating indexes can take significant time on large tables - Table statistics updates (ANALYZE) are NOT automatically performed as they can be long-running - Table statistics maintenance should be handled by PostgreSQL auto-analyze or scheduled maintenance jobs - If statistics are suspected to be stale, suggest running ANALYZE as a separate maintenance task </important_notes> <example> For a query like: ```sql SELECT o.*, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'pending' AND o.created_at > '2024-01-01'; ``` The tool will: 1. Extract referenced tables: `public.orders`, `public.customers` 2. Gather schema information for both tables 3. Analyze the execution plan 4. Suggest improvements like: - Creating a composite index on orders(status, created_at) - Optimizing the join conditions 5. If confirmed, apply the suggested changes to the temporary branch using `run_sql` 6. Compare execution plans and performance before and after changes (but ONLY on the temporary branch passing branch ID to all tools) </example> <next_steps> After executing this tool, you MUST follow these steps: 1. Review the execution plans and suggested changes 2. Follow these instructions to respond to the client: <response_instructions> <instructions> Provide a brief summary of the performance analysis and ask for approval to apply changes on the temporary branch. You MUST include ALL of the following fields in your response: - Tuning ID (this is required for completion) - Temporary Branch Name - Temporary Branch ID - Original Query Cost - Improved Query Cost - Referenced Tables (list all tables found in the plan) - Suggested Changes Even if some fields are missing from the tool's response, use placeholders like "not provided" rather than omitting fields. </instructions> <do_not_include> IMPORTANT: Your response MUST NOT contain ANY technical implementation details such as: - Exact index definitions - Internal PostgreSQL settings - Complex query rewrites - Table partitioning details Keep the response focused on high-level changes and performance metrics. </do_not_include> <example> I've analyzed your query and found potential improvements that could reduce execution time by [X]%. Would you like to apply these changes to improve performance? Analysis Details: - Tuning ID: [id] - Temporary Branch: [name] - Branch ID: [id] - Original Cost: [cost] - Improved Cost: [cost] - Referenced Tables: * public.orders * public.customers - Suggested Changes: * Add index for frequently filtered columns * Optimize join conditions To apply these changes, I will use the `complete_query_tuning` tool after your approval and pass the `tuning_id`, NOT the temporary branch ID to it. </example> </response_instructions> 3. If approved, use ONLY the `complete_query_tuning` tool with the `tuning_id` </next_steps> <error_handling> On error, the tool will: 1. Automatically attempt ONE retry of the exact same operation 2. If the retry fails: - Terminate execution - Return error details - Clean up temporary branch - DO NOT attempt any other tools or alternatives Error response will include: - Original error details - Confirmation that retry was attempted - Final error state Important: After a failed retry, you must terminate the current flow completely. </error_handling>