Adding missing indexes on foreign keys and altering the default statistic
High performance in PostgreSQL can be achieved by having optimal execution plans and proper indexes. Execution plans depend on the statistics gathered from the tables; fortunately, in postgres, one can control the behavior of the statistic collection.
Getting ready
For a developer, it is important to get good performance. When handling foreign keys, there are two recommendations to increase the performance, which are as follows:
Always index foreign keys: Indexing a table on a foreign key allows PostgreSQL to fetch data from the table using an index scan.
Increase the column statistic target on foreign keys: This is also applicable to all predicates because it allows PostgreSQL to have a better estimation of the number of rows. The default statistic target is 100, and the maximum is 10,000. Increasing the statistics target makes the
ANALYZEcommand slower.
How to do it…
Both of the preceding approaches require identifying...
