WHERE $1::timestamptz IS NULL OR "timestamp" > $1
SQL is quite flexible, making it easy to write a single query that works in two situations: one without a filtering parameter and one with a parameterized filter. For example, I came across a benchmark comparing MongoDB and PostgreSQL handling pagination effectively—by avoiding OFFSET and instead using the last value to fetch the next set of results. The first page typically uses only ORDER BY and LIMIT, while subsequent pages add a WHERE condition to continue from the last value returned.
In the MongoDB version of this benchmark, the filter is handled within the application, which leads to two separate queries for these scenarios.
export async function getOrders(cursor) {
const match = cursor ? { timestamp: { $gt: new Date(cursor) } } : {};
const rows = await orders
.aggregate([
{ $match: match },
{ $sort: { timestamp: 1 } },
{ $limit: PAGE_SIZE },
])
The PostgreSQL version uses a single prepared statement. SQL is such a powerful language that it often feels tempting to write it this way:
SELECT *
FROM orders
WHERE $1::timestamptz IS NULL OR "timestamp" > $1
ORDER BY "timestamp" ASC
LIMIT ${PAGE_SIZE}
If $1 is NULL, the predicate evaluates to true for every row, effectively disabling the filter and returning the first rows in timestamp order without applying any filter. When $1 has a value, it filters the results using that specific value, enabling a more targeted search.
However, expressing both use cases in a single generic predicate may result in an execution plan that is suboptimal for some parameter value.
I gave it a try:
drop table if exists orders;
create table orders (
order_id text primary key,
"timestamp" timestamptz not null
);
create index idx_orders_timestamp
on orders("timestamp");
insert into orders
select
'ORD-'||g,
'2025-01-01'::timestamptz + g * interval '1 minute'
from generate_series(1, 5000000) as g;
analyze orders;
prepare getorders(timestamptz, int) as
select * from orders
where $1::timestamptz is null or "timestamp" > $1
order by "timestamp" asc limit $2
;
explain (analyze, buffers, settings, costs off)
execute getorders(date'2026-01-01'::timestamptz, 10);
Here is the execution plan:
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Limit (actual time=0.028..0.031 rows=10.00 loops=1)
Buffers: shared hit=4
-> Index Scan using idx_orders_timestamp on orders (actual time=0.027..0.029 rows=10.00 loops=1)
Index Cond: ("timestamp" > '2026-01-01 00:00:00+00'::timestamp with time zone)
Index Searches: 1
Buffers: shared hit=4
Planning Time: 0.117 ms
Execution Time: 0.042 ms
This looks great because the planner was able to evaluate date'2026-01-01'::timestamptz is null during planning and simplify the predicate. The index scan is efficient, reading just about rows=10.00 entries.
Let's see what happens when PostgreSQL uses a generic plan instead of a custom one:
set plan_cache_mode to force_generic_plan;
explain (analyze, buffers, settings, costs off)
execute getorders(date'2026-01-01'::timestamptz, 10);
It's still an Index Scan, but without an Index Cond to narrow the scan range - the predicate is an after-scan Filter:
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Limit (actual time=71.945..71.948 rows=10.00 loops=1)
Buffers: shared hit=4786
-> Index Scan using idx_orders_timestamp on orders (actual time=71.943..71.945 rows=10.00 loops=1)
Filter: (($1 IS NULL) OR ("timestamp" > $1))
Rows Removed by Filter: 525600
Index Searches: 1
Buffers: shared hit=4786
Settings: plan_cache_mode = 'force_generic_plan'
Planning Time: 0.030 ms
Execution Time: 71.971 ms
Since the generic plan must work for all possible values of $1, PostgreSQL cannot transform the predicate into an Index Cond on "timestamp". As a result, it reads 525600 unnecessary index entries, which are then removed by the filter.
While the default setting plan_cache_mode = 'auto' often works well, it relies on a heuristic: PostgreSQL executes the statement a few times with custom plans, then compares the estimated cost of a generic plan with the average estimated cost of the custom plans. The decision is based on estimates rather than actual execution times, which means it may occasionally select a generic plan even when a custom plan would perform better for a particular workload.
One alternative is to rewrite the predicate so that PostgreSQL can always generate an index condition.
reset plan_cache_mode;
prepare getorders_better(timestamptz, int) as
select * from orders
where timestamp > coalesce($1, '-infinity'::timestamptz)
order by "timestamp" asc limit $2
;
With the new query, there is always an Index Cond. The main difference is whether the scan range starts at -infinity or at the supplied parameter value:
postgres=# explain execute getorders_better(null::timestamptz, 10)
;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Limit (cost=0.43..0.78 rows=10 width=19)
-> Index Scan using idx_orders_timestamp on orders (cost=0.43..174194.43 rows=5000000 width=19)
Index Cond: ("timestamp" > '-infinity'::timestamp with time zone)
(3 rows)
postgres=# explain execute getorders_better(date'2026-01-01'::timestamptz, 10);
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Limit (cost=0.43..0.78 rows=10 width=19)
-> Index Scan using idx_orders_timestamp on orders (cost=0.43..155802.94 rows=4471972 width=19)
Index Cond: ("timestamp" > '2026-01-01 00:00:00+00'::timestamp with time zone)
(3 rows)
postgres=# set plan_cache_mode to force_generic_plan;
SET
postgres=# explain execute getorders_better(null::timestamptz, 10)
;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Limit (cost=0.43..5807.41 rows=166667 width=19)
-> Index Scan using idx_orders_timestamp on orders (cost=0.43..58070.11 rows=1666667 width=19)
Index Cond: ("timestamp" > COALESCE($1, '-infinity'::timestamp with time zone))
(3 rows)
postgres=# explain execute getorders_better(date'2026-01-01'::timestamptz, 10);
QUERY PLAN
----------------------------------------------------------------------------------------------------
Limit (cost=0.43..5807.41 rows=166667 width=19)
-> Index Scan using idx_orders_timestamp on orders (cost=0.43..58070.11 rows=1666667 width=19)
Index Cond: ("timestamp" > COALESCE($1, '-infinity'::timestamp with time zone))
(3 rows)
This revised query is effective because the Index Scan is optimal for pagination with order by "timestamp" asc limit $2, even when a null parameter is used. However, this isn't always the case, as a generic plan must estimate selectivity without knowing the actual parameter values. In some situations, this can lead to poor cardinality estimates and suboptimal plan choices.
I used prepared statements because they are the standard way to execute parameterized queries while avoiding SQL injection. However, you might face the same problem if you construct a custom query using a hardcoded value while keeping the generic predicate. For instance, you could include your parameters in a WITH clause's common table expression.
postgres=# explain
with param as ( select
date'2026-01-01'::timestamptz as p1
)
select * from orders, param
where p1::timestamptz is null or "timestamp" > p1
order by "timestamp" asc limit 10
;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..0.90 rows=10 width=27)
-> Index Scan using idx_orders_timestamp on orders (cost=0.43..210363.45 rows=4467609 width=27)
Filter: ((('2026-01-01'::date)::timestamp with time zone IS NULL) OR ("timestamp" > ('2026-01-01'::date)::timestamp with time zone))
(3 rows)
Since ((('2026-01-01'::date)::timestamp with time zone IS NULL) is provably false, the OR expression could theoretically be simplified to a single predicate. However, PostgreSQL currently does not perform this simplification. The optimizer source code explicitly notes that OR branches proven to be always false could theoretically be removed, but this optimization is not currently implemented:
Currently, when processing OR expressions, we only return true when all of the OR branches are always false. This could perhaps be expanded to remove OR branches that are provably false. This may be a useful thing to do as it could result in the OR being left with a single arg. That's useful as it would allow the OR condition to be replaced with its single argument which may allow use of an index for faster filtering on the remaining condition.
This situation mirrors what happens when a prepared statement is executed using a generic plan. When using generic predicates, such as p1::timestamptz is null or "timestamp" > p1, which contain branches that might be false depending on parameter values, the planner cannot safely eliminate those branches to get a sargable condition. The same workaround applies: if you understand the specific parameter characteristics, write a more targeted query to enable index usage, maybe one per scenario.
For workloads where parameter values can lead to radically different optimal plans, relying on plan_cache_mode = auto may not always produce the desired result. In such cases, forcing custom plans with plan_cache_mode = force_custom_plan can be a simple way to guarantee that the optimizer considers the actual parameter values on every execution.
The final example shows that PostgreSQL's failure to convert OR expressions into indexable predicates extends beyond generic plans. Even when the value is known at planning time via a single-row CTE and not a parameter, PostgreSQL treats the OR expression as a filter instead of simplifying it. This indicates a broader optimizer limitation in OR-clause simplification that you should know and address when writing the SQL query.
