Common mistakes: UNION vs. UNION ALL

In my role as a PostgreSQL consultant and trainer there are a couple of issues, which pop up on a regular basis. One of those issues has to do with set operations. Many people seem to misunderstand the way UNION and UNION ALL work.

UPDATE Sept. 2023: See this post on UNION ALL for more recent information.

The basic assumption of most users is that UNION just appends data – nothing can be further from the truth. Here is why:

Most people would expect that two rows will be returned from this query. In fact, it is just one row. The reason for that is simple. UNION does NOT just append data – it also removes duplicates. As we have two identical numbers in our example, only one row will remain.

We can use the explain command to see how this operation is performed internally:

PostgreSQL will sort the data and remove duplicates later on. The way PostgreSQL handles this query also explains, why many users report performance problems when using UNION. PostgreSQL has to sort all the data to remove duplicate – this is clearly more expensive than just adding data to some result.

What most people really want

What people in many cases really want is UNION ALL.

Let us take a look at an example:

In this case data is really just appended and therefore we get two lines of data.

The plan will reveal that things are really the way we want them to be:

PostgreSQL will take both result sets and just add them up.

So be careful when it comes to set operations and think twice if you really want to filter duplicates or not.

----------
We welcome your feedback on our brand new Facebook page: www.fb.com/cybertec.postgresql