1

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?

0

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

| improve this answer | |
0

Without further schema information, like what the relationship between my_table and other_table is, it's a bit difficult to answer with a query directly, but I've done my best below. As Laurenz Albe mentioned, you could solve this using another query. That query could take the form of a CTE:

WITH max_ts AS (select max(timestamp) from other_table)
SELECT
  item_id,
  timestamp
  position
FROM my_table t
JOIN max_ts
WHERE t.timestamp > max_ts.timestamp

Or a subquery:

SELECT
  item_id,
  timestamp
  position
FROM my_table t
JOIN (select max(timestamp) from other_table) AS max_ts
WHERE t.timestamp > max_ts.timestamp

Or, if this is called inside a plpgsql function:

CREATE FUNCTION 
...
RETURNS TABLE ( ... )
LANGUAGE PLPGSQL
AS $$
DECLARE
    max_ts TIMESTAMP ;
BEGIN
    SELECT MAX(timestamp) INTO max_ts FROM other_table ;
    RETURN QUERY
    SELECT
      item_id,
      timestamp
      position
    FROM my_table t
    WHERE t.timestamp > max_ts ;
END ;
$$ ;

I would guess that the optimizer is making a non-SARGable assessment of the predicate and performing the sequential scan because it doesn't know whether or not the index can be used at plan time. Depending on the specificity of a "cut off" condition, it may still revert to a sequential scan, although I don't know that for certain. I imagine that would be dependent on your statistics.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.