News for hackers. Stuff that matters.
Re:Making a Postgres query 1k times faster by hansonkd on Wednesday May 15, 2024 @06:11PM (#40373032)
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.

Re:Making a Postgres query 1k times faster by Tostino on Wednesday May 15, 2024 @06:56PM (#40373362)
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).

Re:Making a Postgres query 1k times faster by hinkley on Thursday May 16, 2024 @12:16AM (#40375145)
What do you suppose prevents the query planned to do the same thing?
Re:Making a Postgres query 1k times faster by Tostino on Thursday May 16, 2024 @12:31AM (#40375212)
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.
Re:Making a Postgres query 1k times faster by hansonkd on Thursday May 16, 2024 @11:27AM (#40379564)
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.
Re:Making a Postgres query 1k times faster by adr1an on Thursday May 16, 2024 @11:53AM (#40379838)
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.
Re:Making a Postgres query 1k times faster by hun3 on Wednesday May 15, 2024 @06:54PM (#40373348)
This can be mitigated somewhat by not having ORs at top level.

Of course UNION [ALL] does often yield better performance, yes.

Re:Making a Postgres query 1k times faster by rpbiwer2 on Wednesday May 15, 2024 @08:59PM (#40374147)
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?
Re:Making a Postgres query 1k times faster by Tostino on Thursday May 16, 2024 @12:47AM (#40375266)
There were attempts to do this automatically ~7 years ago (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%...

Re:Making a Postgres query 1k times faster by xarope on Wednesday May 15, 2024 @10:46PM (#40374721)
+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.

Re:Making a Postgres query 1k times faster by zeroimpl on Thursday May 16, 2024 @12:24AM (#40375184)
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.