From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nikhil Ingale <niks.bgm@gmail.com>
cc: pgsql-admin@lists.postgresql.org
Subject: Re: Find out the indexed columns
In-reply-to: 
 <CALXkTpw1Sf_TPLjJxWc-bg--21A8wTb126G=zeGqRGh9GgT3Ww@mail.gmail.com>
References: 
 <CALXkTpw1Sf_TPLjJxWc-bg--21A8wTb126G=zeGqRGh9GgT3Ww@mail.gmail.com>
Comments: In-reply-to Nikhil Ingale <niks.bgm@gmail.com>
	message dated "Mon, 08 May 2023 17:53:32 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <334990.1683552353.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 08 May 2023 09:25:53 -0400
Message-ID: <334991.1683552353@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/334991.1683552353%40sss.pgh.pa.us>
Precedence: bulk

Nikhil Ingale <niks.bgm@gmail.com> writes:
> I have a condition based index created. I see that the postgres metadata
> table doesn't return columns associated with condition based indexes.

No, it gives you back the indexed expression.  Showing only the
columns involved would be very misleading.

> How do I find out the associated columns for such an index?

Probably the most future-proof way is to look into pg_depend to see
what columns the index depends on.  For example:

regression=3D# create table t (f1 float8);
CREATE TABLE
regression=3D# create index ti on t (sin(f1));
CREATE INDEX
regression=3D# select * from pg_depend where classid =3D 'pg_class'::regcl=
ass and objid =3D 'ti'::regclass;
 classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptyp=
e =

---------+-------+----------+------------+----------+-------------+-------=
--
    1259 | 48626 |        0 |       1259 |    48623 |           0 | a
    1259 | 48626 |        0 |       1259 |    48623 |           1 | a
(2 rows)

What we have here is one dependency on the whole table t (the row
with refobjsubid =3D 0) and one on just the used column (refobjsubid =3D 1=
).
(There could be dependencies on things other than tables and columns,
for example any user-defined functions in the expression would have
entries too.)

You could get back the column name(s) by joining to pg_attribute,
along the lines of

regression=3D# select attname from pg_depend join pg_attribute a on (refob=
jid =3D a.attrelid and refobjsubid =3D a.attnum) where classid =3D 'pg_cla=
ss'::regclass and objid =3D 'ti'::regclass and refclassid =3D 'pg_class'::=
regclass;
 attname =

---------
 f1
(1 row)

Don't skip the classid/refclassid constraints.  It might seem to work
without those, but someday you'll hit duplicate-across-catalogs OIDs
and get wrong answers.  On the other hand, I've relied on the join to
get rid of the refobjsubid =3D 0 entry; you could consider filtering
that explicitly as well.

			regards, tom lane