> This page location: PostgreSQL Indexes > Display Indexes
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL List Indexes

**Info:** Listing indexes with the pg_indexes view or psql's \d command works the same on any PostgreSQL deployment, so everything here carries over directly to Postgres running anywhere. If you're an enterprise looking for managed Postgres in the AI era, [Lakebase](https://www.databricks.com/product/lakebase) delivers the performance, security, and native Lakehouse integration you need. If you're a developer or startup who wants to ship and scale quickly, [Neon](https://neon.com) is the Postgres platform built for that pace.

**Summary**: in this tutorial, you will learn how to list indexes from a PostgreSQL database by using either `pg_indexes` view or `psql` command.

PostgreSQL does not provide a command like [`SHOW INDEXES`](http://www.mysqltutorial.org/mysql-index/mysql-show-indexes/) to list the index information of a table or database.

However, it does provide you with access to the `pg_indexes` view so that you can query the index information.

If you use the `psql` program to interact with the PostgreSQL database, you can use the `\d` command to view the index information for a table.

## PostgreSQL List Indexes using pg_indexes View

The `pg_indexes` view allows you to access useful information on each index in the PostgreSQL database.

The `pg_indexes` view consists of five columns:

- `schemaname`: stores the name of the schema that contains tables and indexes.
- `tablename`: indicates the name of the table to which the index belongs.
- `indexname`: represents the name of the index.
- `tablespace`: identifies the name of the tablespace that contains indexes.
- `indexdef`: contains the index definition command in the form of [`CREATE INDEX`](https://neon.com/postgresql/indexes/postgresql-create-index) statement.

The following statement lists all indexes of the schema `public` in the current database:

```sql
SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'public'
ORDER BY
    tablename,
    indexname;
```

Output:

```text
     tablename      |                      indexname                      |                                                                   indexdef
--------------------+-----------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------
 accounts           | accounts_email_key                                  | CREATE UNIQUE INDEX accounts_email_key ON public.accounts USING btree (email)
 accounts           | accounts_pkey                                       | CREATE UNIQUE INDEX accounts_pkey ON public.accounts USING btree (user_id)
 accounts           | accounts_username_key                               | CREATE UNIQUE INDEX accounts_username_key ON public.accounts USING btree (username)
 actor              | actor_pkey                                          | CREATE UNIQUE INDEX actor_pkey ON public.actor USING btree (actor_id)
 actor              | idx_actor_first_name                                | CREATE INDEX idx_actor_first_name ON public.actor USING btree (first_name)
 actor              | idx_actor_last_name                                 | CREATE INDEX idx_actor_last_name ON public.actor USING btree (last_name)
...
```

To show all the indexes of a table, you use the following statement:

```sql
SELECT
  indexname,
  indexdef
FROM
  pg_indexes
WHERE
  tablename = 'table_name';
```

For example, to list all the indexes for the `customer` table, you use the following statement:

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

Here is the output:

```php
     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 a list of indexes for tables whose names start with the letter `c`, you can use the following query:

```sql
SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename LIKE 'c%'
ORDER BY
    tablename,
    indexname;
```

The following shows the output:

```php
 tablename  |     indexname     |                                      indexdef
------------+-------------------+------------------------------------------------------------------------------------
 categories | categories_pkey   | CREATE UNIQUE INDEX categories_pkey ON public.categories USING btree (category_id)
 category   | category_pkey     | CREATE UNIQUE INDEX category_pkey ON public.category USING btree (category_id)
 city       | city_pkey         | CREATE UNIQUE INDEX city_pkey ON public.city USING btree (city_id)
 city       | idx_fk_country_id | CREATE INDEX idx_fk_country_id ON public.city USING btree (country_id)
 country    | country_pkey      | CREATE UNIQUE INDEX country_pkey ON public.country USING btree (country_id)
 customer   | customer_pkey     | CREATE UNIQUE INDEX customer_pkey ON public.customer USING btree (customer_id)
 customer   | idx_fk_address_id | CREATE INDEX idx_fk_address_id ON public.customer USING btree (address_id)
 customer   | idx_fk_store_id   | CREATE INDEX idx_fk_store_id ON public.customer USING btree (store_id)
 customer   | idx_last_name     | CREATE INDEX idx_last_name ON public.customer USING btree (last_name)
(9 rows)

```

## PostgreSQL List Indexes using psql command

If you use `psql` to connect to a PostgreSQL database and want to list all indexes of a table, you can use the `\d` [psql command](../postgresql-administration/psql-commands) as follows:

```plaintext
\d table_name
```

The command will return all information about the table including the table's structure, indexes, constraints, and [triggers](../postgresql-triggers).

For example, the following statement returns detailed information about the `customer` table:

```plaintext
\d customer
```

The output is:

```plaintext
                                             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
Referenced by:
    TABLE "payment" CONSTRAINT "payment_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT
    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()

```

The output shows the index of the table under the **Indexes** section.

## Summary

- Query data from the `pg_indexes` view to retrieve the index information.
- Use the `\d table_name` command to display the table information along with indexes.

---

## Related docs (PostgreSQL Indexes)

- [Create Indexes](https://neon.com/postgresql/indexes/create-index)
- [Unique Indexes](https://neon.com/postgresql/indexes/unique-index)
- [Indexes on Expressions](https://neon.com/postgresql/indexes/index-on-expression)
- [Partial Indexes](https://neon.com/postgresql/indexes/partial-index)
- [Multicolumn Indexes](https://neon.com/postgresql/indexes/multicolumn-indexes)
- [Reindexing with REINDEX](https://neon.com/postgresql/indexes/reindex)
- [Drop Indexes](https://neon.com/postgresql/indexes/drop-index)
- [Types of Indexes](https://neon.com/postgresql/indexes/index-types)
- [Full Text Search](https://neon.com/postgresql/indexes/full-text-search)
- [JSON Index](https://neon.com/postgresql/indexes/json-index)