How to Find Slow SQL Queries in Your App (and Fix Them)

When a web application starts timing out in production, your HTTP monitoring alert tells you which endpoint is slow, not why. Maybe GET /api/orders jumped from 80 ms to 1,900 ms after a busy week. You open your application logs, see a dozen SQL queries inside that request handler, and run the biggest JOIN in your local SQL client. Locally, against a smaller dataset or a warm buffer cache, it finishes in 4 ms.

That gap is where developers lose hours guessing at indexes. The good news is that PostgreSQL, SQL Server, MySQL, and MariaDB already record cumulative execution statistics for every normalized query shape in memory. You do not need to install a heavy APM agent on the database host or turn on full query logging that fills up your disk. You just need to read the statistics tables your database engine already maintains, rank queries by their share of total database time, inspect the execution plan of the top offender, and verify that your fix reduces the actual page reads.

Here is the step-by-step workflow for finding and fixing the queries that actually slow down your web app.

1. Enable and Read the Built-In Database Statistics Tables

Every major relational database has a built-in extension or system schema that normalizes SQL statements (stripping literal values like WHERE user_id = 42 into WHERE user_id = $1) and tracks how many times each query ran, how much CPU and wall-clock time it consumed, and how many disk or buffer blocks it read.

PostgreSQL: pg_stat_statements

In PostgreSQL, the pg_stat_statements extension is included in the standard postgresql-contrib package and is preloaded by default on managed services like RDS, Supabase, Neon, and Cloud SQL.

To check if it is active, run:

SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  shared_blks_hit,
  shared_blks_read,
  left(query, 120) AS sql_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Two columns matter more than anything else here: total_exec_time (the cumulative milliseconds the database spent running that query across all calls) and shared_blks_read (blocks read from disk or the OS page cache because they were not in PostgreSQL’s shared buffers).

SQL Server: Query Store

In Microsoft SQL Server (2016 and newer, and Azure SQL Database), Query Store is built directly into the engine and persists execution history across server restarts. It separates query text (sys.query_store_query_text), execution plans (sys.query_store_plan), and runtime metrics (sys.query_store_runtime_stats) into time windows.

SELECT TOP 15
  q.query_id,
  qt.query_sql_text,
  SUM(rs.count_executions) AS total_executions,
  SUM(rs.count_executions * rs.avg_duration) / 1000.0 AS total_duration_ms,
  AVG(rs.avg_duration) / 1000.0 AS avg_duration_ms,
  AVG(rs.avg_logical_io_reads) AS avg_logical_reads
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt
  ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p
  ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs
  ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_duration_ms DESC;

Because Query Store tracks each plan separately, it also reveals plan regressions — cases where a parameter-sensitive query suddenly switched from a fast Index Seek plan to a slow Clustered Index Scan after statistics updated.

MySQL and MariaDB: performance_schema

In MySQL and MariaDB, the events_statements_summary_by_digest table in performance_schema aggregates normalized SQL digests:

SELECT
  SCHEMA_NAME,
  DIGEST_TEXT,
  COUNT_STAR AS calls,
  ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS total_ms,
  ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
  SUM_ROWS_EXAMINED AS rows_examined,
  SUM_ROWS_SENT AS rows_sent,
  SUM_NO_INDEX_USED AS no_index_calls
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 15;

Notice the ratio between SUM_ROWS_EXAMINED and SUM_ROWS_SENT. If a query examines 4,000,000 rows to return 20 rows to your application, you have a missing or mismatched index.

2. Rank Queries by Share of Total Database Time (Not Just Slowest Single Run)

The most common mistake when reading raw statistics tables is sorting by max_exec_time or mean_exec_time.

Suppose your database has two slow queries:

1. A nightly CSV export query that takes 2,400 ms and runs once a day at 03:00 UTC (2.4 seconds of database time per day).
2. A session permission check that takes 38 ms and runs 600 times per minute on every authenticated API request (22.8 seconds of database time every single minute).

A slow-query log with a long_query_time = 1.0 threshold will flag the 2.4-second nightly report and completely ignore the 38 ms permission query that is saturating your CPU cores during business hours.

When you rank queries by percentage of total execution time (total_exec_time / sum(total_exec_time)) and percentage of total buffer reads, the real bottleneck stands out immediately.

Reading raw pg_stat_statements or sys.query_store_runtime_stats joins by hand works, but correlating those numbers with missing indexes, table bloat, and lock waits across multiple engines takes a lot of boilerplate SQL. That is why I built Culprit SQL Studio, a free, open-source (MIT) desktop app and CLI for SQL Server, PostgreSQL, MySQL, and MariaDB. It connects read-only to your database, reads the statistics tables that are already there without installing anything on the server, and ranks every finding by its share of total database time alongside the evidence and a production-safe fix. You can also inspect the raw tables directly in general-purpose database clients like DBeaver or pgAdmin.

3. Read the Execution Plan: Three Causes Behind 90% of Slow Web App Queries

Once you have isolated the query that consumes the largest share of database time, capture its execution plan.

In PostgreSQL, run EXPLAIN (ANALYZE, BUFFERS) inside a transaction (wrapped in BEGIN; ... ROLLBACK; if the statement mutates data). If you want a visual breakdown of a complex JSON plan in PostgreSQL, tools like pgMustard or explain.dalibo.com are also helpful references alongside desktop analyzers.

Across web applications, three patterns account for the vast majority of high-cost execution plans:

Cause 1: Sequential Scan (or Index Scan with High Filter Drops) on a Multi-Column Predicate

Suppose your application fetches recent unread notifications for a user:

SELECT id, title, created_at
FROM notifications
WHERE account_id = 10482
  AND archived_at IS NULL
ORDER BY created_at DESC
LIMIT 20;

When you run EXPLAIN (ANALYZE, BUFFERS), you see:

Limit  (cost=14289.12..14289.17 rows=20 width=52) (actual time=41.820..41.826 rows=20 loops=1)
  Buffers: shared hit=3840 read=912
  ->  Sort  (cost=14289.12..14312.40 rows=9312 width=52) (actual time=41.818..41.821 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Index Scan using idx_notifications_account_id on notifications
              Index Cond: (account_id = 10482)
              Filter: (archived_at IS NULL)
              Rows Removed by Filter: 48210

Look at Rows Removed by Filter: 48210 and Buffers: shared hit=3840 read=912. The database used the single-column index on account_id, fetched 48,230 heap rows from disk and memory, threw away 48,210 archived notifications, sorted the remaining rows in memory, and returned 20.

The Fix: Create a partial composite index that matches the equality filter, the IS NULL predicate, and the sort order:

CREATE INDEX CONCURRENTLY idx_notifications_account_unarchived_created
ON notifications (account_id, created_at DESC)
WHERE archived_at IS NULL;

Always use CONCURRENTLY in PostgreSQL (or ONLINE = ON in SQL Server) so the index build does not block concurrent writes in production.

Cause 2: Key Lookups and Heap Fetches Caused by SELECT *

ORMs default to selecting every column on a model (SELECT * FROM orders WHERE ...). Even when you have a composite index on (customer_id, status), selecting wide text or JSON columns forces the database to jump from the B-tree leaf node back to the main table heap (a Key Lookup in SQL Server or a heap fetch in PostgreSQL) for every matching row.

If a list endpoint only renders id, order_number, total_cents, and created_at, select only those columns. When those columns are included in the index (INCLUDE (order_number, total_cents)), the database satisfies the entire query from an Index-Only Scan with zero heap reads.

Cause 3: Lock Waits and Blocking Chains Disguised as Slow Queries

Sometimes a fast UPDATE or SELECT ... FOR UPDATE takes 3,000 ms not because the plan is bad, but because it is waiting behind an idle-in-transaction connection or a long batch update holding a row or page lock.

You can spot this immediately when mean_exec_time is high while shared_blks_hit + shared_blks_read is tiny (for example, 4 buffer hits taking 2,500 ms). Check pg_stat_activity joined with pg_locks in PostgreSQL (or sys.dm_os_waiting_tasks in SQL Server) to find the blocking PID and shorten the transaction scope so your application never holds a database transaction open across an external HTTP call.

4. Verify the Fix Before and After

Never ship an index and assume it worked without checking the numbers.

1. Re-run EXPLAIN (ANALYZE, BUFFERS): Confirm that the plan switched to an Index Scan or Index Only Scan on the new index, that Rows Removed by Filter dropped to 0, and that Buffers: shared hit dropped from thousands of pages to a handful (typically 3 to 6 buffer hits for a 20-row limit).
2. Check the CLI or MCP output: You can run the same verification from the terminal (culprit analyze) or through the MCP server inside editors like Cursor or Claude Code, comparing the query’s average execution time and buffer reads before and after the change.
3. Watch for unused indexes: Every index slows down INSERT, UPDATE, and DELETE operations on that table. After a week in production, check pg_stat_user_indexes (idx_scan = 0) or SQL Server’s sys.dm_db_index_usage_stats and drop any old single-column index that your new composite index made redundant.

5. Quick Production Checklist for Slow SQL Queries

• Sort by total execution time (calls * mean_exec_time), not just slowest single run.
• Inspect buffer reads (shared_blks_hit + shared_blks_read or logical_reads), not wall-clock time alone — cache state changes wall-clock time, while page reads stay deterministic.
• Match index column order to your query: equality predicates first (=), then sort columns (ORDER BY), then range predicates (>, <), with a WHERE clause for partial indexes when you only query active/unarchived rows.
• Build indexes online (CREATE INDEX CONCURRENTLY in PostgreSQL, WITH (ONLINE = ON) in SQL Server) so production traffic never stalls.
• Verify the plan and drop redundant indexes once the new composite index takes over traffic.

---

Author Bio

Cristi Pufu is a software engineer and the creator of Culprit SQL Studio (culpritsql.com), an open-source desktop application and CLI for diagnosing slow queries, execution plans, and blocking in SQL Server, PostgreSQL, MySQL, and MariaDB. He builds developer tools and writes about database performance and pragmatic software engineering.