15 ms·

OR Queries are performance killers because they often force a full table scan. Another alternative to the Or is actually a UNION. A UNION allows you to use two

OR Queries are performance killers because they often force a full table scan. Another alternative to the Or is actually a UNION. A UNION allows you to use two separate indexes and can speed up queries.

Tostino 2y ago
If at all possible, use a union all rather than a plain union to avoid an extra sort / unique node. 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).
- hinkley 2y ago
  What do you suppose prevents the query planned to do the same thing?
  - Tostino 2y ago
    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-e05c-f0b4-2fdfc8873489%40BlueTreble.com 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%40sigaev.ru https://www.postgresql.org/message-id/flat/567ED6CA.2040504%...
- boomskats 2y ago
  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.
- hansonkd 2y ago
  I was able to implement this in the ORM layer of Django through a function that would inspect the query and split it if it had an OR under certain conditions.
  - adr1an 2y ago
    Is this a django extension? Can you send a link if this is public code? I'd like to see if it speeds up anything on my app.
hun3 2y ago
This can be mitigated somewhat by not having ORs at top level. Of course UNION [ALL] does often yield better performance, yes.
rpbiwer2 2y ago
This seems like the type of thing that a sophisticated query planner in 2024 would be able to figure out on its own? Maybe as a non database expert I'm expecting too much?
- Tostino 2y ago
  There were attempts to do this automatically ~7 years ago (https://www.postgresql.org/message-id/flat/7f70bd5a-5d16-e05c-f0b4-2fdfc8873489%40BlueTreble.com https://www.postgresql.org/message-id/flat/7f70bd5a-5d16-e05...). It didn't actually make it in due to worries about the approach and properly de-duplicating the final resultset, as well as that approach to the optimization not playing well with duplicate inner-join removal optimizations (which were also being discussed at the time). There have been more recent attempts to improve OR clause performance in another thread: https://www.postgresql.org/message-id/flat/567ED6CA.2040504%40sigaev.ru https://www.postgresql.org/message-id/flat/567ED6CA.2040504%...
xarope 2y ago
+1. If you really understand your queries (which, if your queries are complicated enough that you already don't use an ORM, you should), then my favorite set of "tricks" are CTEs to collect info and convert/translate as necessary, then unions to merge/filter said data from the CTEs. Add aggregate/window functions to this data, and you are good to go. You can really do some complicated pipelining of data cleaning and filtering this way, much faster than resorting to a whole bunch of complicated inline SQL work in the clauses. [edit] I should add, learn how to read an explain plan, this is vital for understanding and improving your queries.
zeroimpl 2y ago
While OR can be performance killers, in other cases it works perfectly fine, or sometimes even better than the equivalent UNION ALL query. In this case the problem is that they are applying a LIMIT with ORDER BY so it can’t do the usual BitmapOr optimization and thus the index ends up being used purely for sorting purposes.

Tostino 2y ago

If at all possible, use a union all rather than a plain union to avoid an extra sort / unique node. 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).

hinkley 2y ago
What do you suppose prevents the query planned to do the same thing?
- Tostino 2y ago
  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-e05c-f0b4-2fdfc8873489%40BlueTreble.com 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%40sigaev.ru https://www.postgresql.org/message-id/flat/567ED6CA.2040504%...
boomskats 2y ago
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.
hansonkd 2y ago
I was able to implement this in the ORM layer of Django through a function that would inspect the query and split it if it had an OR under certain conditions.
- adr1an 2y ago
  Is this a django extension? Can you send a link if this is public code? I'd like to see if it speeds up anything on my app.

hun3 2y ago

This can be mitigated somewhat by not having ORs at top level. Of course UNION [ALL] does often yield better performance, yes.

rpbiwer2 2y ago

This seems like the type of thing that a sophisticated query planner in 2024 would be able to figure out on its own? Maybe as a non database expert I'm expecting too much?

Tostino 2y ago
There were attempts to do this automatically ~7 years ago (https://www.postgresql.org/message-id/flat/7f70bd5a-5d16-e05c-f0b4-2fdfc8873489%40BlueTreble.com https://www.postgresql.org/message-id/flat/7f70bd5a-5d16-e05...). It didn't actually make it in due to worries about the approach and properly de-duplicating the final resultset, as well as that approach to the optimization not playing well with duplicate inner-join removal optimizations (which were also being discussed at the time). There have been more recent attempts to improve OR clause performance in another thread: https://www.postgresql.org/message-id/flat/567ED6CA.2040504%40sigaev.ru https://www.postgresql.org/message-id/flat/567ED6CA.2040504%...

xarope 2y ago

+1. If you really understand your queries (which, if your queries are complicated enough that you already don't use an ORM, you should), then my favorite set of "tricks" are CTEs to collect info and convert/translate as necessary, then unions to merge/filter said data from the CTEs. Add aggregate/window functions to this data, and you are good to go. You can really do some complicated pipelining of data cleaning and filtering this way, much faster than resorting to a whole bunch of complicated inline SQL work in the clauses. [edit] I should add, learn how to read an explain plan, this is vital for understanding and improving your queries.

zeroimpl 2y ago

While OR can be performance killers, in other cases it works perfectly fine, or sometimes even better than the equivalent UNION ALL query. In this case the problem is that they are applying a LIMIT with ORDER BY so it can’t do the usual BitmapOr optimization and thus the index ends up being used purely for sorting purposes.