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 to see what the database is really up to and what it is doing.
Learning what the optimizer does
Optimizations by example
To demonstrate how the optimizer works, I have compiled an example, one which I have used over the years in PostgreSQL training. Suppose there are three tables:
CREATE TABLE a (aid int, ...); -- 100 million rows CREATE TABLE b (bid int, ...); -- 200 million rows CREATE TABLE c (cid int, ...); -- 300 million rows
Let us assume further that those...