The point where local and global indexes diverge query plans in PostgreSQL partitioned tables

A weekly report that once jumped from 12 seconds to 47 seconds — no query changes, data volume was within expected range, and indexes were already in place. Opening EXPLAIN revealed more than twenty Index Scan nodes lined up under Append, with the planner hitting each partition's index individually. That was the moment a real incident confirmed that having an index on a partitioned table doesn't automatically make it optimal.

In PostgreSQL partitioned tables, query plans diverge entirely based not only on which column the index is on, but also whether that index is aware of partition boundaries. Without distinguishing these two axes, you can build indexes diligently and still end up with a plan that walks every partition.

This article examines the conceptual difference between local and global indexes, what signals they produce in EXPLAIN (ANALYZE, BUFFERS), and which strategy fits which situation. It also notes that as of August 2026, standard PostgreSQL (17/18) does not yet include global indexes.

Why There Are Two Kinds of Indexes on Partitioned Tables

Local Indexes: PostgreSQL's Default Behavior

When you run CREATE INDEX on a parent table, PostgreSQL automatically propagates individual indexes to each partition. This is a local index. Physically, each partition has its own independent index, and when a partition is DROPped, its index disappears along with it. From a DBA perspective, management is clean and per-partition reindexing is possible.

Below is the 12-month partition schema reused throughout this article (partitions for March–December 2025 are omitted for brevity).

CREATE TABLE events (
    id         BIGSERIAL,
    user_id    BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL,
    email      TEXT NOT NULL
) PARTITION BY RANGE (created_at);
 
CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
-- Add 10 more partitions (events_2025_03 ~ events_2025_12) with the same pattern (12 total)
 
CREATE INDEX idx_events_created_at_user_id
    ON events (created_at, user_id);
CREATE INDEX idx_events_email
    ON events (email);

So far, this is intuitive. The problem starts when you open the query plan.

Query Plan Decision Flow

When a query arrives on a partitioned table, the planner builds an execution plan in the following order.

The key is Partition Pruning. When the partition key is specified as a constant range — like WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01' — the planner eliminates irrelevant partitions entirely.

A common misread when interpreting EXPLAIN output: with static pruning (where constants are resolved at plan time), excluded partitions simply disappear from Append's child list — no "removed" line is printed. Only with runtime pruning (prepared statements, subquery results, etc.) does Subplans Removed: N appear.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01'
  AND user_id = 42;

Index Scan using events_2025_01_created_at_user_id_idx on events_2025_01 events
  (cost=0.43..8.45 rows=1 width=52) (actual time=0.021..0.024 rows=1)
  Index Cond: ((created_at >= '2025-01-01') AND (created_at < '2025-02-01')
               AND (user_id = 42))
  Buffers: shared hit=4
Planning Time: 0.412 ms
Execution Time: 0.058 ms

When static pruning succeeds completely, even Append disappears, leaving only a single partition's index scan node. Note also that because the composite index on (created_at, user_id) exists, both conditions are folded into a single Index Cond. If separate single-column indexes had been placed on created_at and user_id instead, the planner would have used only one as an index and handled the other as a Filter. This difference is an important design decision when choosing covering columns.

What Happens When the Partition Key Is Absent from the Condition

When the partition key is not in the condition, the story changes completely.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events
WHERE email = 'foo@example.com';

Append  (cost=0.43..1284.67 rows=12 width=52) (actual rows=1)
  ->  Index Scan on events_2025_01_email_idx on events_2025_01 events_1
        (cost=0.43..107.07 rows=1 width=52)
        Index Cond: (email = 'foo@example.com')
  ->  Index Scan on events_2025_02_email_idx on events_2025_02 events_2
        (cost=0.43..107.07 rows=1 width=52)
        Index Cond: (email = 'foo@example.com')
  ->  Index Scan on events_2025_03_email_idx on events_2025_03 events_3
        ...
  (repeated for all 12 partitions)
Planning Time: 1.7 ms
Execution Time: 3.9 ms

Because email is not the partition key, no pruning occurs. The planner has no choice but to search each partition's local index individually, and with 100 partitions, 100 Index Scan nodes line up under Append. Even with indexes in place, you end up "searching every index anyway."

As the partition count grows, this overhead scales linearly, and beyond several hundred partitions, Planning Time itself becomes non-negligible.

What a Global Index Does in This Pattern

A global index covers the entire table as a single index, independent of partition boundaries. For a point query like email = 'foo@example.com', a global index finds which partition the row belongs to from just that one index.

Benchmarks showing global indexes outperforming local indexes for point queries on non-partition-key columns have been reported by the Postgres Pro team and others. However, the multiplier varies greatly depending on partition count, data volume, selectivity, and workload, so rather than citing a specific number, it is safer to understand it as a structural advantage: the many index scans lined up under Append are reduced to one.

Examining Real Scenarios

Scenario 1: Date Partition + Date Condition Query (Local Index Is Sufficient)

This is the textbook success pattern for local indexes.

SELECT count(*), event_type
FROM events
WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01'
GROUP BY event_type;

Because the partition key (created_at) appears as an explicit range comparison, static pruning fires and only the events_2025_01 partition is scanned. If only one child remains under Append, the Append itself collapses and a single node is used.

Note that partition-wise aggregation (enable_partitionwise_aggregate) was introduced in PostgreSQL 11 but still defaults to off, so for reporting queries whose GROUP BY aligns with the partition key, you must explicitly enable it per session or in postgresql.conf.

Scenario 2: Function Wrapping That Defeats Partition Pruning

This is the most common pitfall encountered in production.

-- Pruning fails
SELECT * FROM events
WHERE date_trunc('month', created_at) = '2025-01-01';
 
-- Pruning works
SELECT * FROM events
WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

Wrapping the column with date_trunc() prevents the planner from deriving a constant range to compare against partition boundaries. This is not a bug — it is a design constraint: the planner cannot generally know how an arbitrary function maps to partition keys. As a result, pruning does not occur and all partitions are scanned. Type casts and indirect references can also break pruning for the same reason.

When static pruning succeeds, the excluded partitions simply vanish from Append's child list. With runtime pruning, Subplans Removed: N appears. If neither is visible but all partitions are still listed under Append, pruning is failing.

Scenario 3: Multi-Tenant Structure Requiring a Global UNIQUE

This is the most troublesome case: a date-partitioned table where the email column must be globally UNIQUE.

Standard PostgreSQL (17/18) does not directly support a global UNIQUE on a column that is not the partition key. Three workarounds are commonly used in practice.

Option 1: Side table as a unique sentinel

CREATE TABLE email_registry (
    email   TEXT PRIMARY KEY,
    user_id BIGINT NOT NULL
);
-- Perform the events INSERT and email_registry INSERT in the same transaction.
-- A UNIQUE violation is immediately caught as a PK conflict on email_registry.

Option 2: Redesign the partition key to include an email hash

Changing the partition key to something like PARTITION BY LIST (hashtext(email) % 16) allows you to define a UNIQUE constraint that includes the partition key, making it enforceable in standard PG. The tradeoff is giving up time-based partitioning.

Option 3: Enforce at the application level with a trigger + advisory lock

CREATE OR REPLACE FUNCTION check_email_unique()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_advisory_xact_lock(hashtext(NEW.email));
    IF EXISTS (SELECT 1 FROM events WHERE email = NEW.email) THEN
        RAISE EXCEPTION 'email already exists: %', NEW.email;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_events_email_unique
BEFORE INSERT ON events
FOR EACH ROW EXECUTE FUNCTION check_email_unique();

Without a serializing lock like pg_advisory_xact_lock, two concurrent transactions can both pass the EXISTS check and both INSERT — a classic race condition. Covering UPDATE as well requires a separate trigger and conditions.

Among the three, Option 1 is the safest and easiest to diagnose, but it carries the burden of always keeping two tables in sync. Ultimately it is a tradeoff between write frequency and how quickly a UNIQUE violation must be detected.

Global Index Status — As of August 2026

"Can't we just use a global index?" is a frequent question, and the answer depends entirely on the environment.

Multiple contributors including Highgo Software have submitted Global Unique Index patches to the PostgreSQL mailing list, but none have been merged into the core yet. For now, workarounds are required if global indexes are needed in standard PostgreSQL.

Local vs. Global Index: When to Choose Which

Rather than a numeric formula, it is more honest to judge by which of the following conditions carries more weight.

Signals that favor local indexes

• Most query WHERE clauses include a partition key condition
• Write throughput is high, making index maintenance cost sensitive
• Per-partition DROP, ATTACH, and DETACH operations occur frequently
• The environment is restricted to standard PostgreSQL only

Signals that favor global indexes

• Most queries are point lookups on non-partition-key columns
• Partition count is in the tens to hundreds or more
• A global UNIQUE constraint is a business requirement
• Extended implementations such as Postgres Pro Enterprise or PolarDB are available

Two common judgment mistakes:

1. Demanding a global index on partition-key columns — Queries that filter by the partition key are already optimized by local index + pruning. Adding a global index only increases DML overhead.
2. Statistics drift as partition count grows — Statistics on the parent table are not updated by automatic ANALYZE on child partitions alone; manual ANALYZE may be required. Incorrect row estimates can lead to poor join ordering or scan method choices.

Quick Tour of Diagnostic Tools

When checking index efficiency on a running partitioned table, filtering child partitions with LIKE 'events_%' risks picking up unrelated tables like events_backup or events_old. Using pg_inherits to accurately traverse the parent-child relationship is safer.

SELECT
    n.nspname AS schema_name,
    c.relname AS partition_name,
    i.relname AS index_name,
    s.idx_scan,
    s.idx_tup_read,
    s.idx_tup_fetch
FROM pg_inherits h
JOIN pg_class parent ON parent.oid = h.inhparent
JOIN pg_class c      ON c.oid = h.inhrelid
JOIN pg_namespace n  ON n.oid = c.relnamespace
JOIN pg_index idx    ON idx.indrelid = c.oid
JOIN pg_class i      ON i.oid = idx.indexrelid
JOIN pg_stat_all_indexes s ON s.indexrelid = i.oid
WHERE parent.relname = 'events'
ORDER BY s.idx_scan DESC;

If index scan counts are concentrated on specific partitions, queries are focusing on those partitions. Conversely, if the counts are uniformly distributed across all partitions, pruning is likely not working correctly.

To track plan changes for slow queries, enabling auto_explain alongside is convenient.

LOAD 'auto_explain';
SET auto_explain.log_min_duration = '1s';
SET auto_explain.log_analyze = on;
SET auto_explain.log_buffers = on;

When diagnosing, using FORMAT JSON makes it easier to spot differences between actual and estimated row counts.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM events WHERE email = 'foo@example.com';

Closing — Three Things You Can Do in Production Right Now

Index strategy for partitioned tables ultimately comes down to one question: Can this query's WHERE condition hand the planner a partition boundary constant? If it can, local index + pruning is sufficient. If many queries cannot, it is time to consider a global index or a schema redesign.

If you have a partitioned table running in production right now, after reading this article try checking just three things:

• Run EXPLAIN (ANALYZE, BUFFERS) on representative read queries and verify that the number of child partitions under Append matches expectations, and whether Subplans Removed appears.
• Grep for queries where date_trunc(), casts, or function wrapping are applied to partition key columns, and rewrite them as constant range comparisons.
• Use pg_stat_all_indexes to observe whether per-partition index usage is uniform or skewed.

The problem of function wrapping like date_trunc() silently killing pruning tends to surface long after deployment — query results remain correct while performance slowly degrades. Making EXPLAIN part of your release checklist is a single habit that prevents far more incidents than you might expect.

References

• PostgreSQL Official Documentation: Table Partitioning
• PostgreSQL Official Documentation: Partition Pruning
• Global indexes for partitions in Postgres Pro (Habr / Postgres Professional)
• Postgres Pro Enterprise: pgpro_gbtree
• PolarDB: Use Global indexes on partitioned tables (Alibaba Cloud)
• PostgreSQL Mailing List: Proposal — Global Index for PostgreSQL
• PostgreSQL Mailing List: Patch — Global Unique Index (Highgo)
• Oracle to Aurora PostgreSQL Migration Playbook: Local vs Global Partitioned Indexes (AWS)
• Global Unique Constraint on a Partitioned Table in PostgreSQL and YugabyteDB (DEV Community)
• PostgreSQL global statistics on partitioned table require a manual ANALYZE (DEV Community)
• Deep Dive: PostgreSQL 17 Partitioning — Optimize Queries for 1B+ Rows