Problem: I have a table of the customer below, and I want to list column
with indexes in PostgreSQL, Basically purpose of this question is an analysis of
the performance.
CREATE TABLE Customer ( Id serial NOT NULL, FName text COLLATE pg_catalog.default, LName text COLLATE pg_catalog.default, UserName text COLLATE pg_catalog.default, Password text COLLATE pg_catalog.default, Contact text COLLATE pg_catalog.default, Address text COLLATE pg_catalog.default, CONSTRAINT
pk_Customer_Id PRIMARY
KEY (Id) ) |
Solution: Using pg_index we can list the column with index
select t.relname as table, i.relname as index, a.attname as column 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.relkind = 'r' and t.relname like 'Customer%' order by t.relname, i.relname; |
Note: But it will not show the column on which the index is created.
Also, it will show all indexes, but not for a particular table.
No comments:
Post a Comment
Please do not enter any spam link in the comment box.