Slow query performance investigation

Tokens

Latency

Turns

Tool Calls

Schema Calls

Response

vs

I'll help you identify and fix those slow PostgreSQL queries. Let me start by exploring your environment to see what we're working with. I'll create a comprehensive PostgreSQL performance analysis toolkit for you. This will help identify slow queries, missing indexes, and statistics issues. I've created a comprehensive PostgreSQL performance analysis toolkit for you. Here's what it includes: ## **Files Created:** 1. **`analyze_slow_queries.sql`** - Main analysis script that identifies: - Top 20 slowest queries by total time - Queries with highest average execution time - Queries with variable/inconsistent performance - Tables missing indexes (sequential scans on large tables) - Tables with outdated statistics - Unused indexes you can remove - Table bloat estimates - Lock contention issues - Cache hit ratios - Long-running queries 2. **`analyze_specific_query.sql`** - Deep dive into a single slow query with EXPLAIN ANALYZE 3. **`fix_statistics.sql`** - Generates ANALYZE commands for tables needing statistics updates 4. **`run_analysis.sh`** - Convenient shell script to run the analysis 5. **`enable_pg_stat_statements.sql`** - One-time setup for query tracking 6. **`README.md`** - Complete guide with usage instructions and fixing common issues ## **How to Use:** ```bash # 1. Make script executable chmod +x run_analysis.sh # 2. Run the analysis ./run_analysis.sh your_database localhost 5432 postgres # 3. Save to file for review ./run_analysis.sh your_database > performance_report.txt ``` ## **What to Look For:** The analysis will immediately show you: - **Queries consuming the most time** - your top optimization targets - **Missing indexes** - tables doing full scans when they shouldn't - **Stale statistics** - causing PostgreSQL to choose bad query plans - **Bloated tables** - needing VACUUM Based on the results, you'll know whether you need **indexes** (analysis #4), **statistics updates** (analysis #5), or **query rewrites** (analysis #1-3). Would you like me to explain any specific part of the toolkit or help you interpret results once you run it?

I'll help you identify and optimize those slow PostgreSQL queries. Let me start by exploring what we have in this directory. I can see you have a Schema.ai MCP server configured. This appears to be a database schema and query analysis tool. Let me access these tools to help identify your slow queries. Perfect! Now let me search for PostgreSQL query performance insights and metrics to help identify your slow queries. I need your permission to access the Schema.ai tools to analyze your PostgreSQL performance. Once you approve, I'll be able to: 1. **Identify slow query patterns** - Find metrics and insights about query performance degradation 2. **Analyze index usage** - Determine if missing or unused indexes are causing slowdowns 3. **Check statistics** - See if stale table statistics need updating 4. **Review query plans** - Get guidance on query optimization techniques Please approve the tool access, and I'll immediately start investigating your PostgreSQL performance issues.

Query pg_stat_statements ordered by `stat_statements_seconds` (total_exec_time) DESC, not just mean execution time. A query running 50ms but called 10,000 times per hour (139 hours total) is far worse than a 5-second query called once. Look at the `stat_statements_calls` count alongside duration — high call frequency with moderate latency usually offers the best optimization ROI. Focus on the top 5-10 queries by total time for the rest of your investigation.

Check `last-analyze` timestamps and `n_mod_since_analyze` counts in pg_stat_user_tables for the tables used by your slow queries. If `n_mod_since_analyze` shows thousands or millions of rows modified since the last ANALYZE, or `last-analyze` is days/weeks old, your query planner is making decisions on outdated statistics, which can cause exponentially worse query plans. Run ANALYZE on those tables immediately and retest — this is the most common culprit and takes seconds to fix.

Run EXPLAIN (ANALYZE, BUFFERS, VERBOSE) on your top offending queries to see the actual execution plan and I/O patterns. Look for sequential scans on large tables, nested loops with high row counts, and hash joins that spill to disk. Compare `postgresql.blk_read_time` — if block read time is high, you're I/O bound. Pay attention to the planner's row count estimates versus actual rows — large discrepancies confirm stale statistics or missing WHERE clause selectivity.

Compare `postgresql.sequential_scans` to `postgresql.index.scans` at the table level, and check `postgresql.seq_rows_read` versus `postgresql.index.rows_fetched`. If you see high sequential scan counts on tables with >100K rows, especially in your top slow queries, you likely need indexes. Cross-reference with EXPLAIN output — look for 'Seq Scan' nodes where you expected index usage. Create indexes on columns used in WHERE clauses, JOIN conditions, and ORDER BY for those queries.

If sequential scans are legitimate (small tables or queries that need full scans), check `n_dead_tup` to identify table bloat. Bloat increases I/O cost linearly since PostgreSQL reads dead tuples during scans even though they're not returned. If `n_dead_tup` is >20% of live tuples and `postgresql.blk_read_time` is elevated, run VACUUM on those tables or tune autovacuum to run more aggressively. This is especially important after bulk operations or heavy UPDATE/DELETE workloads.

Look at `postgresql.temp_bytes` to identify queries spilling sorts, hashes, or aggregations to disk due to insufficient work_mem. If temp file usage is high (megabytes to gigabytes per query), your queries are memory-starved. Either increase work_mem (carefully — it's per-operation, not per-connection) or rewrite queries to reduce sort/hash operations. This is common with large JOINs, GROUP BY, or ORDER BY on unindexed columns.