Learning what the optimizer does

Before even attempting to think about query performance, it makes sense to familiarize yourself with what the query optimizer does. Having a deeper understanding of what is going on under the hood makes a lot of sense because it helps you see what the database is really up to and what it is doing.

Optimizations by example

To demonstrate how the optimizer works, I have compiled an example. It is something that has been used by me over the years for PostgreSQL training. Let's assume that there are three tables, as follows:

CREATE TABLE a (aid int, ...);         -- 100 million rows
CREATE TABLE b (bid int, ...);         -- 200 million rows 
CREATE TABLE c (cid int, ...);         -- 300 million...