Detecting a missing index
In this recipe, we will be discussing how to identify the tables that need to be indexed.
Getting ready
To find the missing indexes in a database is a tricky task. To find the missing indexes on a table, we have to use the sequential, index scan counter values from the catalog tables. In case we see too many sequential scans on a table, then we can't confirm that the table is a candidate for the index. To confirm this, we have to analyze the queries that we execute on that table using hypothetical indexes.
In general, it is always recommended that you create indexes on foreign key columns, as it helps the query to choose an index while joining parent and child tables. The foreign key's index also improves the key validation ...
Get PostgreSQL High Performance Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
