3 ms·
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 va
by 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 agoWhat do you suppose prevents the query planned to do the same thing?
- Tostino 2y agoI 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 agoI 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 agoI 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 agoIs 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.