PostgreSQL for Data Architects

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 ...