Optimization guidelines and catches
We had a very brief and quick look at the decisions taken by the planner. We now know how to find out what decisions were taken by the planner using EXPLAIN ANALYZE. Now, let's look at a few rules of thumb to be followed to ensure that SQL statements fetch results in a reasonable amount of time. Some of the rules deal with creating indexes, whereas others deal with avoiding certain types of queries.
Indexing foreign keys
When we create a foreign key, we have a parent-child relationship between the tables. For example, there is an order header table and an order line table, which will have multiple rows for each entry in the order header table. The child tables will usually have many more records than the parent table. It's always a good idea to create an index on the foreign key column in the child table.
There are two reasons why this is useful. First, we will be always joining the parent and child table on this column. Creating an index improves performance...
