Skip to main content

Proving Performance with EXPLAIN ANALYZE

How LZStock guarantees sub-millisecond API latencies by analyzing PostgreSQL execution plans and optimizing composite B-Tree indexes.

TL;DR
  • Data-Driven Query Profiling: Eliminated optimization guesswork by utilizing EXPLAIN (ANALYZE, BUFFERS) to definitively prove the query planner executed an efficient Index Scan, completely bypassing expensive Seq Scan and memory Sort nodes.
  • Strategic Composite Indexing: Mastered B-Tree traversal mechanics by strictly ordering index columns (Equality first, Range second: is_active, id), instantly narrowing the initial search space and preventing severe index inefficiencies.
  • Sub-Millisecond Cache Validation: Validated hardware-level I/O efficiency by monitoring the shared hit vs. read metrics, ensuring the vast majority of data pages are served directly from PostgreSQL's RAM buffer cache rather than triggering slow physical disk reads.

The Objective​

In our previous architectural decisions, we implemented Keyset (Cursor) Pagination for the GetAllTickers background job. However, architectural theory is useless without database-level validation.

If the database lacks the correct indexing strategy, a cursor pagination query will still degrade into a catastrophic Seq Scan (Full Table Scan) or trigger an expensive in-memory Sort. The objective of this document is to utilize EXPLAIN (ANALYZE, BUFFERS) to verify that our queries successfully hit the B-Tree index, eliminate disk I/O, and achieve absolute O(1)O(1) stability.

The Mental Model & Execution Tree​

When analyzing a query plan, we read from the inside out (bottom to top). The goal is to see an Index Scan or Index Only Scan at the deepest leaf node, feeding into a Limit node, completely bypassing any Sort or Seq Scan nodes.

Core Implementation​

To support the cursor query efficiently, we need a Composite Index. The order of columns in the composite index is critical.

The Composite Index Strategy​

-- Creates a B-Tree index. 
-- The order (is_active, id) allows the DB to instantly filter active companies,
-- and then traverse the sequentially ordered UUIDs.
CREATE INDEX idx_global_companies_active_id ON global_company_view (is_active, id);

Profiling the Query​

We use EXPLAIN (ANALYZE, BUFFERS) to execute the query and measure the actual hardware footprint.

-- The Cursor Query
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, company_name, ticker, exchange_code, is_active
FROM global_company_view 
WHERE is_active = true 
  AND id > 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'
ORDER BY id ASC 
LIMIT 100;

Analyzing the Output (The Proof)​

Here is the curated output of the execution plan. Notice the absence of Seq Scan and the presence of shared hit.

View Raw EXPLAIN ANALYZE Output
Limit  (cost=0.42..158.67 rows=100 width=89) (actual time=0.123..0.456 rows=100 loops=1)
  Buffers: shared hit=4 read=1
  ->  Index Scan using idx_global_companies_active_id on global_company_view  
      (cost=0.42..15867.23 rows=10000 width=89) (actual time=0.120..0.432 rows=100 loops=1)
      Index Cond: ((is_active = true) AND (id > 'a1b2c3d4-e5f6-7890-abcd-ef1234567890'::uuid))
      Buffers: shared hit=4 read=1
Planning Time: 0.234 ms
Execution Time: 0.567 ms

Key Metrics Validated:

  1. Node Type: Index Scan (Optimal. The database used our exact composite index).
  2. Memory Metrics: shared hit=4, read=1. (Excellent. 4 pages were read directly from RAM cache, only 1 required a physical disk read).
  3. Execution Time: 0.567 ms (Sub-millisecond latency achieved).

Edge Cases & Trade-offs​

  • Index Column Order Matters: Why (is_active, id) and not (id, is_active)? In a composite index, the database traverses from left to right. Because our query uses an equality operator on is_active (= true) and a range operator on id (> 'uuid'), putting the equality column first narrows down the search space immediately before traversing the range. Putting id first would render the is_active part of the index largely useless.

  • Redundant Indexes (The Write Penalty): It is a common mistake to create multiple similar indexes (e.g., one for ASC and one for DESC). PostgreSQL B-Trees support backward scanning natively. Creating redundant indexes provides zero read benefits while doubling the I/O write penalty during INSERT and UPDATE operations, degrading write-heavy performance.

  • The "Index Only Scan" Illusion: To push performance even further, we could achieve an Index Only Scan (where the DB doesn't even touch the main table) by including all selected columns in the index: CREATE INDEX ... INCLUDE (company_name, ticker). However, this massively inflates the size of the index in RAM. For a 100-row batch limit, a standard Index Scan is the optimal trade-off between RAM usage and query speed.

The Outcome​

By establishing a strict database profiling culture, LZStock guarantees that complex pagination and data hydration queries execute in under 1 millisecond with a >80% memory cache hit ratio, ensuring horizontal scalability without overloading the PostgreSQL instance.