CYBERTEC PostgreSQL Logo

Join pruning - Cool stuff in PostgreSQL

12.2015 / Category: / Tags: |

UPDATED July 2023: The PostgreSQL optimizer is really a wonderful piece of software which is capable of doing great things. One of those great things is so-called “join pruning”. In case PostgreSQL detects a join which is actually not needed for execution, it will automatically remove it from the plan. Removing joins from the plan can result in significantly better performance and provide end users with much simpler plans.

Let's take a look and see when a join can be pruned from a query. Here is an example.

Two tables are needed:

Mind that both tables have a primary key - which will play a major role later on.

Inspecting queries

To see what PostgreSQL does, we can write a basic query:

This is a left-join, which takes all values from the left and matches values on the right. Here's the plan:

PostgreSQL will perform a hash join to produce the final result. In the SELECT-clause, a star will ensure that all columns are returned.

However, what happens if only columns from the first table are needed?

In this case, PostgreSQL can prune the plan and just do a sequential scan on “a”.

Conclusion - Join pruning

Why is that possible? Well, first of all no information from “b” is needed to satisfy the SELECT-clause. However, this is not a sufficient condition to justify eliminating the table from the execution plan. Both sides must be unique. Remember, in a 1:n relationship the join could potentially return more data than a query without the join would. So, only if both sides are unique is it logically possible to just skip the second table. If you can prove that a simpler plan will produce the same result, you can use the simpler plan.

Finally...

Read more about joins and performance:

 

There have been two patches since this blog post was originally written that use the table definition to improve row count estimates or take shortcuts:

PostgreSQL 9.6 has added the following:
Use foreign key relationships to infer selectivity for join predicates (Tomas Vondra, David Rowley)
Here also is the improved version of this commit.


In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram