🗄️Database & Performance⏱️ 16 min read•Updated October 2026

SQL Query Optimization & Indexing Architecture: EXPLAIN ANALYZE, B-Trees & High-Throughput Database Tuning

A deep-dive manual for backend engineers and DBAs: decoding EXPLAIN (ANALYZE, BUFFERS) execution plans, B-tree composite indexing rules, SARGable predicate restructuring, and autovacuum tuning.

#SQL#PostgreSQL#MySQL#Database#Indexing#Query Optimization#EXPLAIN ANALYZE#Backend

1. Anatomy of an Execution Plan: Deciphering EXPLAIN ANALYZE

The single most important diagnostic command in relational databases is `EXPLAIN (ANALYZE, BUFFERS)` in PostgreSQL (or `EXPLAIN ANALYZE` in MySQL 8.0+). Unlike basic `EXPLAIN` which shows optimizer estimates, adding `ANALYZE` executes the query and compares estimated planning vs actual execution reality: ```sql EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) SELECT u.id, u.email, COUNT(o.id) AS total_orders FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE u.status = 'active' AND u.created_at >= '2026-01-01' GROUP BY u.id, u.email; ``` **Critical Execution Plan Metrics to Inspect:** - **Seq Scan (Sequential Scan)**: The database reads every single table page from disk. Red flag on large tables ($>10,000$ rows) unless reading $>20\%$ of the dataset. - **Index Scan vs Index Only Scan**: Index Scan traverses the index tree and visits the table heap to retrieve non-indexed columns. An **Index Only Scan** fulfills the query entirely from index leaf nodes without touching heap storage pages. - **Bitmap Index Scan / Bitmap Heap Scan**: Finds matching row locations in the index and builds an in-memory bitmap, then fetches table pages sequentially. Ideal for multi-column range queries. - **Buffers (Hit vs Read)**: `Buffers: shared hit=421 read=8` indicates 421 pages were served instantly from RAM (`shared_buffers`) and only 8 pages triggered slow disk I/O.

PostgreSQL Deep Execution Analysis with Buffer Cache Stats

EXPLAIN (ANALYZE, BUFFERS, TIMING, COSTS, VERBOSE) SELECT * FROM transactions WHERE account_id = 98124;

Generates comprehensive tree of execution nodes, page cache hits, and actual execution millisecond costs.

MySQL 8.0 Tree Format Execution Plan

EXPLAIN FORMAT=TREE SELECT * FROM customers WHERE country = "NL" ORDER BY created_at DESC LIMIT 50;

Outputs modern hierarchical iterator tree detailing table scans, temporary tables, and filesorts.

2. B-Tree Indexing Architecture & The Leftmost Prefix Rule

Standard database indexes use balanced trees (B-Trees). Understanding composite index ordering is paramount: **The Leftmost Prefix Principle:** When creating a multi-column index `CREATE INDEX idx_users_org_status_created ON users (org_id, status, created_at)`, the B-tree is sorted primarily by `org_id`, secondarily by `status`, and thirdly by `created_at`. - `WHERE org_id = 5 AND status = 'active'` → **Uses Index Efficiently** - `WHERE org_id = 5` → **Uses Index Efficiently** - `WHERE status = 'active' AND created_at >= '2026-01-01'` → **CANNOT USE INDEX** (skips first prefix column!) **Index Strategy Types:**

Index Type	SQL Definition	Best Use Case
`Covering Index`	CREATE INDEX idx_user_cover ON users (email) INCLUDE (full_name, avatar_url);	Enables ultra-fast Index-Only Scans without table heap lookups
`Partial Index`	CREATE INDEX idx_unprocessed_orders ON orders (created_at) WHERE status = "pending";	Indexes only active rows; uses 95% less disk space and memory
`GIN (Generalized Inverted)`	CREATE INDEX idx_meta_jsonb ON events USING gin (metadata jsonb_path_ops);	Arbitrary JSONB key/value querying and full-text search vectors
`Expression Index`	CREATE INDEX idx_users_lower_email ON users (LOWER(email));	Accelerates case-insensitive lookups without breaking index matching

3. SARGability: Why Functions on Indexed Columns Kill Speed

A search predicate is **SARGable** (Search Argument Able) when the database query optimizer can leverage an index seek rather than scanning the entire table. Wrapping indexed columns inside runtime functions or operators renders queries non-SARGable: ```sql -- ❌ BAD: Non-SARGable (Forces full table Seq Scan across 10 million rows) SELECT * FROM invoices WHERE DATE(created_at) = '2026-10-22'; -- ✅ GOOD: SARGable Range Query (Executes instant B-Tree index range seek) SELECT * FROM invoices WHERE created_at >= '2026-10-22 00:00:00' AND created_at < '2026-10-23 00:00:00'; -- ❌ BAD: Implicit Type Coercion (String index ignored on numeric search) SELECT * FROM accounts WHERE phone_number = 123456789; -- ✅ GOOD: Explicit Matching Type SELECT * FROM accounts WHERE phone_number = '123456789'; -- ❌ BAD: Leading Wildcard in LIKE SELECT * FROM products WHERE sku LIKE '%PROD-981'; -- ✅ GOOD: Trailing Wildcard (Supports B-Tree index scan) SELECT * FROM products WHERE sku LIKE 'PROD-981%'; ```

4. Eliminating the N+1 Query Anti-Pattern & Subqueries

The N+1 query problem occurs when an application executes 1 initial query to fetch a parent list, followed by N separate queries to fetch related child entities. In web applications with ORMs (Prisma, TypeORM, Hibernate), this saturates network bandwidth and exhausts connection pools.

Eager JOIN with Array Aggregation in PostgreSQL

SELECT 
  u.id, 
  u.name, 
  COALESCE(json_agg(json_build_object('id', o.id, 'total', o.total_amount)) FILTER (WHERE o.id IS NOT NULL), '[]') AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

Fetches parents and all nested child records in a single deterministic query round-trip.

Subquery Elimination: EXISTS instead of IN

-- Suboptimal (Materializes entire subquery set in memory):
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 500);

-- Optimized (Short-circuits at the first matching index seek):
SELECT * FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total_amount > 500
);

EXISTS stops scanning the moment an index hit occurs, saving memory and CPU.

5. Connection Pooling & Autovacuum Tuning

Each active database connection in PostgreSQL forks a dedicated operating system process consuming 5-10 MB of RAM. Creating and destroying connections on every HTTP request induces severe OS context switching. **Connection Pooling Architecture:** Deploy **PgBouncer** or **Supavisor** in front of PostgreSQL using `transaction` pooling mode. This allows thousands of microservice clients to share 50-100 pre-warmed backend connections with zero latency penalty. **Autovacuum & MVCC Maintenance:** PostgreSQL uses Multi-Version Concurrency Control (MVCC). When a row is `UPDATE`d or `DELETE`d, the original row is not physically overwritten; it is flagged dead. The `autovacuum` daemon reclaims space and updates table statistics: - Set `autovacuum_vacuum_scale_factor = 0.05` (vacuums when $5\%$ of rows change, rather than default $20\%$) - Set `autovacuum_vacuum_cost_limit = 2000` (accelerates vacuum throughput on modern NVMe drives) - Monitor `n_dead_tup` to prevent index and table bloat from degrading read latency.

6. Mission-Critical Production Diagnostic Queries

Battle-tested administrative queries to diagnose query latency, detect unused indexes, and kill runaway transactions in production PostgreSQL environments:

Find Slowest Queries by Cumulative Execution Time

SELECT 
  round((total_exec_time / 1000 / 60)::numeric, 2) AS total_minutes,
  calls,
  round((mean_exec_time)::numeric, 2) AS avg_ms,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Identifies the top 10 bottleneck queries consuming the most CPU time across the cluster.

Find Completely Unused Indexes Consuming Disk & Write IO

SELECT 
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
  idx_scan as number_of_scans
FROM pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE indisunique IS FALSE
  AND idx_scan = 0
ORDER BY pg_relation_size(i.indexrelid) DESC;

Reveals dead indexes that slow down INSERT/UPDATE operations and waste RAM.

Identify and Terminate Blocking Lock Transactions

SELECT 
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '30 seconds'
  AND state != 'idle';

Finds queries executing for over 30 seconds blocking concurrent operations.

🧰