Retrieve columns from pg_stat_all

Given the following query:

SELECT pg_get_indexdef(indexrelid)
FROM pg_stat_all_indexes
WHERE schemaname = 'public'

I get all of the indexes that exist in the public schema. The question is if there's a way for me to query the columns that are indexed. For example, given the following table:

CREATE TABLE caregiver
(
    id         INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ssn        TEXT,

    UNIQUE (ssn)
);

I get the following index (pg_get_indexdef(indexrelid)):

CREATE UNIQUE INDEX caregiver_ssn_key ON public.caregiver USING btree (ssn)

Which I would like to return in the query the ssn.

If there's an index that is a constraint of two or more columns, then I would like to get all of them. For example:

CREATE UNIQUE INDEX index_name ON public.caregiver_agency_assoc USING btree (agency_id, caregiver_id)

I would like to receive agency_id and caregiver_id.

CodePudding user response：

You'll have to query the system catalogs:

SELECT d.objid::regclass AS index_name,
       a.attname AS column_name,
       d.refobjid::regclass AS table_name
FROM pg_class AS c
   JOIN pg_depend AS d
      ON c.oid = d.refobjid
   JOIN pg_attribute AS a
      ON d.refobjid = a.attrelid
         AND d.refobjsubid = a.attnum
WHERE d.refclassid = 'pg_class'::regclass
  AND d.classid = 'pg_class'::regclass
  AND c.relnamespace = 'public'::regnamespace
ORDER BY d.refobjid, d.objid;

This will return all indexes in the schema, with the corresponding tables and the columns that occur in their definition.