Mastering PostgreSQL 9.6

Exhaustive searching

Now that those formal transformations have been done, PostgreSQL will perform an exhaustive search. It will try out all possible plans and come up with the cheapest solution to your query. PostgreSQL knows which indexes are possible and just uses the cost model to determine how to do things in the best way possible.

During an exhaustive search, PostgreSQL will also try to determine the best join order. In the original query, the join order was fixed to A → B and A → C. However, using those equality constraints we could join B → C and join A later. All options are open to the planner.

When it comes to joining, the general rule is to join large tables first to get rid of as much data as possible early on in the process. ...