Query goal: | Find composite indexes that do not support any constraint but are on more than three columns. |
Notes about the query: | The query does not count any included columns, which are merely stored and do not participate in the index semantics. According to the PostgreSQL documentation: "Multicolumn indexes should be used sparingly. In most situations, an index on a single column is sufficient and saves space and time. Indexes with more than three columns are unlikely to be helpful unless the usage of the table is extremely stylized." |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | Low (Many false-positive results) |
Query license: | MIT License |
Data source: | system catalog only |
SQL query: | Click on query to copy it
SELECT pg_get_indexdef(indexrelid) AS indexdef, indnkeyatts AS number_of_columns, n.nspname AS table_schema, c.relname AS table_name FROM pg_index AS i INNER JOIN pg_class AS c ON i.indrelid=c.oid INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace INNER JOIN pg_authid AS a ON n.nspowner=a.oid WHERE (n.nspname='public' OR a.rolname<>'postgres') AND indnkeyatts >3 AND NOT EXISTS (SELECT * FROM pg_constraint WHERE i.indexrelid=pg_constraint.conindid) ORDER BY indnkeyatts DESC, table_schema, table_name; |
Collection name | Collection description |
---|---|
Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Category name | Category description |
---|---|
Performance | Queries of this category provide information about indexes in a database. |
Reference |
---|
https://www.postgresql.org/docs/current/indexes-multicolumn.html |