|
Current situation or problem:
Some tables in Agreements are missing indexes that could increase performance in multiple areas including local KB data ingest and searching
In scope
- Firstly all foreign keys should have a foreign key constraint, and I think there are ones that do not. I haven't changed these because that has wider implications for existing data (we have been bitten by this recently).
- Check for any tables missing primary keys and fix (including alternate names table)
- All foreign keys should be indexed and should be the default index type from postgres (Btree). You can use liquibases CreateIndex
- If a table is a resolution of a many to many and has 2 foreign keys you should add a composite index (specify both columns in the create index entry). Order of columns here can be important, and the general rule is anything used in a join or where clause more frequently should be defined first.
- There is no need to add indexes for the columns individually as well, unless you need a free text as well as a composite match, in which case add a GIN as below for the single field as well
- Anything you search on using the text box should have a GIN index. This requires a special kind of entry in the migrations.
When adding foreign key constraints need to handle the situation where people have removed a foreign reference otherwise there will be issues when the migration to add the constraint attempts to run.
|