EXPLAIN Query Plans

The EXPLAIN command shows the query execution plan for a SELECT statement, including cost estimates, index usage, and execution strategy. Use this to understand and optimize query performance.

Table of Contents

EXPLAIN

Shows the query execution plan for a SELECT statement, including cost estimates, index usage, and execution strategy. Use this to understand and optimize query performance.

Syntax

-- Basic query plan analysis
EXPLAIN <SELECT_statement>;

-- Execute query and include actual runtime metrics
EXPLAIN ANALYZE <SELECT_statement>;

Components

Component Description Required
SELECT_statement Any valid SELECT query Yes
ANALYZE Execute query and include actual metrics No

Output Format

Returns a JSON structure containing:

Field Type Description
QueryType string Type of query (SELECT)
Query string Original query string
PlanType string Human-readable plan description
Cost float64 Estimated query cost
EstimatedRows int Estimated number of rows returned
IndexesUsed []string List of indexes utilized
ExecutionTree object Hierarchical execution plan
CostFormulas object Cost calculation formulas (if available)

Examples

Basic EXPLAIN

-- Analyze a simple query
EXPLAIN SELECT * FROM "Authors" WHERE "Name" == "Strohschein";

Response:

{
  "QueryPlan": {
    "QueryType": "SELECT",
    "Query": "SELECT * FROM \"Authors\" WHERE \"Name\" == \"Strohschein\"",
    "PlanType": "IndexScan(Name) -> Filter",
    "Cost": 7.0722,
    "EstimatedRows": 3,
    "IndexesUsed": ["authors_name_hash_idx"],
    "ExecutionTree": {
      "NodeType": "FilterNode",
      "Predicate": "Name == Strohschein",
      "Cost": 7.0722,
      "Child": {
        "NodeType": "IndexScanNode",
        "IndexName": "authors_name_hash_idx",
        "ScanType": "hash_lookup",
        "Cost": 5.2
      }
    }
  }
}

EXPLAIN ANALYZE

-- Execute query and get actual runtime metrics
EXPLAIN ANALYZE SELECT * FROM "Authors" WHERE "Country" == "USA";

Response:

{
  "QueryPlan": {
    "QueryType": "SELECT",
    "Query": "SELECT * FROM \"Authors\" WHERE \"Country\" == \"USA\"",
    "PlanType": "FullScan -> Filter",
    "Cost": 125.4,
    "EstimatedRows": 15,
    "IndexesUsed": [],
    "ExecutionTree": {
      "NodeType": "FilterNode",
      "Predicate": "Country == USA",
      "Cost": 125.4,
      "Child": {
        "NodeType": "FullScanNode",
        "BundleName": "Authors",
        "Cost": 100.0
      }
    },
    "Executed": true,
    "ExecutionTimeMs": 12.3
  }
}

Complex Query Analysis

-- Analyze complex query with multiple operations
EXPLAIN SELECT * FROM "Authors"
WHERE "BirthYear" >= 1970
ORDER BY "Name" ASC
LIMIT 10;

Response:

{
  "QueryPlan": {
    "QueryType": "SELECT",
    "Query": "SELECT * FROM \"Authors\" WHERE \"BirthYear\" >= 1970 ORDER BY \"Name\" ASC LIMIT 10",
    "PlanType": "FullScan -> Filter -> Sort -> Limit",
    "Cost": 284.5,
    "EstimatedRows": 10,
    "IndexesUsed": [],
    "ExecutionTree": {
      "NodeType": "LimitNode",
      "Limit": 10,
      "Cost": 284.5,
      "Child": {
        "NodeType": "SortNode",
        "OrderBy": [{"Field": "Name", "Direction": "ASC"}],
        "Cost": 280.0,
        "Child": {
          "NodeType": "FilterNode",
          "Predicate": "BirthYear >= 1970",
          "Cost": 150.0,
          "Child": {
            "NodeType": "FullScanNode",
            "BundleName": "Authors",
            "Cost": 100.0
          }
        }
      }
    }
  }
}

JOIN Query Analysis

-- Analyze JOIN query
EXPLAIN SELECT * FROM "Authors"
JOIN "Books" ON "Authors"."ID" == "Books"."AuthorID";

Response:

{
  "QueryPlan": {
    "QueryType": "SELECT",
    "Query": "SELECT * FROM \"Authors\" JOIN \"Books\" ON \"Authors\".\"ID\" == \"Books\".\"AuthorID\"",
    "PlanType": "HashJoin",
    "Cost": 450.0,
    "EstimatedRows": 250,
    "IndexesUsed": ["authors_id_idx", "books_authorid_idx"],
    "ExecutionTree": {
      "NodeType": "HashJoinNode",
      "JoinType": "INNER",
      "LeftChild": {
        "NodeType": "IndexScanNode",
        "BundleName": "Authors",
        "IndexName": "authors_id_idx"
      },
      "RightChild": {
        "NodeType": "IndexScanNode",
        "BundleName": "Books",
        "IndexName": "books_authorid_idx"
      }
    }
  }
}

GROUP BY Analysis

-- Analyze aggregation query
EXPLAIN SELECT "Country", COUNT(*) FROM "Authors" GROUP BY "Country";

Response:

{
  "QueryPlan": {
    "QueryType": "SELECT",
    "Query": "SELECT \"Country\", COUNT(*) FROM \"Authors\" GROUP BY \"Country\"",
    "PlanType": "Aggregation",
    "Cost": 180.0,
    "EstimatedRows": 25,
    "IndexesUsed": [],
    "ExecutionTree": {
      "NodeType": "AggregationNode",
      "GroupBy": ["Country"],
      "Aggregates": [
        {
          "Function": "COUNT",
          "Field": "*",
          "Alias": "COUNT(*)"
        }
      ],
      "Cost": 180.0,
      "Child": {
        "NodeType": "FullScanNode",
        "BundleName": "Authors",
        "Cost": 100.0
      }
    }
  }
}

Execution Node Types

The ExecutionTree contains one or more of these node types:

Node Type Description Key Fields
IndexScanNode Uses an index to scan documents IndexName, ScanType
FullScanNode Scans entire bundle BundleName
FilterNode Applies WHERE clause filter Predicate, Child
SortNode Sorts results OrderBy, Child
LimitNode Limits number of results Limit, Offset, Child
AggregationNode GROUP BY aggregation GroupBy, Aggregates, Child
DistinctNode Removes duplicates Child
UnionNode Combines multiple queries Children
JoinExecutionNode Executes JOIN operation JoinType, FromBundle
NestedLoopJoinNode Nested loop JOIN algorithm LeftChild, RightChild
HashJoinNode Hash JOIN algorithm LeftChild, RightChild
MergeJoinNode Merge JOIN algorithm LeftChild, RightChild

Cost Model

Query costs are estimated based on:

Operation Cost Factor Formula
Full Scan Document count documents x 1.0
Index Scan Selectivity documents x 0.1
Hash Lookup Near constant 5.0 + results x 0.1
Filter Input rows input_rows x 0.5
Sort N log N rows x log(rows) x 0.01
Hash Join Build + probe left_rows + right_rows
Nested Loop Cartesian left_rows x right_rows x 0.1
Aggregation Grouping overhead rows x 0.8

Performance Analysis Tips

1. Check Index Usage

EXPLAIN SELECT * FROM "Users" WHERE "email" == "user@example.com";
-- Look for: "IndexesUsed": ["users_email_idx"]
-- If empty [], consider creating an index

2. Compare Costs

-- Without index (high cost)
EXPLAIN SELECT * FROM "Users" WHERE "age" > 30;
-- Cost: 5000 (FullScan)

-- Create index
CREATE INDEX "users_age_btree" ON "Users" ("age") USING BTREE;

-- With index (lower cost)
EXPLAIN SELECT * FROM "Users" WHERE "age" > 30;
-- Cost: 250 (IndexScan)

3. Analyze JOIN Performance

-- Check JOIN algorithm selection
EXPLAIN SELECT * FROM "Orders" JOIN "Customers" ON "Orders"."CustomerID" == "Customers"."ID";

-- Look for:
-- - HashJoinNode (good for large datasets)
-- - NestedLoopJoinNode (warning: may be slow)
-- - IndexesUsed (should list indexes on join columns)

4. Verify LIMIT Optimization

-- Good: LIMIT applied early
EXPLAIN SELECT * FROM "Products" ORDER BY "price" DESC LIMIT 10;
-- PlanType should end with "-> Limit"
-- Cost should be reasonable

-- Bad: Large sort before LIMIT
-- If cost is very high, consider adding index on sort field

5. Validate Execution with ANALYZE

-- Get actual vs estimated comparison
EXPLAIN ANALYZE SELECT * FROM "Logs" WHERE "timestamp" > '2024-01-01';

-- Compare:
-- - EstimatedRows vs actual row count
-- - Estimated cost vs ExecutionTimeMs
-- - Verify index usage in practice

Use Cases

Scenario Use EXPLAIN To
Slow Queries Identify missing indexes or inefficient plans
Index Design Verify indexes are being used
Query Optimization Compare different query formulations
Capacity Planning Estimate query costs for scaling
Debugging Understand unexpected query behavior
Performance Tuning Find bottlenecks in complex queries

Limitations

Limitation Description Workaround
SELECT Only Currently only supports SELECT statements UPDATE/DELETE support planned
Cost Estimates Estimates may not match actual performance Use EXPLAIN ANALYZE for real metrics
Node Timing ANALYZE doesn't track per-node timing yet Monitor overall execution time
Cache Effects Doesn't account for caching Run multiple times to see cache impact

Best Practices

DO:

  • Use EXPLAIN during development to validate queries
  • Run EXPLAIN ANALYZE on production-like data volumes
  • Check index usage before deploying new queries
  • Compare costs when choosing between query approaches
  • Monitor slow queries with EXPLAIN in production
  • Document expected costs for critical queries

DON'T:

  • Don't rely solely on cost estimates - use ANALYZE
  • Don't run EXPLAIN ANALYZE on huge datasets in production
  • Don't ignore index recommendations
  • Don't optimize prematurely - measure first

Complete Example Workflow

-- 1. Initial query (slow)
SELECT * FROM "Orders" WHERE "status" == "pending" AND "amount" > 1000;

-- 2. Analyze current performance
EXPLAIN ANALYZE SELECT * FROM "Orders" WHERE "status" == "pending" AND "amount" > 1000;
-- Response shows:
--   PlanType: "FullScan -> Filter"
--   Cost: 25000
--   ExecutionTimeMs: 450
--   IndexesUsed: []

-- 3. Create index on status field
CREATE INDEX "orders_status_idx" ON "Orders" ("status") USING HASH;

-- 4. Verify improvement
EXPLAIN ANALYZE SELECT * FROM "Orders" WHERE "status" == "pending" AND "amount" > 1000;
-- Response now shows:
--   PlanType: "IndexScan(status) -> Filter"
--   Cost: 1200
--   ExecutionTimeMs: 45
--   IndexesUsed: ["orders_status_idx"]

-- 5. Further optimization - add compound index
CREATE INDEX "orders_status_amount_idx" ON "Orders" ("status", "amount") USING BTREE;

-- 6. Final verification
EXPLAIN ANALYZE SELECT * FROM "Orders" WHERE "status" == "pending" AND "amount" > 1000;
-- Response:
--   PlanType: "IndexScan(status,amount)"
--   Cost: 150
--   ExecutionTimeMs: 8
--   IndexesUsed: ["orders_status_amount_idx"]

Last updated: March 2026