Check how many Bytes does my index need to be stored?



  • Let's suppose we have this table:

    CREATE TABLE CUSTOMER
     ( 
    C_CUSTKEY INTEGER PRIMARY KEY ,
    C_NAME CHAR (25),
    C_ADDRESS CHAR (40),
    C_NATIONKEY INTEGER REFERENCES NATION(N_NATIONKEY),
    C_PHONE CHAR (15),
    C_ACCTBAL NUMERIC (12,2),
    C_MKTSEGMENT CHAR (10),
    C_COMMENT CHAR (117)
    ) 
    

    as you can see there is a PRIMARY KEY on C_CUSTKEY attribute.

    • How can I check how many bytes do that index need to be stored?

    I am using

    SELECT
        pg_size_pretty (pg_indexes_size('customer'));
    

    returning 32MB. Is that correct? Also, I am using pg_table_size to check how many Bytes does a materialized view need to be stored.



  • pg_indexes_size returns the size of all indexes on the table.

    To get the size of the primary key index alone, you first have to find its name (probably customer_pkey). Then you can run

    SELECT pg_relation_size('customer_pkey');
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2