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.

Fatal Error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails.

Problem: Sometimes, we get the following error when trying to make a connection in Rails.  FATAL ERROR: Peer authentication failed for us...