PostgreSQL partition pruning mistakes that slow queries

Why partitioned tables still scan too much

Partition pruning should be one of the main benefits of partitioned tables. Split an orders table by month, ask for April, and PostgreSQL should read April and skip the rest.

That is the theory. In practice, a partitioned table often scans far more than expected.

The plan can look harmless at first. You ask for one week of orders, then EXPLAIN shows an Append or Parallel Append over 12, 24, or 36 partitions. Each child scan has the same filter, so PostgreSQL checks partition after partition instead of ruling most of them out early.

On a small table, you might never notice. On a busy system, the extra work adds up fast. More partitions mean more planning time, more indexes opened, more heap pages touched, and more I/O when the data is not already in memory. Even short scans hurt when there are dozens of them.

That is why partitioning disappoints people. They split the table, add indexes, and expect instant speed. Then one dashboard query still takes seconds because the planner reads every monthly table to find a few thousand rows.

Usually the cause is not dramatic. Small SQL choices can change the plan. A plain range filter on the partition column often prunes almost everything. Wrap that column in a function, compare it with the wrong type, or hide it inside a more complex expression, and pruning can weaken or disappear.

Schema design causes the same kind of trouble. If the partition key does not match how people actually query the data, PostgreSQL has less it can skip. You still have partitioned tables, just without the speed benefit you expected.

When a query on a partitioned table feels oddly heavy, do not start with row counts. Start by counting how many partitions the plan touches. That number often explains the slowdown right away.

How PostgreSQL decides which partitions to read

PostgreSQL prunes partitions by comparing your WHERE clause to the partition bounds. If a table is split by order_date, the planner looks for conditions on order_date that it can understand before it starts reading data. If that comparison is clear, it can skip whole partitions.

Simple constants make this easy. A filter like order_date >= DATE '2025-01-01' AND order_date < DATE '2025-02-01' tells PostgreSQL exactly which monthly partitions can match. The planner can often reduce the scan to one partition before execution starts.

That is plan-time pruning. It happens when PostgreSQL already knows the filter values while building the plan. Literal dates, numbers, and other values that fold into constants work well here.

Run-time pruning is different. PostgreSQL uses it when the value is not known until execution, such as a prepared statement parameter or a value coming from another part of the plan. It can still skip partitions, but the plan is usually less precise up front.

This is where people get tripped up. If you write date_trunc('month', order_date) = DATE '2025-01-01', PostgreSQL has to reason through a function applied to the partition column. That is much harder than checking the raw column against a plain range. The same thing happens with casts and arithmetic on the column side.

A small rewrite often fixes it. Keep the partition column bare and move the logic to the constant side:

WHERE order_date >= DATE '2025-01-01'
  AND order_date < DATE '2025-02-01'

That shape matches how partition bounds are stored. If the filter uses the real partition column and PostgreSQL can evaluate the comparison directly, pruning has a much better chance to work.

Schema choices that block pruning

Many bad plans start long before anyone writes the query. Partition pruning works best when PostgreSQL can compare one plain partition column to one plain filter and match that to partition bounds without extra guesswork.

Start with the data type. Pick one type for the partition column and use it everywhere. If the table partitions on date, send date values from the app. If it partitions on timestamptz, keep the filters in timestamptz. Trouble starts when the column stores one type but the app sends text, mixed parameter types, or casts such as created_at::date.

That last example is common and expensive. When you cast the column in the WHERE clause, you hide the raw partition value from the planner. Cast the constant or parameter instead, not the partition column.

Time zone handling causes the same problem. Choose one rule and stick to it. Store UTC in timestamptz, or store wall-clock time in timestamp if you really need that. Mixing both adds implicit conversions, and those conversions can make PostgreSQL read more partitions than you expect.

Even a small mismatch is enough. If the app asks for "March 1" in local time, but partitions split data by UTC month, the filter can cross a partition boundary even though the user thinks it covers one day.

The partition layout also has to match real query patterns. Monthly partitions work well when most reports ask for whole months. They help less when the app mostly pulls 3-day or 7-day windows and each partition still holds a huge amount of unrelated rows.

Range boundaries should have clean edges. Half-open ranges usually behave best: >= start and < end. They avoid overlap, line up with partition bounds, and cut down off-by-one mistakes.

Expression-heavy designs are another trap. Filters like date_trunc('month', created_at), timezone('UTC', created_at), or coalesce(created_at, now()) force the planner to work around a function instead of pruning on the raw value. If users always search by a derived business date, store that value directly and partition on it instead of wrapping every filter in logic.

A good partition key is boring. One type, one time rule, and direct comparisons usually beat clever design.

SQL patterns that quietly break the plan

Small SQL choices can wipe out the benefit of partitioning. Pruning works best when the planner sees a direct condition on the partition column, with a clear type and a simple range.

A common mistake is casting the partition column inside WHERE. If your table partitions by created_at, this can hurt pruning: created_at::date = DATE '2025-01-15'. PostgreSQL now has to transform every row value before it compares anything. A range usually works better: created_at >= TIMESTAMP '2025-01-15 00:00:00' AND created_at < TIMESTAMP '2025-01-16 00:00:00'.

Functions cause the same problem. Queries like date_trunc('month', created_at) = DATE '2025-01-01' or extract(year from created_at) = 2025 look tidy, but they hide the raw column from the planner.

OR gets messy fast too. One clean range on the partition column is easy to prune. A condition like customer_id = 42 OR created_at >= '2025-01-01' often pushes PostgreSQL toward a broader scan because one side of the OR does not narrow partitions well. If possible, split that into separate queries with UNION ALL, or rewrite it into simpler predicates.

Type mismatches from app code hurt more often than people expect. If the app sends '2025-01-15' as text and PostgreSQL has to guess the type, you can end up with extra casts or weaker estimates. The same goes for numeric IDs sent as strings. Bind parameters with the right type from the start.

Large IN (...) lists are easy to miss. An IN list on the partition column can still prune, but only to the partitions named in the list. If the list spans 18 months, PostgreSQL may still open 18 monthly partitions. That is better than scanning all of them, but it is still a lot of work. In many cases, a tight range gives a better plan and a simpler query.

A quick mental check helps: compare the partition column directly, use typed dates and timestamps, avoid wrapping the column in functions, and be suspicious of broad OR clauses.

How to check pruning step by step

If a query should hit one monthly partition but PostgreSQL reads twelve, start with the plan. EXPLAIN (ANALYZE, VERBOSE) shows which child tables it touched. Count them. If you see an Append or Merge Append with many partitions under it, pruning did not happen the way you expected.

A simple test sequence usually saves time. Run the original query and note how many partitions appear in the plan. Then replace the parameter, function result, or app variable with a plain literal, ideally a date literal. Rewrite the filter as a direct check on the partition column and compare the new plan. After that, test the same SQL through the app and outside the app, because prepared statements can switch to a generic plan. Change one thing at a time, rerun the query, and record the new partition count and timing.

The literal test matters more than it seems. If WHERE created_at >= DATE '2025-03-01' AND created_at < DATE '2025-04-01' reads one partition, but the original query with a parameter or wrapped expression reads many, the table layout is probably fine. The filter shape is the problem.

Direct predicates usually work better than expressions on the partition column. A check on created_at often prunes cleanly. A check like date_trunc('month', created_at) = DATE '2025-03-01' often does not. The two queries mean almost the same thing to you, but not to the planner.

Prepared statements deserve their own check. Some drivers reuse a generic plan after a few runs. That plan may stop using the specific parameter value that would let PostgreSQL narrow the scan early. If a query is fast in a SQL console and slow in the app, compare those two cases before touching the schema.

Measure after every small change. A drop from 24 scanned partitions to 2 tells you more than any theory.

A simple monthly orders table example

A small orders table makes the problem easy to see. You do not need special tools for it. Plain psql and EXPLAIN are enough.

CREATE TABLE orders (
  id bigint NOT NULL,
  customer_id bigint NOT NULL,
  order_date timestamp NOT NULL,
  total numeric(10,2) NOT NULL
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2025_01 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders_2025_02 PARTITION OF orders
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

CREATE TABLE orders_2025_03 PARTITION OF orders
FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');

Now use a filter that matches the partition boundaries.

EXPLAIN
SELECT count(*)
FROM orders
WHERE order_date >= TIMESTAMP '2025-02-01'
  AND order_date < TIMESTAMP '2025-03-01';

This is the clean case. The filter uses the partition column directly, and the date range lines up with one month. PostgreSQL can keep the plan focused on orders_2025_02.

A small rewrite can ruin that.

EXPLAIN
SELECT count(*)
FROM orders
WHERE date_trunc('month', order_date) = TIMESTAMP '2025-02-01 00:00:00';

That query still asks for February orders, but it wraps order_date in a function. Once you do that, the planner usually cannot narrow the scan to one partition at plan time. More partitions stay in the plan, and the query does extra work.

App code adds another trap. Many apps send a month like "2025-02" as text and write SQL around that string.

PREPARE bad_month(text) AS
SELECT count(*)
FROM orders
WHERE to_char(order_date, 'YYYY-MM') = $1;

EXPLAIN EXECUTE bad_month('2025-02');

The text parameter pushes the query into string comparison. PostgreSQL now has to run to_char for rows in each partition it checks. You still get the right answer, but the plan gets wider and slower.

If you want one month, keep the value as date or timestamp and turn it into a half-open range. That gives the planner a fair chance to read one partition instead of three.

Mistakes that start in app code and ORMs

A partitioned table can look fine in the schema and still run badly because the app sends the wrong kind of query. Partition pruning depends on clear, type-safe filters, and many ORMs get in the way without making it obvious.

One common problem is parameter type mismatch. The column stores a date, timestamp, or integer, but the app binds the value as text. PostgreSQL then has to cast somewhere, and that small detail can push the planner toward a weaker plan. If your partition key is order_date and the app sends "2025-01-01" as an untyped string, you may lose the direct comparison that pruning needs.

Optional filters cause trouble too. A lot of app code builds one query and makes conditions optional with patterns like order_date >= $1 OR $1 IS NULL. That looks tidy, but it hides the real predicate. The planner has to consider both branches, and pruning often gets less precise. Two simple queries are usually better than one clever query.

Helper methods inside ORMs can do the same damage. A method that wraps the partition column in DATE(), COALESCE(), or some custom abstraction may read nicely in application code, yet it turns a direct filter into an expression. Once the real condition disappears behind that wrapper, pruning can stop working.

Prepared statements are another quiet source of plan problems. If the app reuses one prepared query for "last 3 days" and "last 3 years," PostgreSQL may settle on a generic plan that works acceptably for both and fits neither well. On partitioned tables, that often means scanning more partitions than the actual value needs.

When you debug this, do not stop at the SQL template. Look at the final SQL the ORM produced, the bound values, the parameter types, and whether the app used a prepared statement. That usually explains the gap between the query you think you sent and the one PostgreSQL actually planned.

Quick checks before you change the schema

Many slow queries come from small SQL mistakes, not bad partition design. Before you rebuild ranges or split tables again, check whether the planner can still see the partition rule clearly.

A good first test is simple: read the WHERE clause as if PostgreSQL had to make the choice by itself. If the filter touches the raw partition column directly, pruning usually has a fair chance. If the query wraps that column in date(), timezone(), coalesce(), or another expression, the planner may stop pruning and read far more partitions than you expect.

The next checks are straightforward. Match parameter types exactly to the column type, including date, timestamp, and timestamptz. Run EXPLAIN and check whether the plan reads only the partitions you expect. Compare your partition ranges with real search habits, not with what looked neat during design. Then inspect ORM output for hidden casts, extra OR conditions, and optional filters that change the plan.

Type mismatches cause more trouble than many teams expect. A partitioned table on created_at timestamptz can behave very differently if the app sends a timestamp or a text parameter and leaves PostgreSQL to cast it later. That cast looks harmless in application code. In the plan, it can turn into wasted reads.

User behavior matters too. Monthly partitions look neat, but they do not help much if most searches ask for a customer ID with no date filter, or for the last 90 days instead of clean calendar months. In that case, the table layout and the query pattern do not match.

ORMs often make this worse by adding wrappers like ($1 is null OR created_at >= $1) or by casting values to generic text types. Those shortcuts help code reuse, but they can hurt pruning.

If EXPLAIN still shows too many partitions after these checks, then it makes sense to revisit the schema. Until then, changing the table design is often just an expensive guess.

What to fix first

Pick one slow query and one partitioned table. That narrow scope keeps the work honest. If you start by changing the whole schema, you can spend days on the wrong problem.

A good first target is a query people run often, like recent orders for one month or one customer range. Run the plan, note how many partitions PostgreSQL reads, and keep that same query while you test changes. If the plan scans ten times more partitions than expected, you already know where to focus.

Clean up the predicate before you redesign partitions. Many bad plans come from small SQL choices, not from the partition layout itself. A cast on the partition column, a function around the date, mixed data types, or an OR that spans unrelated conditions can stop pruning from doing its job.

Check the filter column type against the partition bound type. Remove wrappers like date(created_at) when a plain range filter will work. Look at the exact SQL from the app, not the hand-written version. Test the prepared statement and the literal-value version side by side. Then confirm that the partition bounds match the way the app really filters data.

Prepared statements and ORM output deserve extra attention. Teams often inspect a neat query from a console, while production sends parameterized SQL with casts or generic plans. That gap matters. A query that prunes well with fixed values can scan far more partitions when the app sends it through the ORM.

Look at the table definition, the generated SQL, and the execution plan together. One by itself is usually not enough. If the table partitions by month but the app filters by time zone-shifted expressions, the planner has to do more work than it seems.

If the plan still scans too much after you clean up the predicates, a second review can help. Oleg Sotnikov at oleg.is works with startups and small teams on PostgreSQL performance, schema design, and application query patterns. Sometimes a short architecture review is faster and cheaper than another rewrite.