my_table has an index on the column
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?