The series “PostgreSQL Optimiser Bits” will introduce the strategies and highlights of the PostgreSQL optimiser. We start today with a new feature of PostgreSQL 8.4: Semi and Anti Joins.

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.