Mastering PostgreSQL 12 - Third Edition

Applying equality constraints

The following process creates equality constraints. The idea is to detect additional constraints, join options, and filters. Let's take a deep breath and take a look at the following query: if aid = cid and aid = bid, we know that bid = cid. If cid = 4 and all the others are equal, we know that aid and bid have to be 4 as well, which leads 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 open the door for two additional indexes that weren't clearly visible in the original query.

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