Multi-column index support and related checks

[ReinierMaas]

XRef: https://www.postgresql.org/docs/current/indexes-multicolumn.html

dbcritic/Dbcritic/Check/IndexFkRef.idr

Lines 7 to 17 in 829db64

	mkIssue : String -> String -> String -> Issue
	mkIssue table fk cols =
	let
	qual = table ++ "." ++ fk
	identifier = [ table, fk ]
	description = "The foreign key constraint ‘" ++ qual ++ "’ is missing an index on its referencing side."
	problems = [ "Updating rows in the referenced table causes a sequential scan on the referencing table."
	, "Deleting rows from the referenced table causes a sequential scan on the referencing table." ]
	solutions = [ "Create an index on ‘" ++ table ++ " (" ++ cols ++ ")’." ]
	in
	MkIssue identifier description problems solutions IsNonEmpty IsNonEmpty IsNonEmpty

There are two related warnings, one can be improved and the other can be newly added:

• When you have a multi-index that covers the required column it should be detected and not issue the Index Foreign Key warning
• We should try to minimise the number of indexes that a database has, we can detect overlapping indexes and propose to drop the prefix indexes

Having fewer indexes, especially overlapping indexes, gives a larger chance that a certain index is used. Reducing the number of indexes used makes them more likely to be in memory and updates need to write to fewer indexes. An index that is in memory is quicker to read from and updating fewer indexes on write speeds up writes.

---

There is a known bug with the Index Foreign Key warning:

In the check index_fk_ref, the order of the columns in the suggested index is not always correct.