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.





    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



    t.relname as table,

    i.relname as index,

    a.attname as column


    pg_class t,

    pg_class i,

    pg_index ix,

    pg_attribute a


    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




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

how to use coalesce in PostgreSQL?

To use the `COALESCE` function in PostgreSQL, follow these steps: 1. Start by writing a SELECT statement or any other query where you want t...