Friday, March 23, 2012

PostgreSQL - Getting the size of an index

I just stumbled upon this gentle piece of info:
http://postgresql.cc/postgres-index-size


How to Get a Postgres Table’s Indexes’ Size

Run the following SQL command, substituting the name of your table, to get the size of that Postgres table’s index data:

postgres=# select pg_size_pretty(pg_total_relation_size('test_table') - pg_relation_size('test_table'));
 pg_size_pretty
----------------
 24 kB
(1 row)

The function pg_total_relation_size() gets the size of both the row data and indexes for the named table. We subtract the size of the tables row data (pg_relation_size()) to get the size of the table’s indexes. The pg_size_pretty() function then puts the size in readable units (bytes, kilobytes, megabytes or gigabytes).


As I intent to explain tomorrow the importance of surrogate keys this tip will be very useful. =-)

No comments:

Post a Comment