Skip to main content

PostgreSQL Show Indexes

Introduction​

Indexes are one of the most powerful performance optimization tools in PostgreSQL. They allow the database to find and retrieve specific rows much faster than it could without them. However, as your database grows, it can be challenging to keep track of all the indexes that have been created. This is where PostgreSQL's index information commands become essential.

In this tutorial, you'll learn:

  • Why viewing index information is important
  • Various commands to show indexes in PostgreSQL
  • How to interpret index information
  • Practical applications for index management

Why Is Viewing Index Information Important?​

Before diving into the commands, let's understand why you might need to view index information:

  1. Performance Optimization: Identifying missing or redundant indexes
  2. Database Maintenance: Tracking index sizes and usage
  3. Troubleshooting: Diagnosing query performance issues
  4. Documentation: Keeping track of your database structure

Commands to Show Indexes in PostgreSQL​

PostgreSQL offers several ways to view information about indexes. Let's explore them one by one.

1. Using \di in psql​

The simplest way to list all indexes in the current database is by using the \di command in the psql client:

sql
\di

Example output:

                       List of relations
 Schema |      Name       | Type  |  Owner   |    Table
--------+-----------------+-------+----------+-------------
 public | idx_users_email | index | postgres | users
 public | users_pkey      | index | postgres | users
 public | posts_pkey      | index | postgres | posts
 public | idx_posts_user  | index | postgres | posts
(4 rows)

You can also use \di+ to see more detailed information including index sizes:

sql
\di+

Example output:

                                     List of relations
 Schema |      Name       | Type  |  Owner   |    Table    |  Size  | Description
--------+-----------------+-------+----------+-------------+--------+-------------
 public | idx_users_email | index | postgres | users       | 16 kB  |
 public | users_pkey      | index | postgres | users       | 16 kB  |
 public | posts_pkey      | index | postgres | posts       | 32 kB  |
 public | idx_posts_user  | index | postgres | posts       | 24 kB  |
(4 rows)

2. Using pg_indexes View​

The pg_indexes system view provides information about all indexes in the database:

sql
SELECT * FROM pg_indexes WHERE tablename = 'users';

Example output:

 schemaname | tablename |    indexname    | tablespace |                                  indexdef
------------+-----------+-----------------+------------+-----------------------------------------------------------------------------
 public     | users     | users_pkey      |            | CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)
 public     | users     | idx_users_email |            | CREATE UNIQUE INDEX idx_users_email ON public.users USING btree (email)
(2 rows)

This view is particularly useful because it shows the full SQL command used to create each index, which can be helpful for recreating the index if needed.

3. Using pg_stat_all_indexes View​

To see statistics about index usage, you can query the pg_stat_all_indexes view:

sql
SELECT 
    schemaname,
    relname AS tablename,
    indexrelname AS indexname,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM 
    pg_stat_all_indexes
WHERE 
    schemaname = 'public'
ORDER BY 
    index_scans DESC;

Example output:

 schemaname | tablename |    indexname    | index_scans | tuples_read | tuples_fetched
------------+-----------+-----------------+-------------+-------------+----------------
 public     | users     | idx_users_email |        1427 |        1427 |           1427
 public     | users     | users_pkey      |         843 |         843 |            843
 public     | posts     | idx_posts_user  |         326 |        1304 |           1304
 public     | posts     | posts_pkey      |          48 |          48 |             48
(4 rows)

This information is valuable for identifying which indexes are being used and which might be candidates for removal.

4. Using Information Schema​

For those who prefer working with the information schema:

sql
SELECT
    t.relname AS table_name,
    i.relname AS index_name,
    a.attname AS column_name
FROM
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
WHERE
    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 = 'users'
ORDER BY
    t.relname,
    i.relname;

Example output:

 table_name | index_name       | column_name
------------+------------------+-------------
 users      | idx_users_email  | email
 users      | users_pkey       | id
(2 rows)

This query is more complex but provides detailed information about which columns are included in each index.

5. Using pg_class and pg_index​

For advanced information about index sizes and structure:

sql
SELECT
    c.relname AS index_name,
    pg_size_pretty(pg_relation_size(c.oid)) AS index_size,
    i.indisunique AS is_unique,
    i.indisprimary AS is_primary,
    i.indisvalid AS is_valid
FROM
    pg_index i
JOIN
    pg_class c ON c.oid = i.indexrelid
JOIN
    pg_class t ON t.oid = i.indrelid
WHERE
    t.relname = 'users';

Example output:

    index_name    | index_size | is_unique | is_primary | is_valid
------------------+------------+-----------+------------+----------
 users_pkey       | 16 kB      | t         | t          | t
 idx_users_email  | 16 kB      | t         | f          | t
(2 rows)

This query provides information about index types, sizes, and validity.

Interpreting Index Information​

Now that you know how to retrieve index information, let's understand what to look for:

Index Types​

PostgreSQL supports several index types:

  • B-tree: The default index type, suitable for most scenarios
  • Hash: Optimized for equality comparisons
  • GiST: Generalized Search Tree, useful for full-text search and spatial data
  • GIN: Generalized Inverted Index, good for composite values like arrays and JSON
  • BRIN: Block Range Index, efficient for large tables with naturally ordered data

You can see the index type in the indexdef column from pg_indexes view:

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

Example output:

    indexname    |                              indexdef
-----------------+--------------------------------------------------------------------
 users_pkey      | CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)
 idx_users_email | CREATE UNIQUE INDEX idx_users_email ON public.users USING btree (email)
(2 rows)

In this example, both indexes are using the B-tree algorithm.

Index Usage Statistics​

To determine if an index is being used efficiently:

sql
SELECT
    s.relname AS table_name,
    i.relname AS index_name,
    pg_size_pretty(pg_relation_size(i.oid)) AS index_size,
    idx_scan AS scans
FROM
    pg_stat_user_indexes ui
JOIN
    pg_index x ON ui.indexrelid = x.indexrelid
JOIN
    pg_class i ON ui.indexrelid = i.oid
JOIN
    pg_class s ON ui.relid = s.oid
WHERE
    s.relname = 'users'
ORDER BY
    scans DESC;

Example output:

 table_name |    index_name    | index_size | scans
------------+------------------+------------+-------
 users      | idx_users_email  | 16 kB      |  1427
 users      | users_pkey       | 16 kB      |   843
(2 rows)

Low scan counts might indicate unused indexes that could be candidates for removal.

Practical Examples and Applications​

Let's explore some practical scenarios where showing indexes is useful.

Example 1: Finding Unused Indexes​

Unused indexes take up space and slow down write operations without providing query benefits. To find them:

sql
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS scans,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM
    pg_stat_user_indexes ui
JOIN
    pg_index i ON ui.indexrelid = i.indexrelid
WHERE
    idx_scan < 10  -- Adjust based on your database usage
    AND NOT i.indisunique  -- Exclude unique indexes which are important for constraints
ORDER BY
    scans, pg_relation_size(i.indexrelid) DESC;

Example output:

 schemaname | table_name |     index_name      | scans | index_size
------------+------------+---------------------+-------+------------
 public     | comments   | idx_comments_date   | 0     | 256 kB
 public     | settings   | idx_settings_value  | 3     | 16 kB
(2 rows)

These indexes might be candidates for removal, but be cautious and investigate why they were created before dropping them.

Example 2: Identifying Duplicate Indexes​

Duplicate indexes waste storage and slow down write operations:

sql
WITH index_cols AS (
    SELECT
        t.relname AS table_name,
        i.relname AS index_name,
        array_agg(a.attname ORDER BY array_position(ix.indkey, a.attnum)) AS columns
    FROM
        pg_class t
    JOIN
        pg_index ix ON t.oid = ix.indrelid
    JOIN
        pg_class i ON ix.indexrelid = i.oid
    JOIN
        pg_attribute a ON t.oid = a.attrelid
    WHERE
        a.attnum = ANY(ix.indkey)
        AND t.relkind = 'r'
        AND t.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
    GROUP BY
        t.relname, i.relname
)
SELECT
    ic1.table_name,
    ic1.index_name,
    ic2.index_name AS duplicate_index,
    ic1.columns
FROM
    index_cols ic1
JOIN
    index_cols ic2 ON
    ic1.table_name = ic2.table_name AND
    ic1.columns = ic2.columns AND
    ic1.index_name < ic2.index_name;

Example output:

 table_name |    index_name    |  duplicate_index  |   columns
------------+------------------+-------------------+-------------
 products   | idx_product_name | product_name_idx  | {name}
(1 row)

This indicates that idx_product_name and product_name_idx are duplicate indexes on the products table.

Example 3: Monitoring Index Bloat​

Over time, indexes can become bloated, taking up more space than necessary:

sql
WITH btree_index_atts AS (
    SELECT
        nspname,
        relname,
        reltuples,
        relpages,
        indrelid,
        indexrelid,
        relam,
        regexp_split_to_table(indkey::text, ' ')::smallint AS attnum
    FROM
        pg_index
    JOIN
        pg_class ON pg_class.oid = pg_index.indexrelid
    JOIN
        pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    JOIN
        pg_am ON pg_class.relam = pg_am.oid
    WHERE
        pg_am.amname = 'btree'
),
index_item_sizes AS (
    SELECT
        i.nspname,
        i.relname,
        i.reltuples,
        i.relpages,
        i.indexrelid,
        current_setting('block_size')::numeric AS bs,
        8 AS maxalign,
        24 AS pagehdr,
        CASE WHEN max(coalesce(pg_stats.null_frac, 0)) = 0
            THEN 2
            ELSE 6
        END AS indexheader,
        sum((1 - coalesce(pg_stats.null_frac, 0)) * coalesce(pg_stats.avg_width, 1024)) AS nulldatawidth
    FROM
        btree_index_atts i
    JOIN
        pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = i.attnum
    JOIN
        pg_stats ON pg_stats.schemaname = i.nspname
        AND pg_stats.tablename = i.relname
        AND pg_stats.attname = a.attname
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8
)
SELECT
    nspname AS schema_name,
    relname AS index_name,
    round(relpages::numeric * bs / (1024 * 1024), 2) AS size_mb,
    round(((relpages - (reltuples * (6 + maxalign - 
        CASE WHEN indexheader % maxalign = 0 THEN maxalign
            ELSE indexheader % maxalign
        END + nulldatawidth + 8)) / 
        (bs - pagehdr)::float) / relpages) * 100, 2) AS bloat_pct
FROM
    index_item_sizes
WHERE
    relpages > 10
    AND nspname = 'public'
ORDER BY
    bloat_pct DESC
LIMIT 10;

Example output (simplified):

 schema_name |    index_name    | size_mb | bloat_pct
-------------+------------------+---------+-----------
 public      | idx_order_date   | 45.12   | 28.45
 public      | idx_user_login   | 16.76   | 19.21
(2 rows)

Indexes with high bloat percentages might be candidates for reindexing.

Summary​

In this tutorial, you've learned multiple ways to view and analyze PostgreSQL indexes:

  1. Using \di and \di+ in psql for quick listings
  2. Querying the pg_indexes view for index definitions
  3. Using pg_stat_all_indexes for usage statistics
  4. Working with information schema for detailed column information
  5. Leveraging pg_class and pg_index for advanced index details

You've also learned practical applications for these commands, including:

  • Finding unused indexes
  • Identifying duplicate indexes
  • Monitoring index bloat

Properly managing your database indexes is crucial for maintaining optimal performance. By regularly reviewing your index information, you can ensure your PostgreSQL database runs efficiently.

Additional Resources​

Exercises​

  1. Find all indexes on tables in your database that have not been used since the last database restart.
  2. Identify the three largest indexes in your database and determine if they're being used efficiently.
  3. Create a regular maintenance script that reports on index bloat and unused indexes.
  4. For a specific table in your database, list all indexes and their creation commands.
  5. Find all partial indexes in your database (hint: look for WHERE clauses in the index definitions).

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)