0

I'm using postgresql 11, and am looking for a way to optimize the following query, to retrieve the orders by "most recently handled", i.e. most recently completed or created first, where smth completed yesterday would still be behind something created today:

SELECT * from orders # ... other conditions
ORDER BY 
  CASE WHEN completed_at IS NOT NULL
    THEN completed_at 
    ELSE created_at
  END DESC

While the query above may be subject to debate, what I'm looking for is how to build an index to optimize such a query.

6
  • Why do you think it needs optimisation? Please consider reading this advice
    – mustaccio
    Commented Nov 2, 2022 at 12:19
  • 1
    Unrelated, but: Your CASE expression can be simplified to coalesce(completed_at, created_at)
    – user1822
    Commented Nov 2, 2022 at 12:28
  • it's not, at least not in PostgreSQL (using coalesce vs using case statement delivers different results)
    – ChuckE
    Commented Nov 2, 2022 at 15:04
  • 1
    @ChuckE Delivering different results seems like an extraordinary claim. Can you demonstrate this?
    – jjanes
    Commented Nov 2, 2022 at 15:31
  • 1
    @ChuckE if what you claim happened, it would be a bug. Commented Nov 2, 2022 at 16:01

1 Answer 1

1

Defining an expressional index on that expression can be used for ordering. The planner might decide not to use that index as it might think something else will be faster, but it would at least consider it.

CREATE INDEX ON orders (
  (CASE WHEN completed_at IS NOT NULL
    THEN completed_at 
    ELSE created_at
  END)
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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