0Pricing
PostgreSQL Performance & Query Optimization · レッスン

プラン作成時と実行時のパーティションプルーニング

EXPLAINの出力を読み、静的および実行時のプルーニングによってクエリから不要なパーティションが除外されていることを確認します。

「プラン作成時と実行時のパーティションプルーニング」はCoddyKit上の無料PostgreSQL Performance & Query Optimizationレッスンです。 これはレッスン2/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはPostgreSQL Performance & Query Optimization学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

このレッスンの一部はまだ翻訳されておらず、英語で表示されています。

Why Partition Pruning Matters

You partitioned a huge table so PostgreSQL can skip partitions that cannot contain matching rows. That skipping is called partition pruning.

Without pruning, a query that touches one month of data could still scan every partition for every month. The whole performance benefit of partitioning depends on the planner (and sometimes the executor) recognising which partitions are relevant.

  • Plan-time pruning happens when the planner already knows the filter values.
  • Execution-time pruning happens when the values are only known once the query runs.

In this lesson you will learn to confirm both kinds by reading EXPLAIN output.

Our Example Table

Throughout the lesson we use a range-partitioned events table, partitioned by month on created_at. Each child partition holds one month of rows.

This is the classic time-series layout where pruning pays off the most: queries usually target a narrow date range, so most partitions should be skipped entirely.

CREATE TABLE events (
    id          bigint        NOT NULL,
    created_at  timestamptz   NOT NULL,
    user_id     bigint        NOT NULL,
    payload     jsonb
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_01 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE events_2024_03 PARTITION OF events
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

Static Pruning at Plan Time

When the filter compares the partition key to a constant, the planner can decide which partitions to touch before execution. This is static (plan-time) pruning.

Run EXPLAIN on a query restricted to one month. The plan should reference only the matching partition(s); the others never appear.

The setting enable_partition_pruning (on by default) controls this behaviour.

EXPLAIN
SELECT count(*)
FROM events
WHERE created_at >= '2024-02-10'
  AND created_at <  '2024-02-20';

Reading the Pruned Plan

Here is the shape of the plan for that single-month query. Notice only events_2024_02 is scanned. events_2024_01 and events_2024_03 are absent from the plan entirely.

  • The Append (or Seq Scan with one child) lists only surviving partitions.
  • Pruned partitions leave no trace in the plan output.

This is the clearest confirmation of static pruning: count the partitions in the plan and compare against how many exist.

Aggregate
  ->  Seq Scan on events_2024_02 events
        Filter: ((created_at >= '2024-02-10'::timestamptz)
             AND (created_at <  '2024-02-20'::timestamptz))

When the Plan Still Lists Every Partition

If your EXPLAIN shows an Append over all partitions, static pruning did not apply. Common causes:

  • The filter does not reference the partition key (e.g. filtering on user_id only).
  • A function wraps the key, e.g. date(created_at) = '2024-02-10', hiding the relationship from the planner.
  • The value is not a constant at plan time (parameter or join column) — that needs execution-time pruning.

Keep the partition key bare on one side of the comparison so the planner can match it to partition bounds.

-- This DEFEATS static pruning: function wraps the key
EXPLAIN SELECT count(*) FROM events
WHERE date(created_at) = '2024-02-15';

-- This ENABLES it: bare key compared to constants
EXPLAIN SELECT count(*) FROM events
WHERE created_at >= '2024-02-15'
  AND created_at <  '2024-02-16';

Why Parameters Need a Different Approach

With a prepared statement or a value supplied at run time, the planner may not know the constant when it builds the plan. A generic plan must stay valid for any parameter value, so it cannot statically prune.

Instead PostgreSQL defers the decision: it keeps all partitions in the plan but adds the ability to skip them while the query executes. This is execution-time (runtime) pruning.

PREPARE month_count(timestamptz, timestamptz) AS
  SELECT count(*) FROM events
  WHERE created_at >= $1 AND created_at < $2;

EXPLAIN EXECUTE month_count('2024-03-01', '2024-04-01');

Spotting Execution-Time Pruning in the Plan

Runtime pruning advertises itself in EXPLAIN with two key markers under an Append node:

  • Subplans Removed: N — partitions discarded before scanning.
  • For parameterised plans, a line like Filter or initplan parameters that drive the pruning.

If you see Subplans Removed, the executor pruned partitions at run time. If you see neither that nor a reduced partition list, no pruning occurred.

Aggregate
  ->  Append
        Subplans Removed: 2
        ->  Seq Scan on events_2024_03 events_1
              Filter: ((created_at >= $1) AND (created_at < $2))

Runtime Pruning from Nested Loop Joins

Execution-time pruning is not only for parameters. It also kicks in when the partition key is compared to a value produced by the outer side of a join (a Nested Loop), or by a subquery.

Each outer row supplies a key value, and for each one the executor prunes down to the relevant partition. This is extremely valuable for selective lookups against a partitioned fact table.

EXPLAIN (ANALYZE, COSTS OFF)
SELECT e.*
FROM date_filter df
JOIN events e
  ON e.created_at >= df.start_ts
 AND e.created_at <  df.end_ts;

Use EXPLAIN ANALYZE to Confirm It Actually Ran

Plain EXPLAIN shows what could be pruned. To prove pruning happened during a real run — especially runtime pruning — use EXPLAIN ANALYZE.

  • Subplans Removed: N appears with the actual number removed at execution.
  • Surviving partitions show actual rows; pruned ones show (never executed) if they remain as subnodes.

Reading actual time and actual rows per partition tells you exactly which children did work.

EXPLAIN (ANALYZE, BUFFERS)
EXECUTE month_count('2024-03-01', '2024-04-01');

Pruning Is Not the Same as Constraint Exclusion

Older PostgreSQL relied on constraint_exclusion for inheritance-based partitioning. Modern declarative partitioning uses partition pruning, which is faster and supports runtime pruning.

  • enable_partition_pruning = on drives the new mechanism (plan and execution time).
  • constraint_exclusion only ever worked at plan time and only with CHECK constraints.

For declarative partitions, leave enable_partition_pruning on and do not depend on constraint_exclusion.

SHOW enable_partition_pruning;   -- expect: on
SHOW constraint_exclusion;        -- 'partition' (legacy default)

A Practical Checklist

When verifying pruning on a real query, work through this list:

  • Is the partition key in the predicate, bare and SARGable? No wrapping functions, no implicit casts that block matching.
  • Static case: run EXPLAIN — do pruned partitions disappear from the plan?
  • Runtime case: look for Subplans Removed: N under Append.
  • Confirm with EXPLAIN ANALYZE that only the expected partitions did work.
  • Check the setting if nothing prunes: enable_partition_pruning must be on.

Quick Check

A prepared statement filters a range-partitioned table on its partition key, using a bound parameter. You run EXPLAIN ANALYZE EXECUTE and want to confirm pruning happened.

Recap

You can now confirm partition pruning from EXPLAIN output:

  • Static pruning happens at plan time when the partition key is compared to constants; pruned partitions simply vanish from the plan.
  • Execution-time pruning handles parameters and join-driven values; look for Subplans Removed: N under Append.
  • Keep the partition key bare and SARGable — wrapping it in a function blocks pruning.
  • Use EXPLAIN ANALYZE to prove which partitions actually did work, and verify enable_partition_pruning is on when nothing prunes.

Reading these markers turns partitioning from a hopeful design into a verified performance win.

よくある質問

「プラン作成時と実行時のパーティションプルーニング」レッスンは無料ですか？

はい。「プラン作成時と実行時のパーティションプルーニング」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、PostgreSQL Performance & Query Optimizationコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 PostgreSQL Performance & Query Optimizationコースには全4レッスンが含まれています。

「プラン作成時と実行時のパーティションプルーニング」で何を学びますか？

EXPLAINの出力を読み、静的および実行時のプルーニングによってクエリから不要なパーティションが除外されていることを確認します。 ブラウザで直接実行するハンズオンコードでPostgreSQL Performance & Query Optimizationを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

PostgreSQL Performance & Query Optimizationを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのPostgreSQL Performance & Query Optimizationは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。

「プラン作成時と実行時のパーティションプルーニング」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このPostgreSQL Performance & Query Optimizationレッスンでコードを書いて実行できますか？

はい。すべてのPostgreSQL Performance & Query Optimizationレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. パーティションキーと方式の選び方
  2. プラン作成時と実行時のパーティションプルーニング
  3. パーティション作成と保持期間の自動化
  4. 巨大なテーブルをオンラインでパーティションへ移行する
← PostgreSQL Performance & Query Optimizationに戻る