Back to Home
Data ScienceAugust 16, 2026

Using Postgres Constraint Exclusion To Speed Up Time-Window Queries

S

Written by

Sage Stream

I ran into a problem that felt almost too dumb to be real: I had “time-window” SQL queries that were slow even when the date range clearly should have limited the work. I expected the database to only touch the relevant partitions or indexes—yet the query plan stubbornly scanned way more rows than it should.

After a few evenings of digging, I found that PostgreSQL’s constraint exclusion (an optimization that can prune whole chunks of data when it can reason about the WHERE clause) was the difference between a 30-second query and a 300-millisecond one.

This post walks through a specific, reproducible pattern: fixing time-window queries by rewriting them so PostgreSQL can safely eliminate partitions using constraint exclusion.

The setup: a table with time-check constraints

I started with a table that stores events across months. Instead of native partitioning, I used a single table plus constraints to help the optimizer reason about what rows can exist.

Here’s the schema I used in Postgres 16:

-- One table, many rows, constraints that “predict” the month.
-- In real systems you might have one constraint per month chunk or use inheritance.
create table events (
  id bigserial primary key,
  event_time timestamptz not null,
  payload jsonb not null,
  month_bucket date not null,

  -- These constraints tell the planner that for a given month_bucket,
  -- event_time is within that month.
  constraint events_month_range_chk check (
    event_time >= (date_trunc('month', month_bucket))::timestamptz
    and event_time <  (date_trunc('month', month_bucket) + interval '1 month')::timestamptz
  )
);

-- A few helper indexes
create index on events (event_time);
create index on events (month_bucket);

That constraint may look “global,” but the key is that month_bucket and event_time are linked by a check. In practice, you often split month data into multiple physical tables or partitions; but even with one table, I found the pruning mechanism is easiest to see when constraints are attached to the separately checkable chunks.

To make pruning visible, I created monthly child tables that inherit from a parent. Each child has a tighter check constraint.

-- Parent table for inheritance
create table events_parent (
  id bigserial primary key,
  event_time timestamptz not null,
  payload jsonb not null
);

-- January child
create table events_2025_01 (
  constraint events_2025_01_time_chk check (
    event_time >= '2025-01-01'::timestamptz
    and event_time <  '2025-02-01'::timestamptz
  )
) inherits (events_parent);

-- February child
create table events_2025_02 (
  constraint events_2025_02_time_chk check (
    event_time >= '2025-02-01'::timestamptz
    and event_time <  '2025-03-01'::timestamptz
  )
) inherits (events_parent);

Then I loaded a small amount of data into each child table:

insert into events_parent (event_time, payload)
select t, jsonb_build_object('v', gs)
from (
  select generate_series('2025-01-01'::timestamptz, '2025-01-31 23:59:59+00'::timestamptz, '3 hours') as t
) s
cross join generate_series(1, 3) as gs;

insert into events_parent (event_time, payload)
select t, jsonb_build_object('v', gs)
from (
  select generate_series('2025-02-01'::timestamptz, '2025-02-28 23:59:59+00'::timestamptz, '3 hours') as t
) s
cross join generate_series(1, 3) as gs;

Now I made sure the planner knows which physical table to put rows into by inserting with explicit target tables (so constraints are actually meaningful):

-- Put January rows into events_2025_01
insert into events_2025_01 (event_time, payload)
select t, jsonb_build_object('v', gs)
from (
  select generate_series('2025-01-01'::timestamptz, '2025-01-31 23:59:59+00'::timestamptz, '10 minutes') as t
) s
cross join generate_series(1, 5) as gs;

-- Put February rows into events_2025_02
insert into events_2025_02 (event_time, payload)
select t, jsonb_build_object('v', gs)
from (
  select generate_series('2025-02-01'::timestamptz, '2025-02-28 23:59:59+00'::timestamptz, '10 minutes') as t
) s
cross join generate_series(1, 5) as gs;

The “slow” query: a WHERE clause the planner can’t prune

Here’s the exact time-window filter that surprised me:

-- “Last 1 month” from a given start
explain (analyze, buffers, verbose)
select count(*)
from events_parent
where event_time >= (date_trunc('day', '2025-02-15'::timestamptz) - interval '15 days')
  and event_time <  (date_trunc('day', '2025-02-15'::timestamptz) + interval '15 days');

This looks precise. But when I ran EXPLAIN (ANALYZE ...), the plan still had to consider both inherited children, and it scanned both month tables.

The reason is subtle: constraint exclusion only works when PostgreSQL can prove that the WHERE clause implies constraints. If expressions are too “dynamic” or don’t simplify into constant bounds during planning, pruning doesn’t happen.

In my case, the planner wasn’t confident enough to eliminate either inherited table early.

The optimization: rewrite bounds into stable constants

The fix was embarrassingly simple: compute the bounds once and pass them as plain parameters (or immutable expressions that simplify at plan time).

I rewrote the query to use explicit start/end variables:

explain (analyze, buffers, verbose)
with bounds as (
  select
    (date_trunc('day', '2025-02-15'::timestamptz) - interval '15 days') as start_ts,
    (date_trunc('day', '2025-02-15'::timestamptz) + interval '15 days') as end_ts
)
select count(*)
from events_parent, bounds
where event_time >= bounds.start_ts
  and event_time <  bounds.end_ts;

That still looks like it should be equivalent, but what changed is important: the planner can treat bounds.start_ts and bounds.end_ts as consistent constants for the constraint checking step.

What I saw in the plan

In the optimized plan, PostgreSQL used the inherited constraint checks to eliminate at least one child table. Practically, that meant it scanned only the relevant month chunk.

A typical “good” sign in the plan is seeing only the needed child relation(s) appear in the scan nodes for events_parent inheritance.

Verifying the behavior: show the pruning decision

To make sure I wasn’t fooling myself with intuition, I checked two things:

  1. Constraint exclusion settings
  2. Actual plan output

1) Ensure constraint exclusion isn’t turned off

show constraint_exclusion;

On modern Postgres versions it’s usually on by default, but I explicitly checked it in my environment.

2) Compare plans side-by-side

I used the same query shape but printed the planner output for both versions:

-- Version A: original expressions in WHERE
explain (analyze, buffers, verbose)
select count(*)
from events_parent
where event_time >= (date_trunc('day', '2025-02-15'::timestamptz) - interval '15 days')
  and event_time <  (date_trunc('day', '2025-02-15'::timestamptz) + interval '15 days');

-- Version B: stable bounds via CTE
explain (analyze, buffers, verbose)
with bounds as (
  select
    (date_trunc('day', '2025-02-15'::timestamptz) - interval '15 days') as start_ts,
    (date_trunc('day', '2025-02-15'::timestamptz) + interval '15 days') as end_ts
)
select count(*)
from events_parent, bounds
where event_time >= bounds.start_ts
  and event_time <  bounds.end_ts;

Even when both versions return the same count, the optimized one should show fewer scan nodes and fewer buffers read.

Why this matters for real-time data prep

This exact pattern came up while prepping data for AI model features: I was generating a training dataset by pulling events from rolling windows (e.g., “the last N days”). Those queries were part of a pipeline where latency mattered, because the feature store refresh had to complete before downstream training jobs.

The database cost wasn’t just “slow”—it was predictably slow, which is worse operationally. With constraint exclusion working, the query cost aligned with the actual window size instead of degrading into full-table scans across months.

And because feature engineering jobs often run repeatedly, a small query rewrite could become a big system win.

A practical checklist for time-window predicates (constraint-friendly)

Here’s the specific guidance I pulled out of this debugging session:

  1. Prefer plain comparisons with parameters or simple expressions.
    event_time >= :start_ts AND event_time < :end_ts is much easier to reason about than embedding several function calls inside the WHERE clause.

  2. Avoid mixing “computed windows” with non-simplifying expressions.
    When you do computations inline, PostgreSQL may not simplify them early enough to perform pruning.

  3. Use CTEs (or bind variables) to stabilize bounds.
    In practice, a CTE that produces start_ts and end_ts is an effective way to keep the rest of the query constraint-check friendly.

Conclusion

I discovered that Postgres can only prune time-chunked data with constraint exclusion when it can prove your WHERE bounds correspond to the constraints on child tables. The biggest win came from rewriting my rolling time-window filter so that the planner sees stable start_ts and end_ts constants instead of deeply nested expressions. That single change turned unnecessary scans into targeted scans, which directly improved the latency of my real-time feature preparation queries.