Language Selection

English French German Italian Portuguese Spanish

PostgreSQL Optimizer Bits: Semi and Anti Joins

Filed under

Since version 8.4, PostgreSQL has been offering a new optimisation strategy for the optimisation of certain queries: Semi and Anti Joins.

A Semi Join is a specific form of a join, which only takes the keys of relation a into account if these are also present in the associated table b. An Anti Join is the negative form of a Semi Join: that is, a key picked in table a will be taken into account if it is not present in table b.

To summarize, Semi and Anti Joins are specific forms of a join which only take certain keys on the left side into account - where queries want to make sure certain keys exist, but are not concerned with the content of the key itself. This behaviour is already widely known in Object Relation Mappers (ORM) which formulate such queries using EXIST() or NOT EXIST().

Read the optimization results here.

More in Tux Machines

Security Leftovers

Android Leftovers

Using open source principles to build better engineering teams

We become better software developers by observing how some of the best software in the world is being written. Open source has changed and will continue to change the way the world builds software, not only by creating high-quality reusable components, but by giving us a model for how to produce better software. Open source gives us complete transparency into that process. Read more

LinuxCon Europe and Embedded Linux Conference Europe