Postgres ignoring index using the COALESCE function

Question

I have the following table, with ~4 million rows:

CREATE TABLE members (
  id                INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  created_at        TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at        TIMESTAMP WITH TIME ZONE,
  -- other columns...
);

I use this following query to extract the latest updated rows:

SELECT *
FROM members
WHERE COALESCE(updated_at, created_at) > current_timestamp - interval '24 hours'

This query is obviously slow, so I created an index, but it is not used by Postgres:

CREATE INDEX members_updated_or_created_at ON members(COALESCE(updated_at, created_at));

Here's the execution plan:

Seq Scan on members  (cost=0.00..171792.01 rows=1326991 width=1826) (actual time=62.663..22064.805 rows=1 loops=1)
  Filter: (COALESCE(updated_at, created_at) > (CURRENT_TIMESTAMP - '48:00:00'::interval))
  Rows Removed by Filter: 3980971
Planning Time: 0.123 ms
JIT:
  Functions: 2
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 7.481 ms, Inlining 0.000 ms, Optimization 8.067 ms, Emission 35.308 ms, Total 50.857 ms
Execution Time: 22072.906 ms

I don't understand why it's doing a table scan instead of using an index scan. I also tried to select fewer fields, and adding a limit, but it didn't change anything.

EDIT:

So it seems like the index is not used because I'm fetching many columns that are not present in the index (select *).

I tried to do the same with the updated_at column, and this time, the index is used if the only column I select is the "updated_at" column (Index Only Scan), it is not used if I include another column though.

What I don't understand, is why don't I get the same behavior with the coalesce function?

This query results in a full table scan

SELECT coalesce(updated_at, created_at)
FROM members
WHERE coalesce(updated_at, created_at) > current_timestamp - interval '7 days';

This query results in an Index Only Scan (index on updated_at)

SELECT updated_at
FROM members
WHERE updated_at > current_timestamp - interval '7 days';

What if you create indexes for each column, and also for the tuple (without function call)? — Niloct
– Niloct, Commented Nov 18, 2022 at 23:16
Because your predicate is not sargable and you are selecting all columns; Try with an index on each column and two separate unioned queries - it will still depend in whether your indexes are covering or not. — Stu
– Stu, Commented Nov 19, 2022 at 0:05
@KenWhite Same way coalesce know how to do that when it isn't in an index. The function doesn't know it is in an index, it just spits out a result and that result gets indexed. — jjanes
– jjanes, Commented Nov 19, 2022 at 1:56
@KenWhite The system knows which columns the functional index has a dependency on, and so knows when to do maintenance. — jjanes
– jjanes, Commented Nov 19, 2022 at 2:06

clemgrim · Accepted Answer · 2022-11-19 15:53:26Z

1

I found the solution, in order to force the DB to use my index, I added an "ORDER BY" clause, and it seems to work:

SELECT *
FROM members
WHERE coalesce(updated_at, created_at) > current_timestamp - interval '7 days'
ORDER BY coalesce(updated_at, created_at) DESC;

Index Scan Backward using members_updated_or_created_at on members  (cost=0.43..446282.78 rows=1326991 width=1834) (actual time=8.367..8.369 rows=1 loops=1)
  Index Cond: (COALESCE(updated_at, created_at) > (CURRENT_TIMESTAMP - '7 days'::interval))
Planning Time: 0.261 ms
JIT:
  Functions: 5
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 2.065 ms, Inlining 0.000 ms, Optimization 0.825 ms, Emission 7.484 ms, Total 10.375 ms
Execution Time: 10.524 ms

answered Nov 19, 2022 at 15:53

clemgrim

316 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres ignoring index using the COALESCE function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related