我正在使用Mac Sierra上的Postgres 9.5.1。我在表上创建了以下索引
CREATE INDEX my_object_times_name_gin_trgm_idx
ON my_object_times USING gin (name gin_trgm_ops);
我如何测量该索引在磁盘上占用多少磁盘空间?我想将其与如果我创建另一种类型的索引将被起诉的空间进行比较。请注意,我不在乎表中数据的大小,只有索引占据的存储。
您可以使用pg_relation_size()
,例如:
select pg_relation_size('my_object_times_name_gin_trgm_idx');
大小为字节。如果您希望以"人类可读"方式格式化,则可以使用pg_size_pretty()
select pg_size_pretty(pg_relation_size('my_object_times_name_gin_trgm_idx'));
您会在此处找到一些有趣的脚本:磁盘用法
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END AS SIZE
FROM pg_catalog.pg_database d
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END DESC -- nulls first
LIMIT 20