Speed up Postgres queries with UNIONs and subquery pull-up
Today, we're gonna talk about Postgres UNION. We'll look into the differences of Postgres UNION vs. OR and are investigating if using UNION is always the better choice. Quick answer: It is not.
Share this episode: Click here to share this episode on twitter, or sign up for our newsletter and subscribe to our YouTube channel.
Transcript
Let's jump right in!
Postgres UNION Performance
Today, we're gonna talk about Postgres UNION. This is a blog post that Laurenz Albe written earlier this week about UNION ALL , describing how it behaves with data types and how that can cause performance issues. I always appreciate Laurenz's posts because he goes into a lot of detail and tries to give you a good, common sense of how to optimize your queries based on his own experience as a consultant.
This blog post actually referenced an earlier post that I want to give you a quick summary on. In this article from 2018, Laurenz wrote about avoiding using OR if you care about Postgres performance. Let’s walk through an example on why he suggested this: Oftentimes when you have a query in Postgres - you might be querying a table and then saying: this column equals this value, OR this other column equals this other value - then what Postgres does in this particular case is to choose to do a Bitmap Heap Scan across two different indexes. That can be fast, that can be slow. It depends a little bit on the particular situation.
Using UNION vs. OR in Postgres
In his post back then, Laurenz described a particular situation that he considered to be really problematic, where an OR was not efficient at all. What ended up happening was that he joined two tables and said: “give me the results matching that ID in one table or the ID in the other table, and we are joining those two tables with the same ID.”
Postgres picked a really odd plan here. It ended up doing an Index Scan without any condition, just scanning the whole index into memory and then doing a Merge Join onto these two tables and using a Join Filter.
Obviously that's very slow. Back then, Laurenz recommended that instead of doing an OR condition in such a situation you can actually use a UNION.
By rewriting your query here the plan becomes much better: Two nested loops that then get UNIONed together leading to much faster performance!
Is UNION always faster than OR?
Earlier this year, Laurenz talked a little bit more about UNION over OR should be general guidance. Should we always be rewriting an OR to a UNION?
To answer this question, he generated two test tables, inserted a bunch of values and started exploring the following: if I use UNION, is it going to get me the same result in all cases? And can be seen in his article: This is actually not the case!
In his example, you cannot safely rewrite to a UNION because there is actually different behavior when there are duplicates. You shouldn't necessarily do this all the time, but you may choose to do UNION or UNION ALL to optimize your queries.
Understanding Postgres' UNION ALL
Earlier this week he ran into an interesting problem when using UNION ALL, and the situation was slightly different: essentially, a form of polymorphism using different shaped tables and then creating a view across tables to query two tables together.
Laurenz had three tables:
- a "bird" table,
- a "bat" table,
- a "cat" table.
Bats and birds are flying animals, so they both have wingspans. Bats and cats are mammals so they have a body temperature. This is a good simplified example of an actual real-world situation he ran into. He loaded about a million rows for testing and used a lookup table, and the lookup table is the data that he's looking for, so the same set of IDs.
In one case, he queried the flying animal table. In the other case, he queried the mammal table. These should behave the same way. Now, what's really strange here is that one table took 380 milliseconds, while the other table took 1 millisecond. For the same number of rows! Why is it slow in one case, but not the other?
As always, you should look at EXPLAIN plans.
In the good case, Postgres chooses a Nested Loop. In the bad case, Postgres chooses a Hash Join.
Postgres UNION and subqueries
Why was this happening? The gist of it is this: when Postgres has a UNION, this UNION is a set of subqueries. The Postgres planner has a method to pull up subqueries into the overall join tree, which is necessary for the optimizer to then be able to use a parameterized index scan in situations like this.
If you have two different queries you're UNIONing, you have to make sure to not have any data type coercions in order for subquery pull-up to work. That means there are no casts that Postgres needs to do implicitly, versus you doing it explicitly.
If you have two different queries you're UNIONing, you have to make sure to not have any data type coercions in order for subquery pull-up to work!
The problem in the above example was that the bird table had wingspan as a different data type than the bat table. In one case numeric, in the other case real.
The way to fix this was to do an explicit cast to real on one of the tables. That way, the tables had the same data type and Postgres can choose the fast execution plan.
Thanks for joining us for today’s episode of 5mins of Postgres. Subscribe to our YouTube channel, sign up for our newsletter and follow us on LinkedIn and Twitter to get updates about new episodes! I wish you Happy Holidays and a Happy New Year!