Each directory represents a database (created via create database). The number is the oid of the database. To see the oid and its name, run the following statement: sachi=# select oid, datname from pg_database; oid | datname -------+----------- 16992 | gpadmin 10900 | postgres 1 | template1 10899 | template0 33476 | himanshu 16993 | sachi 33553 | gpperfmon (7 rows) sachi=# Inside each directory each file corresponds to the an entry in pg_class where the oid matches the number of the file in the directory. You can see the oids and to which relation they relate by running the statement: PSQL>select cl.relfilenode, nsp.nspname as schema_name, cl.relname, cl.relkind from pg_class cl join pg_namespace nsp on cl.relnamespace = nsp.oid; sachi=# select cl.relfilenode, nsp.nspname as schema_name, cl.relname, cl.relkind from pg_class cl join pg_namespace nsp on cl.relnamespace = nsp.oid and cl.relname like 'gp_%'; relfilenode | schema_name | relname | relkind -------------+-------------+------------------------------------------------+--------- 10873 | pg_catalog | gp_pgdatabase | v 10876 | pg_catalog | gp_distributed_xacts | v 10879 | pg_catalog | gp_transaction_log | v 10882 | pg_catalog | gp_distributed_log | v 6429 | pg_catalog | gp_verification_history | r 5008 | pg_catalog | gp_master_mirroring | r 5043 | pg_catalog | gp_fastsequence | r 5096 | pg_catalog | gp_global_sequence | r 5090 | pg_catalog | gp_persistent_relation_node | r 5094 | pg_catalog | gp_relation_node | r 5091 | pg_catalog | gp_persistent_database_node | r 5092 | pg_catalog | gp_persistent_tablespace_node | r 5093 | pg_catalog | gp_persistent_filespace_node | r 16497 | gp_toolkit | gp_log_system | v 16500 | gp_toolkit | gp_log_database | v 16503 | gp_toolkit | gp_log_master_concise | v 16506 | gp_toolkit | gp_log_command_timings | v 16520 | gp_toolkit | gp_param_setting_t | c 16535 | gp_toolkit | gp_param_settings_seg_value_diffs | v 16538 | gp_toolkit | gp_pgdatabase_invalid | v 16552 | gp_toolkit | gp_skew_details_t | c 16576 | gp_toolkit | gp_skew_analysis_t | c 16590 | gp_toolkit | gp_skew_coefficients | v 16595 | gp_toolkit | gp_skew_idle_fractions | v 16598 | gp_toolkit | gp_stats_missing | v 16612 | gp_toolkit | gp_disk_free | r 16624 | gp_toolkit | gp_bloat_expected_pages | v 16628 | gp_toolkit | gp_bloat_diag | v 16631 | gp_toolkit | gp_resq_activity | v 6111 | pg_catalog | gp_san_config_mountid_index | i 6101 | pg_catalog | gp_configuration_content_definedprimary_index | i 6102 | pg_catalog | gp_configuration_dbid_index | i 6108 | pg_catalog | gp_db_interfaces_dbid_index | i 6109 | pg_catalog | gp_interfaces_interface_index | i 16634 | gp_toolkit | gp_resq_activity_by_queue | v 6106 | pg_catalog | gp_segment_config_content_preferred_role_index | i 6107 | pg_catalog | gp_segment_config_dbid_index | i 16637 | gp_toolkit | gp_resq_priority_backend | v 16640 | gp_toolkit | gp_resq_priority_statement | v 16646 | gp_toolkit | gp_locks_on_resqueue | v 6431 | pg_catalog | gp_verification_history_vertoken_index | i 16643 | gp_toolkit | gp_resq_role | v 16649 | gp_toolkit | gp_locks_on_relation | v 5000 | pg_catalog | gp_configuration | r 5006 | pg_catalog | gp_configuration_history | r 5029 | pg_catalog | gp_db_interfaces | r 5030 | pg_catalog | gp_interfaces | r 5001 | pg_catalog | gp_id | r 5002 | pg_catalog | gp_distribution_policy | r 5003 | pg_catalog | gp_version_at_initdb | r 5036 | pg_catalog | gp_segment_configuration | r 5039 | pg_catalog | gp_fault_strategy | r 5035 | pg_catalog | gp_san_configuration | r 63239 | pg_catalog | gp_fastsequence_objid_objmod_index | i 63249 | pg_catalog | gp_relation_node_index | i 16652 | gp_toolkit | gp_roles_assigned | v 16655 | gp_toolkit | gp_size_of_index | v 16658 | gp_toolkit | gp_size_of_table_disk | v 16661 | gp_toolkit | gp_size_of_table_uncompressed | v 16664 | gp_toolkit | gp_table_indexes | v 16667 | gp_toolkit | gp_size_of_all_table_indexes | v 16670 | gp_toolkit | gp_size_of_table_and_indexes_disk | v 16673 | gp_toolkit | gp_size_of_table_and_indexes_licensing | v 16676 | gp_toolkit | gp_size_of_partition_and_indexes_disk | v 16679 | gp_toolkit | gp_size_of_schema_disk | v 16682 | gp_toolkit | gp_size_of_database | v 16685 | gp_toolkit | gp_resqueue_status | v 63324 | pg_catalog | gp_policy_localoid_index | i 44088 | public | gp_log_master_ext | r (69 rows) sachi=# --More-- sachi=# select * from pg_catalog.pg_filespace_entry; fsefsoid | fsedbid | fselocation ----------+---------+------------------------ 3052 | 1 | /home/gpmaster/gpsne-1 3052 | 2 | /disk1/gpdata1/gpsne0 3052 | 3 | /disk2/gpdata2/gpsne1 (3 rows) sachi=# select * from pg_catalog.pg_filespace; fsname | fsowner -----------+--------- pg_system | 10 (1 row) sachi=# To show the space used by TOAST tables, use a query like the following: SELECT relname, relpages FROM pg_class, (SELECT reltoastrelid FROM pg_class WHERE relname = 'customer') AS ss WHERE oid = ss.reltoastrelid OR oid = (SELECT reltoastidxid FROM pg_class WHERE oid = ss.reltoastrelid) ORDER BY relname; relname | relpages ----------------------+---------- pg_toast_16806 | 0 pg_toast_16806_index | 1 You can easily display index sizes, too: SELECT c2.relname, c2.relpages
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'customer' AND
c.oid = i.indrelid AND
c2.oid = i.indexrelid
ORDER BY c2.relname;
relname | relpages
----------------------+----------
customer_id_indexdex | 26
It is easy to find your largest tables and indexes using this information: SELECT relname, relpages
FROM pg_class
ORDER BY relpages DESC;
relname | relpages
----------------------+----------
bigtable | 3290
customer | 3144 |
|
|