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 to 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, one which I have used over the years in PostgreSQL training. Suppose there are three tables:

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

Let us assume further that those tables contain millions, or maybe hundreds of millions, of rows. In addition to that, there are indexes:

CREATE INDEX idx_a ON a (aid); 
CREATE INDEX idx_b ON b (bid); 
CREATE INDEX idx_c ON c (cid);
CREATE VIEW v AS SELECT * FROM a, b
WHERE aid = bid;

Finally, there is a view...