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 among child and parent tables. It is also recommended that you create indexes on the child tables, while creating child tables by inheriting from parent tables.

How to do it...

Let's query the database as to whether the delta of seq_scan...