Query Plan Cache

Query Plan Cache

HeliosDB-Lite includes an automatic query plan cache that eliminates redundant parsing, planning, RLS evaluation, and optimization for repeated SQL strings. In benchmarks this delivers up to 129x speedup for cached queries. The cache is transparent — no configuration needed — and automatically invalidates on DDL changes.

Prerequisites

• HeliosDB-Lite v3.5+
• A database with tables for testing

How It Works

Cache Architecture

1. Hash computation — When a query arrives, HeliosDB computes a 64-bit hash of the SQL string using the standard Rust hasher.
2. Cache lookup — The hash is checked against an in-memory HashMap<u64, LogicalPlan> protected by a RwLock. Multiple readers can check the cache concurrently.
3. Cache hit — If found, the stored optimized LogicalPlan is cloned and executed directly, skipping parse, plan, and optimize phases.
4. Cache miss — The query goes through the full pipeline: parse, plan, RLS enforcement, optimization. The resulting plan is stored in the cache for future use.

What Gets Cached

• The fully optimized LogicalPlan after all optimizer rules have run
• Includes predicate pushdown, join reordering, materialized view rewrites

What Triggers Invalidation

Any DDL statement clears the entire plan cache and increments the cache generation counter:

• CREATE TABLE / DROP TABLE / ALTER TABLE
• CREATE INDEX / DROP INDEX
• CREATE VIEW / DROP VIEW / CREATE MATERIALIZED VIEW
• GRANT / REVOKE (privilege changes affect query access)
• CREATE ROLE / DROP ROLE

This ensures that queries always run against current schema and privileges.

Step 1: Create Test Data

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL,
    status TEXT,
    created_at TIMESTAMP
);

CREATE INDEX idx_orders_customer ON orders (customer_id);

INSERT INTO orders VALUES (1, 100, 250.00, 'shipped',  '2026-03-01');
INSERT INTO orders VALUES (2, 101, 450.00, 'pending',  '2026-03-02');
INSERT INTO orders VALUES (3, 100, 125.00, 'shipped',  '2026-03-03');
INSERT INTO orders VALUES (4, 102, 800.00, 'returned', '2026-03-04');
INSERT INTO orders VALUES (5, 100, 300.00, 'pending',  '2026-03-05');

Step 2: First Execution (Cache Miss)

SELECT * FROM orders WHERE customer_id = 100 AND status = 'shipped';

On the first execution, the full pipeline runs:

1. Parse: SQL text to AST
2. Plan: AST to LogicalPlan
3. RLS: Row-level security filter injection
4. Optimize: Predicate pushdown, index selection, etc.
5. Execute: Volcano-style evaluation
6. Cache store: Optimized plan saved under the SQL hash

Step 3: Repeated Execution (Cache Hit)

Run the exact same SQL again:

SELECT * FROM orders WHERE customer_id = 100 AND status = 'shipped';

This time steps 1-4 are skipped entirely. The cached plan is retrieved and execution starts immediately.

Measuring the Difference

Use EXPLAIN ANALYZE to see the timing breakdown:

-- First run (populates cache)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100 AND status = 'shipped';

-- Second run (cache hit -- plan phase shows ~0ms)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100 AND status = 'shipped';

In benchmarks with concurrent workloads, cached queries achieve up to 129x throughput improvement because the parse/plan/optimize overhead dominates for simple queries.

Step 4: Cache Invalidation via DDL

-- This invalidates the plan cache
ALTER TABLE orders ADD COLUMN notes TEXT;

-- The next query is a cache miss (full pipeline)
SELECT * FROM orders WHERE customer_id = 100 AND status = 'shipped';
-- Subsequent runs are cache hits again

Every DDL operation calls invalidate_plan_cache() which:

1. Clears all entries from the HashMap
2. Increments the plan_cache_generation atomic counter

Step 5: Cache Behavior with Parameters

The plan cache keys on the exact SQL string. Different parameter values produce different hashes:

-- These are TWO separate cache entries:
SELECT * FROM orders WHERE customer_id = 100;
SELECT * FROM orders WHERE customer_id = 101;

For applications that use PREPARE/EXECUTE or parameterized queries ($1, $2), the prepared statement mechanism provides similar benefits.

Step 6: Understanding Cache Scope

• Per-database instance — Each EmbeddedDatabase has its own plan cache.
• Shared across connections — The cache uses Arc<RwLock<...>> so all connections to the same database share the cached plans.
• No size limit — The cache grows with the number of unique SQL strings. In practice, applications use a finite set of query patterns.
• Not persisted — The cache is in-memory only and starts empty on database restart.

Performance Characteristics

Operation	Without cache	With cache	Speedup
Simple SELECT	~50 us	~0.4 us	~129x
Complex JOIN	~200 us	~5 us	~40x
Aggregation	~100 us	~2 us	~50x
INSERT (DML)	~30 us	~30 us	1x (no cache)

DML statements (INSERT, UPDATE, DELETE) go through the standard pipeline and are not cached because they modify data.

Step 7: Monitoring

Check optimizer statistics

SELECT * FROM pg_stat_optimizer;

This view shows optimizer metrics including rules applied and planning times, which drop to zero for cached queries.

Check database-level statistics

SELECT datname, xact_commit, tup_returned, tup_fetched
FROM pg_stat_database;

Best Practices

1. Use consistent SQL strings — The cache matches on exact string equality. Differences in whitespace, capitalization, or comments produce separate cache entries. Normalize SQL in your application layer.

2. Use prepared statements for variable queries — Instead of embedding values in SQL strings, use PREPARE and EXECUTE with parameters:

   PREPARE get_orders AS SELECT * FROM orders WHERE customer_id = $1;
   EXECUTE get_orders(100);
   EXECUTE get_orders(101);

3. Minimize DDL during peak hours — Every DDL statement flushes the entire cache. Batch schema changes during maintenance windows.

4. Let the cache warm up — After a restart, the first execution of each query will be slower. In latency-sensitive applications, consider a warm-up phase that runs common queries.

Common Pitfalls

1. String sensitivity — SELECT * FROM orders and select * from orders are different cache entries (different hash). Standardize case in your application.

2. DML does not cache — INSERT, UPDATE, and DELETE always go through the full pipeline. The cache only benefits read queries.

3. GRANT/REVOKE invalidates cache — Changing privileges flushes the cache because cached plans may reference old access rules. This is correct but means frequent privilege changes have a performance cost.

4. No partial invalidation — The cache is all-or-nothing. There is no per-table invalidation. A DDL change on any table flushes plans for all tables.

5. Memory — Each cached plan is a cloned LogicalPlan. For applications with millions of unique SQL strings, memory usage can grow. In practice, most applications have thousands of unique patterns at most.