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 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, bWHERE aid = bid;
Finally, there is a view joining the first two tables together.
Let us suppose now the end user wants to run the following query. ...