pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFind out the indexed columns
5+ messages / 2 participants
[nested] [flat]
* Find out the indexed columns
@ 2023-05-08 12:23 Nikhil Ingale <niks.bgm@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Nikhil Ingale @ 2023-05-08 12:23 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi All,
I have a condition based index created. I see that the postgres metadata
table doesn't return columns associated with condition based indexes.
Example:
ssdb=# SELECT indexdef FROM pg_indexes WHERE indexname = 'test_u01';
indexdef
------------------------------------------------------------------
CREATE UNIQUE INDEX test_idx1 ON test USING btree ((+
CASE inet_type +
WHEN 2 THEN a +
WHEN 4 THEN a +
ELSE NULL::character varying +
END), ( +
CASE inet_type +
WHEN 2 THEN ipaddress +
WHEN 4 THEN ipaddress +
ELSE NULL::character varying +
END), ( +
CASE inet_type +
WHEN 2 THEN port +
WHEN 4 THEN port +
ELSE NULL::bigint +
END))
(1 row)
The below query is unable to find the indexed columns (a,ipadress,port)
through the metadata tables for the above expression based index.
SELECT ix.indisprimary as contype,pg_get_userbyid(t.relowner) as
table_owner, t.relname as table_name, pg_get_userbyid(i.relowner) as
index_owner, i.relname as index_name, a.attname as att_name,
pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type FROM pg_class
t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND
i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
AND i.relname = 'test_idx1';
How do I find out the associated columns for such an index?
Regards,
nik
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Find out the indexed columns
@ 2023-05-08 13:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Nikhil Ingale <niks.bgm@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-05-08 13:25 UTC (permalink / raw)
To: Nikhil Ingale <niks.bgm@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Find out the indexed columns
@ 2023-05-08 13:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-05-08 13:38 UTC (permalink / raw)
To: Nikhil Ingale <niks.bgm@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
I wrote:
> You could get back the column name(s) by joining to pg_attribute,
Oh, another possible approach is to use pg_describe_object,
which'd be interesting if you also want to handle non-column
dependencies. For example,
regression=# drop table t;
DROP TABLE
regression=# create table t(f1 text);
CREATE TABLE
regression=# create index ti on t (fipshash(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 | 48632 | 0 | 1259 | 48627 | 0 | a
1259 | 48632 | 0 | 1259 | 48627 | 1 | a
1259 | 48632 | 0 | 1255 | 16501 | 0 | n
(3 rows)
regression=# select pg_describe_object(refclassid, refobjid, refobjsubid) from pg_depend where classid = 'pg_class'::regclass and objid = 'ti'::regclass;
pg_describe_object
-------------------------
table t
column f1 of table t
function fipshash(text)
(3 rows)
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Find out the indexed columns
@ 2023-05-08 16:22 Nikhil Ingale <niks.bgm@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Nikhil Ingale @ 2023-05-08 16:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
This is very helpful for me. Thank you very much Tom.
On Mon, May 8, 2023 at 7:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I wrote:
> > You could get back the column name(s) by joining to pg_attribute,
>
> Oh, another possible approach is to use pg_describe_object,
> which'd be interesting if you also want to handle non-column
> dependencies. For example,
>
> regression=# drop table t;
> DROP TABLE
> regression=# create table t(f1 text);
> CREATE TABLE
> regression=# create index ti on t (fipshash(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 | 48632 | 0 | 1259 | 48627 | 0 | a
> 1259 | 48632 | 0 | 1259 | 48627 | 1 | a
> 1259 | 48632 | 0 | 1255 | 16501 | 0 | n
> (3 rows)
>
> regression=# select pg_describe_object(refclassid, refobjid, refobjsubid)
> from pg_depend where classid = 'pg_class'::regclass and objid =
> 'ti'::regclass;
> pg_describe_object
> -------------------------
> table t
> column f1 of table t
> function fipshash(text)
> (3 rows)
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Find out the indexed columns
@ 2023-05-15 05:26 Nikhil Ingale <niks.bgm@gmail.com>
parent: Nikhil Ingale <niks.bgm@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Nikhil Ingale @ 2023-05-15 05:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
Hi Tom and All,
Below query is not listing the particular expression based index created.
SELECT distinct i.relname as index_name, ix.indisprimary as contype FROM
pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid =
ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum =
ANY(ix.indkey) AND t.relname = 'test' and ix.indisunique = 't' AND
ix.indisprimary = 'f' AND a.attname <> 'sequence_number'
Example:
prod=# SELECT indexdef FROM pg_indexes WHERE indexname = 'test_u01';
indexdef
------------------------------------------------------------------
CREATE UNIQUE INDEX test_idx1 ON test USING btree ((+
CASE inet_type +
WHEN 2 THEN a +
WHEN 4 THEN a +
ELSE NULL::character varying +
END), ( +
CASE inet_type +
WHEN 2 THEN ipaddress +
WHEN 4 THEN ipaddress +
ELSE NULL::character varying +
END), ( +
CASE inet_type +
WHEN 2 THEN port +
WHEN 4 THEN port +
ELSE NULL::bigint +
END))
(1 row)
How do I list all the indexes created based on CASE statements or
expressions?
On Mon, May 8, 2023 at 9:52 PM Nikhil Ingale <niks.bgm@gmail.com> wrote:
> This is very helpful for me. Thank you very much Tom.
>
> On Mon, May 8, 2023 at 7:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> I wrote:
>> > You could get back the column name(s) by joining to pg_attribute,
>>
>> Oh, another possible approach is to use pg_describe_object,
>> which'd be interesting if you also want to handle non-column
>> dependencies. For example,
>>
>> regression=# drop table t;
>> DROP TABLE
>> regression=# create table t(f1 text);
>> CREATE TABLE
>> regression=# create index ti on t (fipshash(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 | 48632 | 0 | 1259 | 48627 | 0 | a
>> 1259 | 48632 | 0 | 1259 | 48627 | 1 | a
>> 1259 | 48632 | 0 | 1255 | 16501 | 0 | n
>> (3 rows)
>>
>> regression=# select pg_describe_object(refclassid, refobjid, refobjsubid)
>> from pg_depend where classid = 'pg_class'::regclass and objid =
>> 'ti'::regclass;
>> pg_describe_object
>> -------------------------
>> table t
>> column f1 of table t
>> function fipshash(text)
>> (3 rows)
>>
>> regards, tom lane
>>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-05-15 05:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-08 12:23 Find out the indexed columns Nikhil Ingale <niks.bgm@gmail.com>
2023-05-08 13:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-08 13:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-08 16:22 ` Nikhil Ingale <niks.bgm@gmail.com>
2023-05-15 05:26 ` Nikhil Ingale <niks.bgm@gmail.com>
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