The \di command can be used to list indexes in the current database when using psql (PostgreSQL’s command-line tool). This meta-command can display all indexes, or just indexes that match certain patterns. Here’s an in-depth explanation of how \di works, its options, and examples of its various uses.

Basic Usage

The \di command lists all indexes in the current database, along with details about them. By default, it returns the schema name, index name, associated table, and index type.

Example:

\di

This will output something like:

                            List of relations
 Schema |          Name           | Type  |  Owner   |       Table        
--------+-------------------------+-------+----------+--------------------
 public | actors_pkey             | index | postgres | actors
 public | authors_pkey            | index | postgres | authors
...

Filtering by Schema

You can restrict the list of indexes to a specific schema by using the schema name prefixed by a dot (.).

Example:

\di public.*

This command lists all indexes in the public schema only.

Without specifying the schema, the pattern matches only indexes that are visible in the current schema search path.

Filtering by Pattern

You can provide a pattern to match against index names by using the \di pattern syntax.

Example:

\di *pkey

That will list all indexes that end with pkey, which typically denotes primary key indexes (at least, ones where the primary key was given a default name by Postgres).

Another example:

\di tasks*

That will list all indexes that start with tasks.

Detailed Index Information

To get more detailed information about a specific index, you can add a plus sign (+), so that it becomes \di+. This displays additional details like the index’s size on disk, its access method, etc.

Example:

\di+ *tasks*

Example output:

                                           List of relations
 Schema |      Name      | Type  | Owner  | Table | Persistence | Access method |  Size  | Description 
--------+----------------+-------+--------+-------+-------------+---------------+--------+-------------
 public | idx_all_tasks  | index | barney | tasks | permanent   | btree         | 328 kB | 
 public | idx_open_tasks | index | barney | tasks | permanent   | btree         | 184 kB | 
 public | tasks_pkey     | index | barney | tasks | permanent   | btree         | 240 kB | 

Combining \di with Other Filters

You can combine the schema and pattern filters for more refined searches. For example, to list all indexes in the public schema that start with tasks:

\di public.tasks*

A \di Alternative

Although \di is very useful for quickly getting index information, sometimes you might want more information or to be able to filter on different criteria. Querying system catalog views such as pg_indexes can provide you with more flexibility.

Example:

SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename = 'tasks';

Example output:

 schemaname | tablename |   indexname    |                                                      indexdef                                                       
------------+-----------+----------------+---------------------------------------------------------------------------------------------------------------------
 public     | tasks     | tasks_pkey     | CREATE UNIQUE INDEX tasks_pkey ON public.tasks USING btree (task_id)
 public     | tasks     | idx_open_tasks | CREATE INDEX idx_open_tasks ON public.tasks USING btree (task_name, due_date) WHERE ((status)::text = 'Open'::text)
 public     | tasks     | idx_all_tasks  | CREATE INDEX idx_all_tasks ON public.tasks USING btree (task_name, due_date)

Practical Example

Here’s an example that creates the schema, tables and indexes so that you can run various \di command variations and see the results.

First, run the following script to create a new schema and put a table in it, along with some indexes:

-- Create the sales schema
CREATE SCHEMA IF NOT EXISTS sales;

-- Create the orders table
CREATE TABLE sales.orders (
    order_id SERIAL PRIMARY KEY,             -- Primary key index (orders_pkey)
    customer_id INT NOT NULL,                -- Will have a foreign key index
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL,             -- This will be used in a partial index
    total_amount NUMERIC(10, 2) NOT NULL
);

-- Create a foreign key index on customer_id (orders_customer_id_idx)
CREATE INDEX orders_customer_id_idx
ON sales.orders (customer_id);

-- Create a partial index on status column (orders_status_idx)
-- This index is specifically for 'active' orders.
CREATE INDEX orders_status_idx
ON sales.orders (status)
WHERE status = 'active';

This script creates a sales schema and puts a table called orders in it with the following indexes:

• A primary key index: orders_pkey
• A foreign key index: orders_customer_id_idx
• A partial index: orders_status_idx (on status = 'active')

Here are some examples of using \di with different arguments to return information about the indexes on that table:

1. List only indexes that are visible in the current schema search path:

   \di

2. List all indexes in the sales schema:

   \di sales.*

3. List indexes that start with orders_:

   \di sales.orders_*

4. Get detailed information on orders_status_idx:

   \di+ sales.orders_status_idx

By using these different variations, you can easily explore and manage indexes in your PostgreSQL database. It’s also possible to use regular expressions as a pattern. See the PostgreSQL documentation for more detail on the patterns that can be used with the \di command.