How to List columns with indexes in PostgreSQL?


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;

 


Using command \di

\di command is the easiest way to list all indexes in the current database.



As we know, command \d is used to list all the relations in the current database. \di is used to list the database indexes.

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.

Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...