Mastering PostgreSQL 9.6

Applying equality constraints

The following process creates equality constraints. The idea is to detect additional constraints, join options, and filters. Let us take a deep breath and take a look at the query: If aid = cid and aid = bid, we know that bid = cid. If cid = 4 and all the others are equal too, we know that aid and bid have to be 4 as well, which leads us to the following query:

SELECT  * FROM    a, b, c WHERE   a.aid = c.cid         AND aid = bid         AND cid = 4         AND bid = cid         AND aid = 4         AND bid = 4 

The importance of this optimization cannot be stressed enough. What the planner did here was to open the door for two additional indexes, which were not clearly visible in the original query.

By being able to use indexes on all three columns, ...