Re: Find out the indexed columns

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


Nikhil Ingale <niks.bgm@xxxxxxxxx> 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);
regression=# create index ti on t (sin(f1));
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;
(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

[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux