Table of contents
    blog cover

    How to get table index size in PostgreSQL

    Software Engineer
    Software Engineer
    Indexes play a crucial role in enhancing the performance of database queries. They allow for faster data retrieval by creating a separate data structure pointing to the data stored in a table. However, indexes come with a cost in terms of disk space consumption. Understanding the size of indexes helps you evaluate their impact on storage requirements and overall database performance. In this blog post, we will explore how to get the table index size in PostgreSQL

    To retrieve the index size of all tables in PostgreSQL
    // language: sql
    SELECT
        relname AS object_name,
        pg_size_pretty(pg_relation_size(relid)) AS table_size,
        pg_size_pretty(pg_indexes_size(relid)) AS index_size,
        pg_size_pretty(pg_total_relation_size(relid)) AS total_size
    FROM pg_catalog.pg_statio_user_tables
    ORDER BY pg_total_relation_size(relid) DESC;
    The sample result is
    // language: bash
    "object_name"    "table_size"    "index_size"   "total_size"
    "users"          "8192 bytes"    "64 kB"        "80 kB"
    "admin_users"    "8192 bytes"    "48 kB"        "64 kB"

    To retrieve each index type in the table "users" (you can try with other tables)
    // language: sql
    SELECT i.relname "Table Name",indexrelname "Index Name",
     pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
     pg_size_pretty(pg_indexes_size(relid)) as "Total Size of all Indexes",
     pg_size_pretty(pg_relation_size(relid)) as "Table Size",
     pg_size_pretty(pg_relation_size(indexrelid)) "Index Size"
    FROM pg_stat_all_indexes i JOIN pg_class c ON i.relid=c.oid 
    WHERE i.relname='users'
    The sample result is
    // language: bash
    "Table Name"   "Index Name"                             "Total Size"    "Total Size of all Indexes"    "Table Size"    "Index Size"
    "users"        "users_pkey"                             "80 kB"         "64 kB"                        "8192 bytes"    "16 kB"
    "users"        "index_users_on_email"                   "80 kB"         "64 kB"                        "8192 bytes"    "16 kB"
    "users"        "index_users_on_reset_password_token"    "80 kB"         "64 kB"                        "8192 bytes"    "16 kB"
    "users"        "index_users_on_confirmation_token"      "80 kB"         "64 kB"                        "8192 bytes"    "16 kB"

    Created at 2023-05-22 18:39:58 +0700

    Related blogs