PostgreSQL show indexes

This article describes how to show indexes of a table in PostgreSQL.

In PostgreSQL, you have two methods to view a table’s indexes:

  1. Use the \d command view the index of the table.
  2. Retrieve index information from the pg_indexes view.

Show PostgreSQL indexes using psql command

If you use the psql tool manage the PostgreSQL database, you can use the psql command \d to view indexes on the specified table, as follows:

\d table_name

This \d command will descripe a table, including the table’s structure, indexes, constraints, and triggers.

For example, the following statement returns details of the customer table in the Sakila sample database:

\d customer

                                             Table "public.customer"
   Column    |            Type             | Collation | Nullable |                    Default
-------------+-----------------------------+-----------+----------+-----------------------------------------------
 customer_id | integer                     |           | not null | nextval('customer_customer_id_seq'::regclass)
 store_id    | smallint                    |           | not null |
 first_name  | character varying(45)       |           | not null |
 last_name   | character varying(45)       |           | not null |
 email       | character varying(50)       |           |          |
 address_id  | smallint                    |           | not null |
 activebool  | boolean                     |           | not null | true
 create_date | date                        |           | not null | 'now'::text::date
 last_update | timestamp without time zone |           |          | now()
 active      | integer                     |           |          |
Indexes:
    "customer_pkey" PRIMARY KEY, btree (customer_id)
    "idx_fk_address_id" btree (address_id)
    "idx_fk_store_id" btree (store_id)
    "idx_last_name" btree (last_name)
Foreign-key constraints:
    "customer_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(address_id) ON UPDATE CASCADE ON DELETE RESTRICT
    "customer_store_id_fkey" FOREIGN KEY (store_id) REFERENCES store(store_id) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
    TABLE "payment" CONSTRAINT "payment_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "payment_p2007_01" CONSTRAINT "payment_p2007_01_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    TABLE "payment_p2007_02" CONSTRAINT "payment_p2007_02_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    TABLE "payment_p2007_03" CONSTRAINT "payment_p2007_03_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    TABLE "payment_p2007_04" CONSTRAINT "payment_p2007_04_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    TABLE "payment_p2007_05" CONSTRAINT "payment_p2007_05_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    TABLE "payment_p2007_06" CONSTRAINT "payment_p2007_06_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
    TABLE "rental" CONSTRAINT "rental_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    last_updated BEFORE UPDATE ON customer FOR EACH ROW EXECUTE FUNCTION last_updated()

Among them, the Indexes part contains all the indexes in the table.

Show PostgreSQL indexes using pg_indexes view

PostgreSQL’s built-in pg_indexes view allow you to access useful information about each index in your PostgreSQL database. The pg_indexes view consists of five columns:

  • schemaname: The name of the schema containing the tables and indexes.
  • tablename: The name of the table to which the index belongs.
  • indexname: The name of the index.
  • tablespace: The name of the tablespace where the index is stored.
  • indexdef: The index definition commands as a CREATE INDEX statement.

For example, to list all indexes on the customer table, use the following statement:

SELECT
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename = 'customer';

     indexname     |                                    indexdef
-------------------+--------------------------------------------------------------------------------
 customer_pkey     | CREATE UNIQUE INDEX customer_pkey ON public.customer USING btree (customer_id)
 idx_fk_address_id | CREATE INDEX idx_fk_address_id ON public.customer USING btree (address_id)
 idx_fk_store_id   | CREATE INDEX idx_fk_store_id ON public.customer USING btree (store_id)
 idx_last_name     | CREATE INDEX idx_last_name ON public.customer USING btree (last_name)
(4 rows)

If you want to get all indexes in the PostgreSQL database, you can leave out the WHERE clause.

Conclusion

This article shows two methods to show indexes of a table in PostgreSQL.