What do you suppose prevents the query planned to do the same thing?
I remembered reading a thread on the hackers mailing list about this starting probably 6 or 7 years ago... Nothing ever came of it if I remember right.
Found it: https://www.postgresql.org/message-id/flat/7f70bd5a-5d16-e05...
I just did some more digging, and it looks like there has been some more recent discussion in a different thread about the same topic though: https://www.postgresql.org/message-id/flat/567ED6CA.2040504%...
Re:Making a Postgres query 1k times faster
by boomskats on Thursday May 16, 2024 @03:29AM
(#40376020)
I have a vague memory of this exact optimisation being one of the things Hibernate did automagically, about 10 years ago. I remember doing a fair bit of reading to figure out how & why it was so much faster.
I've used that OR > UNION ALL trick a number of times to vastly improve performance on specific queries. It's crazy how much of an effect it can have.
I wish Postgres would implement a planner optimization to automatically run queries with an OR more efficiently (e.g. use the same plan as with a UNION/ALL where possible).