Before we dive into optimizing joins, it is important to take a look at some of the most common problems arising with joins and which of them should ring alarm bells for you.
Here is an example:
test=# CREATE TABLE a (aid int);CREATE TABLEtest=# CREATE TABLE b (bid int);CREATE TABLEtest=# INSERT INTO a VALUES (1), (2), (3);INSERT 0 3test=# INSERT INTO b VALUES (2), (3), (4);INSERT 0 3
In the next example, you will see a simple outer join:
test=# SELECT * FROM a LEFT JOIN b ON (aid = bid); aid | bid-----+----- 1 | 2 | 2 3 | 3 (3 rows)
You can see that PostgreSQL will take all rows from the left-hand side and only list the ones fitting the join.
The next example might come as a surprise to many people:
test=# SELECT *
