Speed up Postgres queries with UNIONs and subquery pull-up
Today I want to start by saying: Thank you! This is episode 49 of 5mins of Postgres, and almost a year ago we started with the first episode. And back then, I wouldn't have believed if you told me we would get a thousand subscribers on our YouTube channel and this many people listening and commenting to each episode.So I really appreciate everyone's time in watching and learning more about Postgres and giving me a good reason to look at what's new with Postgres every week. This is our last episode for the year, and we'll resume again next year in early January with 5mins of Postgres on a regular weekly schedule.
Let's jump right in!
Today, we're gonna talk about 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.
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
By rewriting your query here the plan becomes much better: Two nested loops that then get
UNIONed together leading to much faster performance!
Earlier this year, Laurenz talked a little bit more about
OR should be general guidance. Should we always be rewriting an
OR to a
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 ALL to optimize your queries.
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.
Why was this happening? The gist of it is this: when Postgres has a
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
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 Twitter to get updates about new episodes! I wish you Happy Holidays and a Happy New Year!