List Indexes - (PostgreSQL Indexes)

Indexes are used to improve the performance of database queries by allowing them to find the required data quickly. In PostgreSQL, we can list all the indexes currently defined on a table by executing a query. In this tutorial, we'll show you how to use the list indexes query to display the index information for a table.

Syntax

SELECT
    i.indname AS index_name,
    pg_get_indexdef(i.indexrelid) AS index_definition,
    i.indrelid::regclass AS table_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM
    pg_index x
    INNER JOIN
    pg_class c ON c.oid = x.indrelid
    INNER JOIN
    pg_class i ON i.oid = x.indexrelid
WHERE
    c.relname = 'table_name'
ORDER BY
    c.relname,
    i.indisprimary DESC,
    i.indisunique DESC,
    i.indname;

• i.indname: The name of the index.
• pg_get_indexdef(i.indexrelid): The definition of the index.
• i.indrelid::regclass: The name of the table the index is defined on.
• pg_size_pretty(pg_relation_size(i.indexrelid)): The size of the index.

Example

Let's take a look at an example of using the list indexes query to display the index information for a particular table.

SELECT
    i.indname AS index_name,
    pg_get_indexdef(i.indexrelid) AS index_definition,
    i.indrelid::regclass AS table_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM
    pg_index x
    INNER JOIN
    pg_class c ON c.oid = x.indrelid
    INNER JOIN
    pg_class i ON i.oid = x.indexrelid
WHERE
    c.relname = 'employees'
ORDER BY
    c.relname,
    i.indisprimary DESC,
    i.indisunique DESC,
    i.indname;

Output

The output of the list indexes query will display the following columns:

• index_name: The name of the index.
• index_definition: The definition of the index.
• table_name: The name of the table the index is defined on.
• index_size: The size of the index.

Here's an example of what the output might look like:

 index_name  |                     index_definition                     | table_name | index_size 
-------------+----------------------------------------------------------+------------+------------
 employees_pkey | CREATE UNIQUE INDEX employees_pkey ON employees USING btree (emp_id) | employees | 16 kB
 employees_name_index | CREATE INDEX employees_name_index ON employees USING btree (emp_name) | employees | 8192 bytes
(2 rows)

Explanation

In the above example, we used the list indexes query to display the index information for the employees table.

The query joins three tables: pg_index, pg_class, and pg_class. It retrieves the name of the index, the definition of the index, the name of the table the index is defined on, and the size of the index.

The WHERE clause of the query restricts the results to only the indexes on the employees table.

Use

The list indexes query is useful for identifying the indexes defined on a table in PostgreSQL. This information can be used to optimize the performance of queries on that table.

Important Points

• The list indexes query can only be used to display the indexes defined on a single table.
• The pg_get_indexdef() function is used to display the definition of the index.
• The pg_size_pretty() function is used to display the size of the index in a human-readable format.

Summary

In this tutorial, we showed you how to use the list indexes query in PostgreSQL to display the index information for a table. We discussed the syntax, example, output, explanation, use, and important points of using the list indexes query. With this knowledge, you can now easily identify and optimize indexes on tables in your PostgreSQL database.