Postgresql – Postgres not using index when expression in WHERE condition

postgresqlpostgresql-12

The table my_table has an index on the column timestamp.

The following query makes use of this index:

explain select
  item_id,
  timestamp
  position
from my_table t
where t.timestamp > '2020-05-05'::timestamp

QUERY PLAN
(...)
->  Index Scan using (..) on "my_table" t  (cost=0.57..641762.36 rows=926839 width=52)

But this other query doesn't — almost identical but the WHERE condition depends on an expression:

explain select
  item_id,
  timestamp
  position
from my_table t
where t.timestamp > (select max(timestamp) from other_table)

QUERY PLAN
(...)
->  Seq Scan on "my_table" t  (cost=0.00..33523870.16 rows=343386904 width=52)

What I'm getting trouble understanding is, once the select max() subquery has been executed, the max(timestamp) value is known and fixed, so how comes Postgres is unable to make an index scan based on this (now known) value? I don't see how it makes sense.

More importantly, how to circumvent this so that Postgres does use the index on my_table.timestamp? Is there a way around this?

Best Answer

The plan is determined before the query is executed, and such a choice cannot be made at execution time in PostgreSQL.

I see two ways:

  • execute two queries and use the result of the first in the second

  • add a second "cut off" condition like

    AND t.timestamp > '2020-01-01'

    that won't change the result, but let the optimizer know that an index scan will be the right choice

Related Question