Definition

A query plan is the internal execution strategy that a database engine generates when processing a query. It describes the step-by-step operations the engine will perform -- which indexes to use, which join algorithms to apply, how to sort results, and in what order to access tables. The query planner (also called the query optimizer) evaluates multiple possible plans and selects the one with the lowest estimated cost based on table statistics, available indexes, and data distribution.

In PostgreSQL, which powers the Prismatic Platform's persistent storage, the query planner uses a cost-based optimization approach. It assigns cost estimates to each possible operation (sequential scans, index scans, hash joins, merge joins, nested loops) and constructs a plan tree that minimizes total execution cost. The EXPLAIN and EXPLAIN ANALYZE commands expose these plans for developer inspection.

Understanding query plans is not merely an academic exercise -- it is a core operational skill for anyone running production PostgreSQL workloads. A single poorly-planned query can dominate database CPU, block other transactions through lock contention, and cascade into application-level timeouts. The Prismatic Platform's DD pipeline, OSINT execution history, and glossary search all rely on efficient query execution across tables with millions of rows.

Core Concepts

EXPLAIN vs EXPLAIN ANALYZE

The two primary introspection commands serve different purposes:

Feature	`EXPLAIN`	`EXPLAIN ANALYZE`
Executes query	No	Yes
Shows estimated costs	Yes	Yes
Shows actual row counts	No	Yes
Shows actual timing	No	Yes
Shows buffer usage	No	With `BUFFERS` option
Safe for mutations	Yes (never executes)	No (wraps in transaction + rollback for safety)
Use case	Quick plan inspection	Diagnosing planner estimation errors

The most common pitfall is relying solely on EXPLAIN without ANALYZE. The planner's row count estimates can be wildly wrong -- predicting 10 rows when the actual result is 100,000 -- leading to catastrophic plan choices. EXPLAIN ANALYZE reveals these discrepancies by executing the query and reporting both estimated and actual values.

Plan Node Types

PostgreSQL builds query plans as trees of operation nodes. Each node type has specific performance characteristics:

Node Type	Description	Cost Profile	When Used
Seq Scan	Full table scan, reads every row	O(n)	No useful index, small table, or high selectivity
Index Scan	B-tree lookup + heap fetch	O(log n + k)	Selective predicate with matching index
Index Only Scan	Reads from index without heap access	O(log n + k)	All needed columns in index, visibility map current
Bitmap Index Scan	Builds bitmap from index, then heap scan	O(log n + k)	Medium selectivity, multiple indexes to combine
Bitmap Heap Scan	Reads heap pages identified by bitmap	O(pages)	Always paired with Bitmap Index Scan
Hash Join	Builds hash table for inner relation	O(n + m)	Equi-joins, inner relation fits in work_mem
Merge Join	Merges two sorted streams	O(n log n + m log m)	Pre-sorted inputs or when sort cost is low
Nested Loop	For each outer row, probe inner	O(n * m) worst case	Small outer relation or indexed inner relation
Sort	In-memory or disk sort	O(n log n)	ORDER BY, merge join input, DISTINCT
HashAggregate	Hash-based grouping	O(n)	GROUP BY with moderate cardinality
GroupAggregate	Stream-based grouping on sorted input	O(n)	GROUP BY on pre-sorted data
Materialize	Caches subplan results in memory	O(n)	Avoids re-executing expensive subplans
CTE Scan	Scans materialized CTE	O(n)	WITH queries (pre-PG12: always materialized)
Gather	Collects results from parallel workers	O(n / workers)	Parallel query execution

Join Algorithm Comparison

Choosing the right join algorithm is one of the planner's most impactful decisions:

Algorithm	Best When	Memory	CPU	Sorted Output	Index Needed
Nested Loop	Small outer + indexed inner	Low	High for large sets	No	Inner index critical
Hash Join	Equi-joins, one side fits in memory	High (hash table)	Linear	No	No
Merge Join	Both sides sorted or sort is cheap	Low	n log n + merge	Yes	Optional (if pre-sorted)

Index Types in PostgreSQL

Index Type	Use Case	Supported Operators	Size	Update Cost
B-tree	Equality, range, ordering	`=`, `<`, `>`, `<=`, `>=`, `BETWEEN`, `IN`	Moderate	Low
Hash	Equality only	`=`	Small	Low
GIN	Full-text search, arrays, JSONB	`@>`, `<@`, `&&`, `@@`	Large	High
GiST	Geometric, range, proximity	`<<`, `>>`, `@>`, `<@`, `&&`	Moderate	Moderate
BRIN	Physically sorted data (timestamps)	Range operators	Very small	Very low
SP-GiST	Non-balanced tree structures	Varies by operator class	Moderate	Moderate

Technical Deep Dive

PostgreSQL Planner Architecture

PostgreSQL's query planner operates in several phases. First, the parser converts SQL text into a parse tree. The rewriter applies any rules (views, row-level security). The planner then generates candidate plans and estimates their costs using statistics from pg_statistic (populated by ANALYZE or VACUUM ANALYZE).

The cost model uses two primary components: startup cost (time before the first row is produced) and total cost (time to produce all rows). These are expressed in arbitrary cost units calibrated by parameters like seq_page_cost (1.0), random_page_cost (4.0), and cpu_tuple_cost (0.01). The ratio between sequential and random I/O costs reflects the mechanical reality of spinning disks -- on SSDs, reducing random_page_cost to 1.1-1.5 better reflects actual performance.

For complex queries, PostgreSQL uses a genetic query optimizer (GEQO) when the number of tables exceeds geqo_threshold (default 12). GEQO uses a genetic algorithm to search the join-order space because exhaustive enumeration of all possible orderings is factorial -- 12 tables produce 479,001,600 possible orderings.

CTE Optimization and Inlining

Prior to PostgreSQL 12, Common Table Expressions (CTEs) were always materialized -- the database executed the CTE once and stored the results, preventing the planner from pushing predicates into the CTE. This was an optimization fence that could cause severe performance problems:

-- Pre-PG12: planner cannot push the WHERE into the CTE
WITH recent_entities AS (
  SELECT * FROM dd_entities
)
SELECT * FROM recent_entities WHERE loaded_at > NOW() - INTERVAL '1 day';
-- Would scan ALL dd_entities, then filter

PostgreSQL 12+ inlines CTEs by default when they are referenced once and are not recursive. Use MATERIALIZED or NOT MATERIALIZED hints to control this behavior explicitly:

-- Force materialization (useful when CTE is referenced multiple times)
WITH recent_entities AS MATERIALIZED (
  SELECT * FROM dd_entities WHERE loaded_at > NOW() - INTERVAL '1 day'
)
SELECT * FROM recent_entities re
JOIN dd_relationships dr ON dr.source_entity_id = re.id;

Statistics and Estimation

The planner's accuracy depends entirely on the quality of statistics in pg_statistic. Key statistics include:

n_distinct: Number of distinct values in a column
most_common_vals / most_common_freqs: The most frequent values and their frequencies
histogram_bounds: Equally-populated histogram buckets for range estimation
correlation: Physical ordering correlation (high = BRIN-friendly)

The default statistics target is 100 histogram buckets. For columns with skewed distributions (common in DD entity types), increasing this improves estimates:

ALTER TABLE dd_entities ALTER COLUMN entity_type SET STATISTICS 500;
ANALYZE dd_entities;

Parallel Query Execution

PostgreSQL can split sequential scans, aggregations, and joins across multiple workers. The plan shows Gather or Gather Merge nodes that collect results from workers. Key parameters:

max_parallel_workers_per_gather (default 2): Workers per query
min_parallel_table_scan_size (default 8MB): Minimum table size for parallel scan
parallel_tuple_cost (default 0.1): Cost penalty for inter-process communication

Advanced Topics

Plan Instability and Tipping Points

Query plans can change abruptly when table sizes cross thresholds. A query that used an index scan on 10,000 rows may switch to a sequential scan at 100,000 rows because the planner estimates that reading the entire table sequentially is cheaper than the random I/O pattern of index lookups on a large fraction of rows.

This tipping point behavior means that performance testing on small datasets does not predict production behavior. Always test with production-scale data volumes or use pg_hint_plan to force specific plans during development.

Join Order and Cardinality Estimation Errors

The planner's biggest weakness is multi-table join cardinality estimation. When predicates on different tables are correlated (e.g., country = 'CZ' AND entity_type = 'company'), the planner assumes independence and multiplies selectivities, potentially underestimating result sizes by orders of magnitude. PostgreSQL 14+ extended statistics (CREATE STATISTICS) can capture cross-column correlations:

CREATE STATISTICS dd_entity_type_country (dependencies, ndistinct, mcv)
ON entity_type, country FROM dd_entities;
ANALYZE dd_entities;

Prepared Statement Plan Caching

PostgreSQL caches plans for prepared statements. After five executions with consistent plan choices, the planner switches from custom plans (re-planned each time) to a generic plan (cached). This can cause performance regressions if the generic plan is suboptimal for specific parameter values. Monitor with pg_stat_statements and consider DEALLOCATE for problematic prepared statements.

Usage in Prismatic Platform

Query plan analysis is critical in several Prismatic Platform subsystems. The DD pipeline's entity queries join multiple tables (dd_entities, dd_relationships, dd_entity_attributes) and benefit from proper indexing on source_slug, entity_type, and loaded_at columns. The OSINT execution history queries filter by tool slug and time range, requiring composite indexes.

The glossary search system uses Meilisearch for full-text queries but falls back to PostgreSQL ILIKE queries for structured filtering. These fallback queries require careful indexing with pg_trgm GIN indexes to avoid sequential scans on the glossary content table.

Performance monitoring uses EXPLAIN ANALYZE output to detect regression -- when a previously fast query switches from an index scan to a sequential scan due to stale statistics or data growth, the platform's quality gates flag the degradation.

The platform's PERF doctrine enforces that no Ecto query may use unbounded Repo.all/1 without a limit clause. This rule exists because the planner may choose an efficient plan for 1,000 rows but a catastrophic one for 1,000,000 rows when no limit constrains the result set.

Code Examples

Query Plan Analyzer

defmodule PrismaticStorage.QueryAnalyzer do
  @moduledoc """
  Analyzes query execution plans for performance monitoring.
  Wraps PostgreSQL EXPLAIN ANALYZE for Ecto queries and detects
  common anti-patterns like sequential scans on large tables,
  nested loops with large outer relations, and stale statistics.
  """

  alias PrismaticDd.Repo

  require Logger

  @type plan_analysis :: %{
    plan_text: String.t(),
    total_cost: float(),
    seq_scans: list(String.t()),
    nested_loops: list(String.t()),
    actual_vs_estimated_ratio: float() | nil
  }

  @spec explain(Ecto.Query.t(), keyword()) :: {:ok, String.t()} | {:error, term()}
  def explain(query, opts \\ []) do
    analyze = Keyword.get(opts, :analyze, true)
    format = Keyword.get(opts, :format, :text)
    buffers = Keyword.get(opts, :buffers, false)

    explain_opts = [analyze: analyze, format: format, buffers: buffers]

    case Ecto.Adapters.SQL.explain(Repo, :all, query, explain_opts) do
      result when is_binary(result) -> {:ok, result}
      error -> {:error, error}
    end
  rescue
    e in Postgrex.Error ->
      Logger.warning("Query explain failed: #{Exception.message(e)}")
      {:error, {:postgrex_error, Exception.message(e)}}
  end

  @spec analyze_plan(String.t()) :: plan_analysis()
  def analyze_plan(plan_text) do
    %{
      plan_text: plan_text,
      total_cost: extract_total_cost(plan_text),
      seq_scans: detect_seq_scans(plan_text),
      nested_loops: detect_nested_loops(plan_text),
      actual_vs_estimated_ratio: compute_estimation_accuracy(plan_text)
    }
  end

  @spec detect_seq_scans(String.t()) :: list(String.t())
  def detect_seq_scans(plan_text) do
    plan_text
    |> String.split("\n")
    |> Enum.filter(&String.contains?(&1, "Seq Scan"))
    |> Enum.map(&String.trim/1)
  end

  @spec detect_nested_loops(String.t()) :: list(String.t())
  def detect_nested_loops(plan_text) do
    plan_text
    |> String.split("\n")
    |> Enum.filter(&String.contains?(&1, "Nested Loop"))
    |> Enum.map(&String.trim/1)
  end

  @spec extract_total_cost(String.t()) :: float()
  defp extract_total_cost(plan_text) do
    case Regex.run(~r/cost=[\d.]+\.\.([\d.]+)/, plan_text) do
      [_, cost] -> String.to_float(cost)
      _ -> 0.0
    end
  end

  @spec compute_estimation_accuracy(String.t()) :: float() | nil
  defp compute_estimation_accuracy(plan_text) do
    with [_, estimated] <- Regex.run(~r/rows=(\d+)/, plan_text),
         [_, actual] <- Regex.run(~r/actual .+ rows=(\d+)/, plan_text) do
      est = String.to_integer(estimated)
      act = String.to_integer(actual)
      if est > 0, do: act / est, else: nil
    else
      _ -> nil
    end
  end
end

Ecto Query with Plan-Aware Patterns

defmodule PrismaticDd.EntityQueries do
  @moduledoc """
  Plan-optimized entity queries for the DD pipeline.
  Follows PERF doctrine: all queries bounded, indexes verified.
  """

  import Ecto.Query

  alias PrismaticDd.{Entity, Relationship, Repo}

  @max_results 1_000

  @doc """
  Fetches entities by source with bounded results.
  Uses composite index on (source_slug, loaded_at DESC).

  ## Examples

      iex> EntityQueries.by_source("ares", limit: 50)
      {:ok, [%Entity{}, ...]}

  """
  @spec by_source(String.t(), keyword()) :: {:ok, list(Entity.t())}
  def by_source(source_slug, opts \\ []) do
    limit = min(Keyword.get(opts, :limit, 100), @max_results)

    entities =
      from(e in Entity,
        where: e.source_slug == ^source_slug,
        order_by: [desc: e.loaded_at],
        limit: ^limit
      )
      |> Repo.all()

    {:ok, entities}
  end

  @doc """
  Fetches entities with relationships in a single query.
  Avoids N+1 by using a preload join instead of separate queries.
  """
  @spec with_relationships(String.t(), keyword()) :: {:ok, list(Entity.t())}
  def with_relationships(source_slug, opts \\ []) do
    limit = min(Keyword.get(opts, :limit, 100), @max_results)

    entities =
      from(e in Entity,
        where: e.source_slug == ^source_slug,
        left_join: r in Relationship,
        on: r.source_entity_id == e.id,
        preload: [relationships: r],
        order_by: [desc: e.loaded_at],
        limit: ^limit
      )
      |> Repo.all()

    {:ok, entities}
  end
end

Automated Plan Regression Detection

defmodule PrismaticStorage.PlanMonitor do
  @moduledoc """
  Monitors query plan changes and detects regressions.
  Compares current plans against baseline plans stored in ETS.
  Emits telemetry events when plan structure changes.
  """

  require Logger

  @spec check_for_regression(String.t(), String.t()) :: :ok | {:regression, map()}
  def check_for_regression(query_name, current_plan) do
    analysis = PrismaticStorage.QueryAnalyzer.analyze_plan(current_plan)

    cond do
      length(analysis.seq_scans) > 0 ->
        Logger.warning("Plan regression: #{query_name} has sequential scans",
          query: query_name,
          seq_scans: analysis.seq_scans
        )

        :telemetry.execute(
          [:prismatic, :storage, :plan_regression],
          %{count: 1},
          %{query: query_name, type: :seq_scan}
        )

        {:regression, %{type: :seq_scan, details: analysis.seq_scans}}

      analysis.actual_vs_estimated_ratio != nil and
          analysis.actual_vs_estimated_ratio > 10.0 ->
        Logger.warning("Plan estimation error: #{query_name} ratio=#{analysis.actual_vs_estimated_ratio}",
          query: query_name
        )

        {:regression, %{type: :estimation_error, ratio: analysis.actual_vs_estimated_ratio}}

      true ->
        :ok
    end
  end
end

Common Pitfalls

Pitfall	Problem	Solution
Stale statistics	Planner makes wrong estimates after bulk inserts	Run `VACUUM ANALYZE` after bulk loads; tune `autovacuum_analyze_threshold`
Missing composite index	Query filters on (A, B) but only index on A	Create composite index matching query predicates in order
Wrong column order in index	Index on (B, A) when queries filter on A first	Match index column order to query predicate order
Low `work_mem`	Hash joins spill to disk, merge sorts use temp files	Increase `work_mem` per-session for analytical queries
LIKE without trigram index	`WHERE name LIKE '%search%'` forces sequential scan	Create GIN index with `pg_trgm` extension
Implicit casts	`WHERE varchar_col = 42` prevents index use	Ensure parameter types match column types
Over-indexing	Too many indexes slow writes and increase WAL volume	Remove unused indexes (check `pg_stat_user_indexes`)
Correlated subqueries	Subquery executes once per outer row	Rewrite as JOIN or lateral join
CTE optimization fence	CTE prevents predicate pushdown (pre-PG12)	Use subquery or `NOT MATERIALIZED` hint
Function volatility	`WHERE col = my_function()` called per row if VOLATILE	Mark function as IMMUTABLE or STABLE when appropriate

Best Practices

Run EXPLAIN ANALYZE on slow queries -- the ANALYZE option executes the query and shows actual vs. estimated row counts, revealing planner miscalculations.
Keep statistics current -- run VACUUM ANALYZE regularly or configure autovacuum aggressively for high-churn tables like dd_fetch_records. Consider increasing default_statistics_target for columns with skewed distributions.
Watch for sequential scans on large tables -- a Seq Scan on a million-row table indicates a missing index or a non-sargable predicate (function applied to indexed column).
Check for nested loop joins with large outer relations -- these produce O(n*m) behavior and often indicate a missing index on the join column.
Use pg_stat_statements -- track query performance over time to catch plan regressions before they impact users. Monitor mean_exec_time, stddev_exec_time, and calls.
Use EXPLAIN (BUFFERS, ANALYZE) -- buffer statistics reveal whether queries hit shared buffers (cache) or require disk reads, which is critical for understanding I/O-bound queries.
Create covering indexes for hot queries -- include all SELECT columns in the index via INCLUDE to enable Index Only Scans that avoid heap fetches entirely.
Bound all queries with LIMIT -- follows the PERF doctrine. Even if the application logic limits results, explicit LIMIT helps the planner choose more efficient plans.
Monitor plan stability -- use auto_explain with log_min_duration to automatically log plans for slow queries in production without manual EXPLAIN.
Test with production-scale data -- plans that work on 1,000 rows often break on 1,000,000 rows due to planner tipping points between scan strategies.

Query -- the request that triggers plan generation
Sequential Scan -- full-table scan, often a sign of missing indexes
Vacuum -- maintenance operation that updates planner statistics
Statistics -- data distribution information used by the planner
Schema -- structural definitions that determine available indexes
Ecto -- Elixir database wrapper that generates SQL from query DSL
Index -- data structure enabling efficient lookups
Transaction -- isolation context affecting plan visibility
Connection Pool -- resource management for database connections
N Plus One -- anti-pattern where separate queries replace joins
Materialized View -- pre-computed query results for expensive plans
CTE -- Common Table Expressions and their planning implications

Connect & Contribute

Created by Tomas Korcak (korczis) | Open Source under GHL

GitHub | GitLab | LinkedIn | Contact
Developer Portal | Architecture | Meet the Creator

Query Plan