A sluggish database query can bring down an entire web application. When an API endpoint slows down from 25 milliseconds to 8 seconds under load, 95% of the time the culprit is not your server hardware or framework code - it is a missing, fragmented, or poorly chosen database index.

Without proper indexes, a query searching for a specific user or transaction must read every single data page on disk - a catastrophic operation known as a Full Table Scan (Sequential Scan).

In this guide, we break down how to inspect existing indexes, diagnose query execution plans using EXPLAIN ANALYZE, and eliminate bottlenecks visually using SQLOZ on macOS.

SQLOZ Native Table Diagnostics and Index Inspection Hub

1. How B-Tree Indexes Work (The 10-Second Mental Model)

Think of a relational table without an index as an unsorted 1,000-page book. If you want to find every mention of “Kubernetes,” you must flip through every single page from 1 to 1,000.

A B-Tree Index is the book’s index at the back. It maintains sorted references to physical row addresses (TID in Postgres, or Primary Key in MySQL InnoDB). Instead of scanning 1,000,000 rows, the database engine traverses the B-Tree root, branch, and leaf levels in just 3 or 4 logarithmic disk reads (log N).

2. Inspecting Indexes via SQL

In MySQL:

SHOW INDEX FROM orders;

Pay close attention to Cardinality: an index on a column with only two values (e.g. is_active boolean) has low cardinality and may be ignored by the cost-based optimizer in favor of a table scan.

In PostgreSQL:

SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'orders';

3. Diagnosing Queries with EXPLAIN ANALYZE

Never guess why a query is slow - ask the database engine to explain its execution plan:

-- PostgreSQL:
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE customer_id = 4821 AND status = 'completed';

Key Warning Signs in Execution Plans:

  1. Seq Scan (PostgreSQL) or ALL (MySQL): The database scanned the entire table because no usable index matched the WHERE condition.
  2. Using filesort / Sort: The engine had to allocate temporary memory buffers to sort results because the index order didn’t match the ORDER BY clause.
  3. Non-SARGable Functions: Queries like WHERE YEAR(created_at) = 2026 wrap indexed columns in scalar functions, blinding the index. (Fix: WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01').

4. Visual Query Diagnostics in SQLOZ: OzExplain

Reading raw text execution plans with dozens of nested loops and hash joins is visually exhausting.

SQLOZ features OzExplain, a native macOS visual execution plan visualizer:

  1. Visual Execution Tree: Run any query and click the Explain tab. SQLOZ converts raw JSON/text plans into a color-coded hierarchical tree.
  2. Bottleneck Highlighting: Nodes consuming more than 20% of total query execution time are automatically highlighted in Amber or Red.
  3. Automated Index Recommendations (OzAI): Highlight a slow query and ask the integrated OzAI SQL Assistant. It analyzes your schema and suggests the exact composite index definition (CREATE INDEX ...) needed to turn full table scans into sub-millisecond index seeks.

Summary

Query optimization is the highest-leverage skill in backend engineering. When you can inspect indexes and visualize execution plans, slow queries become quick, repeatable fixes.

Download SQLOZ for macOS and experience visual query performance tuning on Apple Silicon today.