Mastering PostgreSQL 12 - Third Edition

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 rows 

Let's further assume 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 that's joining the first two tables together.

Let's suppose ...