Slow query performance investigation

Tokens

Latency

Turns

Tool Calls

Schema Calls

Response

vs

I'll help you identify and diagnose those slow PostgreSQL queries. Let me start by exploring your project to understand the setup and find the queries. I see this is a test environment. Let me create a comprehensive PostgreSQL performance analysis toolkit for you. I'll provide scripts and guidance to identify slow queries and diagnose whether you need indexes, query rewrites, or statistics updates. I've prepared a comprehensive PostgreSQL performance analysis toolkit for you. Here's how to diagnose your slow queries: ## Immediate Action Plan ### 1. **Identify Top Offenders** First, enable `pg_stat_statements` if you haven't already: ```sql -- Check if it's available: SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements'; -- If not enabled, add to postgresql.conf: -- shared_preload_libraries = 'pg_stat_statements' -- Then restart PostgreSQL and run: CREATE EXTENSION IF NOT EXISTS pg_stat_statements; ``` Then run this query to find your slowest queries: ```sql SELECT calls, total_exec_time::numeric(10,2) as total_time_ms, mean_exec_time::numeric(10,2) as avg_time_ms, max_exec_time::numeric(10,2) as max_time_ms, 100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS cache_hit_ratio, LEFT(query, 100) as query_preview FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; ``` ### 2. **Diagnose Each Slow Query** For each identified slow query, run: ```sql EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <YOUR_SLOW_QUERY_HERE>; ``` **What to look for:** - ✅ **"Seq Scan" on large tables** → **Need index** - ✅ **Low cache hit ratio (<90%)** → **I/O bottleneck, need index or query rewrite** - ✅ **High "rows removed by filter"** → **Need better WHERE clause or index** - ✅ **Expensive Hash/Nested Loops** → **Need index on JOIN columns** ### 3. **Check for Missing Indexes** ```sql -- Find tables doing sequential scans frequently: SELECT schemaname, tablename, seq_scan, seq_tup_read, seq_tup_read / NULLIF(seq_scan, 0) as avg_rows_per_scan, pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_tup_read DESC LIMIT 20; ``` If you see large tables with high `seq_tup_read`, they likely need indexes on frequently queried columns. ### 4. **Check Statistics Freshness** ```sql -- Find tables with outdated statistics: SELECT schemaname, tablename, last_analyze, last_autoanalyze, n_mod_since_analyze, n_live_tup FROM pg_stat_user_tables WHERE last_analyze < NOW() - INTERVAL '7 days' OR n_mod_since_analyze > n_live_tup * 0.1 ORDER BY n_mod_since_analyze DESC LIMIT 20; ``` ## Solutions Based on Diagnosis ### If you need **NEW INDEXES:** ```sql -- Single column index: CREATE INDEX CONCURRENTLY idx_table_column ON schema.table(column_name); -- Composite index (most selective column first): CREATE INDEX CONCURRENTLY idx_table_multi ON schema.table(col1, col2, col3); -- Partial index for common filters: CREATE INDEX CONCURRENTLY idx_active_users ON users(email, created_at) WHERE status = 'active'; ``` ### If you need **STATISTICS UPDATE:** ```sql -- For specific table: ANALYZE VERBOSE table_name; -- For entire database: ANALYZE VERBOSE; -- If table has bloat (many dead tuples): VACUUM ANALYZE table_name; ``` ### If you need **QUERY REWRITES:** Common optimization patterns: - Add more selective WHERE clauses earlier - Break complex JOINs into CTEs - Use EXISTS instead of IN for subqueries - Avoid SELECT * (fetch only needed columns) - Add LIMIT to queries that don't need all rows Would you like me to create these diagnostic scripts as files, or do you have specific queries you'd like me to analyze right now? If you can share some of your slow queries or EXPLAIN ANALYZE output, I can give you targeted recommendations.

I'll help you identify and diagnose those slow PostgreSQL queries. Let me start by exploring your project to find query logs, database schemas, and any existing monitoring data. I see you have an MCP server configured. Let me check what tools are available to help analyze your PostgreSQL performance. Perfect! Let me search for PostgreSQL query performance metrics and insights to help diagnose your slow queries. I'm querying the Schema.ai catalog to find PostgreSQL performance metrics and insights. Once you approve, I'll be able to pull comprehensive information about: 1. **Query performance metrics** - execution time, planning time, slow query patterns 2. **Index-related metrics** - index usage, missing indexes, index bloat 3. **Statistics metrics** - stale statistics, autovacuum performance 4. **Common slow query patterns** and their root causes While waiting for approval, here's the typical diagnostic approach I'll help you with: **Top metrics to check:** - `pg_stat_statements` - tracks query execution times and call counts - `pg_stat_user_tables` - shows sequential scans vs index scans - `pg_stat_user_indexes` - reveals unused or under-utilized indexes - `pg_stat_progress_analyze` - checks if ANALYZE is running/needed **Common causes of degradation over time:** 1. **Stale statistics** - PostgreSQL's query planner needs updated statistics 2. **Missing indexes** - New query patterns or data growth exposing gaps 3. **Index bloat** - Indexes becoming fragmented and inefficient 4. **Table bloat** - Dead tuples accumulating from updates/deletes Once you approve the tool access, I'll provide specific metrics to query and insights on what to look for.

Query pg_stat_statements ordered by total_exec_time DESC to find your actual bottlenecks. A query running 50ms but called 10,000 times per hour consumes far more resources than a 10-second query called once per day. Check `postgresql-queries-duration-max` and `postgresql-queries-count` together — focus optimization efforts on queries with high call frequency and moderate per-call cost, as these provide the best ROI. The `slow-queries-high-total-execution-time` insight highlights exactly this pattern.

Compare `postgresql-sequential-scans` to `postgresql-index-scans` — if sequential scans are high on tables with >1M rows, you likely have missing or unused indexes. Also check `postgresql-seq-rows-read` vs `postgresql-index-rows-fetched`; a high ratio of sequential rows read indicates table scans dominating your workload. The `missing-indexes-sequential-scans` insight explains how missing indexes force expensive full-table scans that consume excessive CPU and I/O as data grows.

Run EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) on your top offenders to see the actual execution path. Look for sequential scans where indexes should be used, high row count estimates that differ wildly from actual rows, and nested loops on large result sets. The `inefficient-query-plans-production-slowdowns` insight covers cases where queries run fast in dev but crawl in production due to the planner choosing suboptimal plans at scale.

Check n_mod_since_analyze in pg_stat_all_tables — if you see tables with >10-20% of rows modified since last ANALYZE, your statistics are stale and the query planner is flying blind. Unlike bloat which causes linear performance degradation, stale statistics can cause exponentially worse plans (e.g., the planner thinks a table has 1000 rows when it has 10M). Run ANALYZE manually on high-churn tables or tune autovacuum to run more aggressively. See the `stale-statistics-inefficient-query-plans` insight for details.

Look at `postgresql-queries-shared-blks-read` (disk reads) vs `postgresql-queries-shared-blks-hit` (cache hits) to calculate your buffer cache hit ratio. A ratio below 95% suggests you're doing excessive disk I/O. Check `postgresql-blk-read-time` and `postgresql-blk-write-time` to see if I/O latency is high — if block read time is consistently high relative to query execution time, you may be disk-bound rather than CPU-bound, pointing toward different optimization strategies (more memory, better indexes, or faster storage).

Set log_min_duration_statement to capture queries exceeding a threshold (start with 1000ms for 1 second, tune down as needed). Enable the auto_explain extension with log_min_duration and log_analyze to automatically capture execution plans for slow queries in your logs. This gives you a continuous record of query performance over time rather than point-in-time snapshots. The `slow-queries-duration-threshold` insight explains how to use this for systematic performance tracking.