News for hackers. Stuff that matters.
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.