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

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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');

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply