Why does my query insist on a full table scan? Closed

Your query forces a full table scan because applying a function (date_trunc) to the partition key prevents PostgreSQL from using partition pruning. Here's why and how to fix it:

Why This Happens

IssueExplanationFunction on partition keyWHERE date_trunc('day', journeyTime) = '01-May-2025' wraps journeyTime in a function, so Postgres can't match it directly to partition boundariesPartition pruning disabledThe optimizer can't determine which partition contains the data because it would need to evaluate date_trunc() for every rowPostgreSQL limitationAnything more complicated than a simple comparison (>=, <, =) on the partition key won't trigger partition pruning

The Solution

Use a range comparison instead of date_trunc():

sql

-- ❌ Doesn't work (your current query) WHERE date_trunc('day', journeyTime) = '2025-05-01' -- ✅ Works (partition pruning enabled) WHERE journeyTime >= '2025-05-01 00:00:00' AND journeyTime < '2025-05-02 00:00:00'

This allows Postgres to immediately identify the single partition for that day and skip all others.

Additional Tips

1. Prepare correct timestamps upfront in your application code rather than transforming them in SQL

2. Make sure journeyTime is the actual partition column declared with PARTITION BY RANGE (journeyTime)

3. Run ANALYZE on the table to ensure statistics are current, which helps the planner make better decisions

4. Check PostgreSQL version—partition pruning improved significantly in Postgres 11+ (declarative partitioning) compared to 10.x

The key principle: keep the partition key "bare" in your WHERE clause without wrapping it in functions so the optimizer can use it for partition elimination.

Has many characteristics of being AI generated - which is not allowed on this site.

How about you show us the explain plan so we can see? (But the other answer is probably correct - but will likely get deleted since it looks to be AI generated).

Also it appears you want a concrete answer, id so, why ask it as an open-ended question for discussion?

because clicked on the wrong button!

BTW - the AI answer is correct.

PostgreSQL can only perform partition pruning if the WHERE clause contains a comparison with the partitioning key: one side of the comparison operator must be the plain partitioning key. Burying it inside a function call or other expression won't work.

Assuming that journeytime is a timestamp with time zone and your days begin and end according to UTC, your WHERE condition should be:

WHERE journeytime >= TIMESTAMP WITH TIME ZONE '2025-05-01 00:00:00 UTC'
  AND journeytime < TIMESTAMP WITH TIME ZONE '2025-05-01 00:00:00 UTC' + INTERVAL '1 day'