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.
A practical example – how the query optimizer handles a sample query
To demonstrate how the optimizer works, I have compiled an example. It is something that I have used 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 rows
Let’s further assume that those tables contain millions, or maybe hundreds of millions,...