But It's a Simple Query — Why Is It So Slow?

"It's a simple query. Why does it take so long? We need this faster — do you know what you're doing?"

I hear some version of this at work all the time. Yes, I know what I'm doing. The question is whether the query knows what it's asking.

Here's the gap: there's what the application asks the database to do, and there's the actual internal work the database has to do to answer it. Those are not the same thing. The PostgreSQL query planner has a mind of its own, and it's picky about how it gets your data. Two queries that look identical to you can look completely different to the planner. For example, one detail that separates them is something most developers have never heard of: function volatility.

Two WHERE clauses that aren't the same

Look at these two queries:

-- Query A: a literal constant
SELECT * FROM events
WHERE time >= '2026-07-08 00:00:00';
-- Query B: a function expression
SELECT * FROM events
WHERE time >= now() - INTERVAL '1 day';

Same intent. Same rows (assuming you run them at the right moment). But the planner treats them differently, because of when it can know the value on the right-hand side of that comparison.

PostgreSQL classifies every function by volatility:

• IMMUTABLE — always returns the same output for the same input. upper('abc') is 'ABC' today, tomorrow, forever. The planner can evaluate these during planning and fold them into constants.
• STABLE — returns the same value for every row within a single statement, but may change between statements. now() is the classic example: it's fixed for the duration of your query, but the next query gets a different answer.
• VOLATILE — can return a different value on every single call. random(), timeofday(). The planner can assume nothing.

A bare literal like '2026-07-08 00:00:00' is even better than IMMUTABLE — it's already a constant. The planner knows the exact value while it's still deciding how to execute the query.

now() - INTERVAL '1 day' is STABLE. The planner knows it will be one value for the whole query, but it can't hardcode that value into the plan — because the plan might be cached and reused later, when now() returns something else. So the expression is evaluated once, at the start of execution, after planning is already done.

What actually changes — and what doesn't

Let me clear up a common myth first: a STABLE expression does not prevent an index scan. Because Postgres knows the value won't change during the statement, it can compute it once at executor startup and use it as the index boundary just fine. If you've been told "never use now() in a WHERE clause because it kills the index" — that's folklore. Run EXPLAIN on Query B and you'll see an Index Scan or Index Cond exactly like Query A. (VOLATILE functions are a different story — a value that can change per row genuinely can't be used as a single index boundary.)

So where's the real difference? It shows up when the plan itself depends on the value. Two big cases:

1. Partition pruning. If events is a partitioned table — or a TimescaleDB hypertable made of chunks — the planner wants to exclude partitions that can't contain matching rows. With a literal constant, it does this at plan time: partitions for last year simply never appear in the plan. With a STABLE expression, the value isn't known during planning, so the plan has to include every partition and prune at executor startup instead. You'll see it in EXPLAIN as Subplans Removed: N.

Runtime pruning works — modern PostgreSQL and TimescaleDB handle this well — but it's not free. On a table with hundreds or thousands of partitions, planning a query that references all of them takes real time: more locks, more catalog lookups, bigger plan trees. The query is "simple." The planning work isn't. That's often exactly where your mystery latency lives.

2. Prepared statements and generic plans. Here's the trap that catches people trying to be clever. A prepared statement with a parameter (WHERE time >= $1) starts out with custom plans — replanned per execution with the actual value, so pruning and estimates are good. But after a few executions, Postgres may switch to a generic plan, built without knowing the value at all. Now you get default selectivity guesses and no plan-time pruning — the same problem as the STABLE expression, sometimes worse. If a query is fast five times and slow forever after, go look at plan_cache_mode.

What to do about it

If the timestamp math is causing you pain, the fix is usually to move the computation out of the query so the database sees a concrete value:

-- Application side: compute the boundary first,
-- then send it as a literal or a bind parameter.
start_date := now() - INTERVAL '1 day';
SELECT * FROM events WHERE time >= :start_date;

The application computes now() - INTERVAL '1 day' once and hands Postgres an actual timestamp. Now the planner is back in Query A territory: exact value, accurate row estimates, plan-time partition exclusion. For TimescaleDB continuous aggregates and some chunk-exclusion paths, constant boundaries matter even more — parts of that machinery flat-out require values the planner can see.

One caution: wrapping the query in a PL/pgSQL function does not automatically fix this — functions cache plans too, and you can land right back in the generic-plan trap. The reliable pattern is: compute the value, then query with the value.

The mental model

The planner isn't being difficult. It's being honest about what it can know and when. A literal is knowledge at plan time; a STABLE function is knowledge at execution time; a VOLATILE function is knowledge never. Every step later in that sequence takes options off the planner's table.

So when someone asks why the "simple" query is slow, the real answer is: simplicity is measured from the planner's chair, not yours. Learning to sit in that chair — to ask what does the planner know, and when does it know it — is the single highest-leverage skill in PostgreSQL performance work.

If you want more of this, subscribe to the newsletter at thepostgresguy.com — and subscribe to my YouTube channel.

Let's go deeper than the docs.

— Gio