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.
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.
Both of the preceding approaches require identifying foreign keys. The pg_catalog.pg_constraint function can be used to look up table constraints. To get all foreign key constrains, one can simply run the following query:
SELECT * FROM pg_constraint WHERE contype = 'f';
Also, from the previous examples, we can see how to get overlapping indexes; one can combine information from both tables to get foreign keys that do not have indexes, as follows:
SELECT conrelid::regclass,conname,reltuples::bigint,indkey,conkey ,CASE WHEN conkey && string_to_array(indkey::text, ' ')::SMALLINT[] THEN FALSE ELSE TRUE END as might_require_index FROM pg_constraint JOIN pg_class ON (conrelid = pg_class.oid) JOIN pg_index ON indrelid = conrelid WHERE contype = 'f'
Note that if indkey overlaps with conkey, we might not need to add an index; however, this should be validated by the usage pattern and how the rows are selected. Also, in the preceding example, the number of reltuples is selected as this is an important factor to decide index creation because performing sequential scans on big tables is quite costly. After the identification of foreign keys, one can use the CREATE INDEX command and ALTER TABLE to create indexes and alter default statistics, respectively.