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
Date: Mon, 08 May 2023 09:25:53 -0400
Message-ID: <334991.1683552353@sss.pgh.pa.us> (raw)
In-Reply-To: <CALXkTpw1Sf_TPLjJxWc-bg--21A8wTb126G=zeGqRGh9GgT3Ww@mail.gmail.com>
References: <CALXkTpw1Sf_TPLjJxWc-bg--21A8wTb126G=zeGqRGh9GgT3Ww@mail.gmail.com>
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=# create table t (f1 float8);
CREATE TABLE
regression=# create index ti on t (sin(f1));
CREATE INDEX
regression=# select * from pg_depend where classid = 'pg_class'::regclass and objid = 'ti'::regclass;
classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+-------+----------+------------+----------+-------------+---------
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 = 0) and one on just the used column (refobjsubid = 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=# select attname from pg_depend join pg_attribute a on (refobjid = a.attrelid and refobjsubid = a.attnum) where classid = 'pg_class'::regclass and objid = 'ti'::regclass and refclassid = '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 = 0 entry; you could consider filtering
that explicitly as well.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, niks.bgm@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Find out the indexed columns
In-Reply-To: <334991.1683552353@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox