acceptance-criteria:
  - [ ] Data persisted correctly; constraints respected; no orphaned data
    type: acceptance-criterion
    blocker: true
    validation: |
      Assert data persisted correctly; constraints respected; no orphaned data
    error_message: "Acceptance criterion not met: Data persisted correctly; constraints respected; no orphaned data"

Error Handling

Strategy:* retry

Common Errors:*

Error:* Connection Failed
- Cause:* Unable to connect to Neo4j database
- Resolution:* Check connection string, credentials, network
- Recovery:* Retry with exponential backoff (max 3 attempts)
Error:* Query Syntax Error
- Cause:* Invalid Cypher query syntax
- Resolution:* Validate query syntax before execution
- Recovery:* Return detailed syntax error, suggest fix
Error:* Transaction Rollback
- Cause:* Query violates constraints or timeout
- Resolution:* Review query logic and constraints
- Recovery:* Automatic rollback, preserve data integrity

Inputs

sql (string): SQL query to analyze

1. Confirm Query

Ask user:

Query to analyze
Expected result count (approximate)
Known performance issues?

2. Run EXPLAIN ANALYZE

Execute with full analysis options:

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<SQL
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
{sql};
SQL

3. Interpret Results

Present key metrics:

=== Query Performance Analysis ===

Execution Time: X.XX ms
Planning Time: Y.YY ms
Total Time: Z.ZZ ms

Buffers:
  - Shared Hit: XXX (cache hits)
  - Shared Read: YYY (disk reads)
  - Temp Read/Written: ZZZ (temp files)

Cost: XXX.XX..YYY.YY
Rows: Estimated XXX, Actual YYY

Top-Level Metrics

Planning Time*

Time spent planning query
High value (>100ms) suggests complex query or missing statistics

Execution Time*

Actual query execution time
This is what users experience

Total Cost*

Estimated cost units (not milliseconds)
Higher = more expensive
Compare different query versions

Node Types (Common Patterns)

Seq Scan* (Sequential Scan)

🔴 Reads entire table
Slow for large tables
Fix*: Add index if filtering rows

Index Scan*

✅ Uses index to find rows
Fast for selective queries
Good when returning few rows

Index Only Scan*

✅✅ Best case - reads only index
No table access needed
Requires VACUUM to update visibility map

Bitmap Heap Scan*

✅ Good for medium selectivity
Combines multiple indexes
Better than multiple index scans

Nested Loop*

Good for small result sets
Joins by iterating
Can be slow with large data

Hash Join*

Good for large result sets
Builds hash table in memory
Fast for equi-joins

Merge Join*

Good for sorted inputs
Efficient for large sorted data
Requires sorted inputs (or sorts them)

Buffer Analysis

Shared Hits* (Good)

Data found in cache
No disk I/O needed
High ratio = good caching

Shared Reads* (Bad if high)

Data read from disk
Slow compared to cache
High ratio = cache misses

Temp Read/Written* (Bad)

Using temp disk files
Memory insufficient
Often due to large sorts/hashes

Issue 1: Sequential Scan on Large Table

Seq Scan on fragments  (cost=0.00..10000 rows=1000000)
  Filter: (user_id = '...')

Problem*: Scanning entire table
Impact*: Slow for large tables
Fix*: Create index

CREATE INDEX idx_fragments_user_id ON fragments(user_id);

Issue 2: Missing Index on Join

Nested Loop  (cost=0.00..50000)
  -> Seq Scan on users
  -> Seq Scan on fragments
       Filter: (fragments.user_id = users.id)

Problem*: No index for join condition
Impact*: Quadratic complexity
Fix*: Index foreign key

CREATE INDEX idx_fragments_user_id ON fragments(user_id);

Issue 3: High Temp File Usage

Sort  (cost=10000..12000)
  Sort Key: created_at DESC
  Sort Method: external merge  Disk: 5000kB

Problem*: Sorting spills to disk
Impact*: Much slower than in-memory
Fix*: Increase work_mem or add index

-- Option 1: Increase memory (session)
SET work_mem = '64MB';

-- Option 2: Add index to avoid sort
CREATE INDEX idx_fragments_created_at ON fragments(created_at DESC);

Issue 4: Poor Row Estimate

Seq Scan on users  (cost=0.00..100 rows=10 actual rows=10000)

Problem*: Estimated 10 rows, actually 10,000
Impact*: Wrong join strategy chosen
Fix*: Update statistics

ANALYZE users;
-- Or more aggressive:
VACUUM ANALYZE users;

Issue 5: Slow RLS Policy

Seq Scan on fragments  (cost=0.00..10000 rows=500000)
  Filter: ((user_id = auth.uid()) AND (deleted_at IS NULL))
  Rows Removed by Filter: 499990

Problem*: RLS policy not using index
Impact*: Scans all rows to apply policy
Fix*: Index RLS policy columns

CREATE INDEX idx_fragments_user_id_not_deleted 
ON fragments(user_id) 
WHERE deleted_at IS NULL;

1. Baseline

Run current query:

explain "SELECT * FROM table WHERE ..."

Note execution time and plan.

2. Hypothesize

What might be slow?

Sequential scans?
Missing indexes?
Sort/hash spills?
Poor statistics?

3. Test Fix

Apply potential fix:

CREATE INDEX ...;
-- or
VACUUM ANALYZE table;
-- or
SET work_mem = '...';

4. Re-Measure

Run explain again:

explain "SELECT * FROM table WHERE ..."

Compare:

Execution time improved?
Plan changed as expected?
Cost reduced?

5. Iterate

Repeat until performance acceptable.

Compare Different Queries

# Option A
explain "SELECT * FROM users WHERE status = 'active'"

# Option B (rewritten)
explain "SELECT * FROM users WHERE deleted_at IS NULL AND status = 'active'"

Pick query with better plan.

Analyze Hot Paths

For critical queries, analyze under load:

-- Run multiple times to warm cache
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- Check consistency of execution time

Export Plan for Analysis

psql "$SUPABASE_DB_URL" -qAt -c \
"EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ..." \
> query_plan.json

Upload to: https://explain.depesz.com or https://explain.dalibo.com

Response Time Goals

Interactive queries*: < 100ms
Reports*: < 1s
Batch/Background*: < 5s

If slower:*

Check for sequential scans
Add/optimize indexes
Consider caching
Optimize RLS policies

Cache Hit Ratio

Goal*: > 95% shared hits

-- Check overall cache hit ratio
SELECT 
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

If low:*

Increase shared_buffers (DBA task)
Query optimization needed
Consider query pattern changes

When to Use EXPLAIN

Always:*

New query in production code
After schema changes
When adding indexes
RLS policy changes

Reactive:*

Slow query reports
Performance degradation
High database load
Before optimization attempts

Never:*

For queries already known to be fast
On queries with no data yet (stats unreliable)
Without ANALYZE if you need actual timing

EXPLAIN ANALYZE Runs Query

⚠️ Warning*: ANALYZE actually executes query

Safe:*

SELECT queries
Read-only queries

Dangerous:*

INSERT/UPDATE/DELETE (use transaction + rollback)
Queries with side effects

-- Safe way to EXPLAIN write queries
BEGIN;
EXPLAIN ANALYZE DELETE FROM ...;
ROLLBACK;  -- Undo changes

Statistics May Be Stale

Plans based on table statistics:

Updated by VACUUM/ANALYZE
May not reflect current data
Run ANALYZE if estimates way off

Plan Can Change

Plans vary based on:

Data distribution
Table size
Server configuration
Cache state
Time of day (load)

Integration with Workflow

Query optimization workflow:

Find slow query (logs, monitoring)
explain "SELECT ..." - Baseline
Analyze plan (sequential scans? missing indexes?)
Hypothesize fix
Apply fix in dev
explain "SELECT ..." - Verify improvement
Test with real data volume
Deploy to production
Monitor actual performance

Resources

Visualization Tools:*

Documentation:*

PostgreSQL EXPLAIN: https://www.postgresql.org/docs/current/sql-explain.html
Using EXPLAIN: https://www.postgresql.org/docs/current/using-explain.html

Related Commands:*

analyze-hotpaths - Check common query patterns
design-indexes - Plan index strategy
rls-audit - Check RLS policy performance