How to Find index include list and filter definition of BTree index in PostGreSQL?

Question

I have to create a complete view of BTree index list to analyze the performances of each and to flush out abnormal indexes (includes or equivalent) to drop them...

Nevertheless I cannot find a way to have the list of the columns involved in the INCLUDE part of index definition.

Also I need index filter part for indexes having it, but I don't found any information in the tables and views of the pg_catalog definition...

The query I use to try is this is one:

SELECT --i.indexrelid, 
       s.nspname AS "TABLE_SCHEMA", tc.relname AS "TABLE_NAME",
       ic.relname AS "INDEX_NAME", m.amname as "INDEX_TYPE",
       (SELECT STRING_AGG(a.attname ||  
                        CASE 
                           WHEN indoption[a.attnum-1] = 3 
                              THEN ' DESC' 
                           ELSE ' ASC' 
                        END ||
                        CASE 
                           WHEN i.indcollation[attnum-1] = 0 
                              THEN '' 
                           ELSE ' COLLATE ' ||
                                 (SELECT collname  
                                FROM   pg_collation  
                                WHERE  oid = i.indcollation[attnum-1])
                        END,  ', ') 
        FROM   pg_attribute AS a
        WHERE  a.attrelid = i.indexrelid) AS "KEY_COLUMN_LIST",
       i.indisunique AS "UNIQUE",
       i.indisprimary AS "PRIMARY_KEY",
       i.indisclustered AS "CLUSTERED",
       i.indisvalid AS "VALID",
       CASE k.contype 
          WHEN 'c' THEN 'CHECK'
          WHEN 'f' THEN 'FOREIGN KEY'
          WHEN 'p' THEN 'PRIMARY KEY'
          WHEN 'u' THEN 'UNIQUE'
       END AS "CONSTRAINT_TYPE",

-->      ??? AS "INCLUDE_COLUM_LIST" attnum <= 0 suppose to have all other columns...
       (SELECT STRING_AGG(a2.attname,  ', ') 
        FROM   pg_attribute AS a2
        WHERE  a2.attrelid = i.indexrelid
          AND  a2.attnum <= 0) AS "INCLUDE_COLUM_LIST",
-- does not works

-->   ??? AS "FILTER_DEFINITION"
--> I did not find

       pg_get_indexdef(i.indexrelid) AS "INDEX_DEFINITION"
FROM   pg_index AS i           -- index
       JOIN pg_class AS ic       -- classe index
          ON i.indexrelid = ic.oid
       JOIN pg_class AS tc       -- classe table
          ON i.indrelid = tc.oid          
       JOIN pg_namespace AS s  -- schema
          ON tc.relnamespace = s.oid
       JOIN pg_am AS m         -- type d'index
          ON ic.relam = m.oid
       LEFT OUTER JOIN pg_constraint AS k   -- contraintes associées
          ON i.indexrelid = k.conindid AND i.indkey[0:indnkeyatts-1] = k.conkey
WHERE  m.amname = 'btree'
--  AND  ic.relname LIKE 'x%' --> to match with my sample
ORDER BY 1, 2, 3, 4

For my demo I create this table and those indexes:

CREATE TABLE TX (A INT, B INT, C INT, D INT, E VARCHAR(32), F INT, G DATE, H BOOLEAN)

CREATE INDEX X1 ON TX (A DESC, B ASC, C DESC)

CREATE INDEX X2 ON TX (A, C) INCLUDE (D, E, F)

CREATE INDEX X3 ON TX (B) WHERE G > '2020-01-01'

Of course I could parse the INDEX_DEFINITION but there is no garanty that ever works with a simple SUBSTRING... And I dont' want to incluse Lex and Yacc into a simple query !!! ;-)

Answer 1

Actually I have parse the index_def with this SQL code :

CASE WHEN POSITION(') INCLUDE (' IN pg_get_indexdef(i.indexrelid)) = 0 
               THEN NULL
            WHEN POSITION(') WHERE (' IN pg_get_indexdef(i.indexrelid)) = 0 
               THEN SUBSTRING(pg_get_indexdef(i.indexrelid), 
                              POSITION(') INCLUDE (' 
                                       IN pg_get_indexdef(i.indexrelid)) + 11, 
                              CHARACTER_LENGTH(pg_get_indexdef(i.indexrelid)) 
                                               - POSITION(') INCLUDE (' 
                                                          IN pg_get_indexdef(i.indexrelid)
                                                         ) - 11)
            ELSE SUBSTRING(pg_get_indexdef(i.indexrelid), 
                           POSITION(') INCLUDE (' 
                                    IN pg_get_indexdef(i.indexrelid)) + 11, 
                           POSITION(') WHERE (' 
                                    IN pg_get_indexdef(i.indexrelid)
                                   ) - POSITION(') INCLUDE (' 
                                                IN pg_get_indexdef(i.indexrelid)
                                               ) - 11)
       END AS "INCLUDE_COLUMN_LIST",          
       CASE WHEN POSITION(') WHERE (' IN pg_get_indexdef(i.indexrelid)) = 0 
               THEN NULL
            ELSE SUBSTRING(pg_get_indexdef(i.indexrelid), 
                           POSITION(') WHERE (' 
                                    IN pg_get_indexdef(i.indexrelid)) + 9, 
                           CHARACTER_LENGTH(pg_get_indexdef(i.indexrelid)) 
                              - POSITION(') WHERE (' 
                                         IN pg_get_indexdef(i.indexrelid)) - 9)
       END AS "WHERE_PREDICATE"

But I will surely use @Zegarek solution which seems the best !

Answer 2

The indnkeyatts column in pg_catalog.pg_index can tell you which columns are key or non-key (payload) as described in the create index doc)

indnkeyatts int2

The number of key columns in the index, not counting any included columns, which are merely stored and do not participate in the index semantics

SELECT a.attname,
  a.attnum<=(SELECT i.indnkeyatts 
             FROM pg_catalog.pg_index i 
             WHERE i.indexrelid='X2'::regclass::oid) AS is_key
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'X2'::regclass::oid 
AND a.attnum > 0 
AND NOT a.attisdropped
ORDER BY a.attnum;

attname	is_key
a	T
c	T
d	f
e	f
f	f

As to the index predicate, there's a pg_get_expr() function for that because it's stored as pg_node_tree, not regular text in pg_index:

indpred pg_node_tree

Expression tree (in nodeToString() representation) for partial index predicate. Null if not a partial index.

SELECT pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)
FROM pg_catalog.pg_index i
WHERE i.indexrelid = 'X3'::regclass::oid;

pg_get_expr
g > '2020-01-01'::date

Demo at db<>fiddle with above added to your select returns:

TABLE_SCHEMA	TABLE_NAME	INDEX_NAME	INDEX_TYPE	KEY_COLUMN_LIST	UNIQUE	PRIMARY_KEY	CLUSTERED	VALID	CONSTRAINT_TYPE	INCLUDE_COLUM_LIST	FILTER_DEFINITION	INDEX_DEFINITION
public	tx	x1	btree	a DESC, b ASC, c DESC	f	f	f	t	null	null	null	CREATE INDEX x1 ON public.tx USING btree (a DESC, b, c DESC)
public	tx	x2	btree	a ASC, c ASC	f	f	f	t	null	d, e, f	null	CREATE INDEX x2 ON public.tx USING btree (a, c) INCLUDE (d, e, f)
public	tx	x3	btree	b ASC	f	f	f	t	null	null	g > '2020-01-01'::date	CREATE INDEX x3 ON public.tx USING btree (b) WHERE (g > '2020-01-01'::date)

---

If psql \d+ meta-command can fetch you something, so can you: just start it with -E and it'll begin to print all queries it used to show you the information you request:

-E

--echo-hidden

Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN to on.

The queries above can be obtained from what you get if you do \d+ on any of your indexes with ECHO_HIDDEN set on.