How to Identify Columns Included in PostgreSQL Indexes?

Identifying Columns Indexed in PostgreSQL

In PostgreSQL, obtaining the columns that participate in an index can be achieved using system tables. Follow these steps:

SELECT
    t.relname AS table_name,  -- Name of the indexed table
    i.relname AS index_name,  -- Name of the index
    a.attname AS column_name  -- Indexed column name
FROM
    pg_class t  -- Catalog table for tables
JOIN
    pg_class i ON t.oid = i.indrelid  -- Catalog table for indexes
JOIN
    pg_index ix ON i.oid = ix.indexrelid  -- Index information
JOIN
    pg_attribute a ON t.oid = a.attrelid AND a.attnum = ANY(ix.indkey)  -- Join with table attributes
WHERE
    t.relkind = 'r'  -- Restrict to regular tables
    AND t.relname LIKE 'test%';  -- Filter based on pattern (optional)
ORDER BY
    t.relname,  -- Sort by table name
    i.relname;   -- Then sort by index name

Example Output:

table_name | index_name | column_name
------------+------------+-------------
test       | pk_test    | a
test       | pk_test    | b
test2      | uk_test2   | b
test2      | uk_test2   | c
test3      | uk_test3ab | a
test3      | uk_test3ab | b
test3      | uk_test3b  | b
test3      | uk_test3c  | c

For further analysis, you can group the column names and roll up the results:

SELECT
    t.relname AS table_name,  -- Name of the indexed table
    i.relname AS index_name,  -- Name of the index
    array_to_string(array_agg(a.attname), ', ') AS column_names  -- Concatenate column names
FROM
    pg_class t  -- Catalog table for tables
JOIN
    pg_class i ON t.oid = i.indrelid  -- Catalog table for indexes
JOIN
    pg_index ix ON i.oid = ix.indexrelid  -- Index information
JOIN
    pg_attribute a ON t.oid = a.attrelid AND a.attnum = ANY(ix.indkey)  -- Join with table attributes
WHERE
    t.relkind = 'r'  -- Restrict to regular tables
    AND t.relname LIKE 'test%';  -- Filter based on pattern (optional)
GROUP BY
    t.relname,  -- Group by table name
    i.relname  -- Then group by index name
ORDER BY
    t.relname,  -- Sort by table name
    i.relname;   -- Then sort by index name

Example Output:

table_name | index_name | column_names
------------+------------+--------------
test       | pk_test    | a, b
test2      | uk_test2   | b, c
test3      | uk_test3ab | a, b
test3      | uk_test3b  | b
test3      | uk_test3c  | c

The above is the detailed content of How to Identify Columns Included in PostgreSQL Indexes?. For more information, please follow other related articles on the PHP Chinese website!