找出在postgres 9中使用吐司的行或列(并减少吐司使用)？
找出在postgres 9中使用吐司的行或列(并减少吐司使用)？
提问于 2019-11-28 22:35:44
我继承了一个拥有大得离谱的烤面包桌的系统。对于大约400 MB的表和索引大小，toast为631 GB。
clientportal=# SELECT oid, table_schema, table_name,
total_bytes,
pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a order by total_bytes desc;
oid | table_schema | table_name | total_bytes | total | index | toast | table
---------+--------------------+------------------------------------------+--------------+------------+------------+------------+------------
17202 | public | phones | 678297559040 | 632 GB | 119 MB | 631 GB | 280 MB使用对所有表执行pg_size_pretty(sum(pg_column_size($columnName)))的查询，我看不出哪一列可能会使用这么多。因此，我可能遇到了对该功能的限制或误解：
-[ RECORD 1 ]---------+-----------
access_info | 153 kB
activation_date | 1291 kB
autocreate | 835 kB
btn_id | 3409 kB
callerid | 5954 kB
calling_firstname | 1 bytes
calling_lastname | 1 bytes
connectiontype_id | 1 bytes
created_at | 6985 kB
did_supplier_id | 17 kB
e911_notes | 274 kB
e911_tested | 70 bytes
e911_tested_by_id |
e911_tested_date |
email | 2431 kB
extension | 2020 kB
firstname | 152 kB
foreign_pid | 16 kB
foreign_userid | 10 kB
human_validation_date | 176 bytes
huntgroup | 873 kB
id | 3492 kB
incoming_toll | 873 kB
ipaddress | 1 bytes
lastname | 4406 bytes
line_of_business | 305 kB
local_rate_plan_id | 6264 bytes
national_rate_plan_id |
notes | 114 MB
phone_mac_addr | 5041 kB
phone_number | 9656 kB
phone_type_id |
rate_center_id | 4 bytes
service_type | 3786 bytes
sip_password | 22 MB
sip_username | 8919 kB
site_id | 3480 kB
termination_date | 2217 kB
updated_at | 6985 kB
virtualdid | 873 kB
voicemail_number | 1268 kB
voicemailportal | 873 kB
world_rate_plan_id | 我怀疑sip_password或notes列正在将该行发送到吐司。这并不能证明我希望将这些列设置为null将减少吐司的使用，但经过研究后，我假设这不会发生，或者需要一个VACCUM FULL
- 是否有一种方法可以确定哪一列或哪一行正在使用这么大的吐司空间？
- 在将一些文本列设置为null后，我需要运行什么来减少吐司大小？
Update：万一\d+很重要(但它没有完全回答问题)：
clientportal=# \d+ phones
Table "public.phones"
Column | Type | Modifiers | Storage | Stats target | Description
-----------------------+-----------------------------+-----------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('phones_id_seq'::regclass) | plain | |
created_at | timestamp without time zone | | plain | |
updated_at | timestamp without time zone | | plain | |
site_id | integer | | plain | |
btn_id | integer | | plain | |
phone_number | character varying(255) | | extended | |
extension | character varying(255) | | extended | |
voicemail_number | character varying(255) | | extended | |
access_info | character varying(255) | | extended | |
notes | text | | extended | |
activation_date | date | | plain | |
termination_date | date | | plain | |
phone_mac_addr | character varying(255) | | extended | |
phone_type_id | integer | | plain | |
sip_username | character varying(255) | | extended | |
e911_notes | character varying(255) | | extended | |
e911_tested | boolean | | plain | |
e911_tested_by_id | integer | | plain | |
e911_tested_date | date | | plain | |
did_supplier_id | integer | | plain | |
email | character varying(255) | | extended | |
ipaddress | character varying(255) | | extended | |
connectiontype_id | character varying(255) | | extended | |
sip_password | text | | extended | |
virtualdid | boolean | default false | plain | |
voicemailportal | boolean | default false | plain | |
callerid | character varying(255) | default NULL::character varying | extended | |
autocreate | boolean | default false | plain | |
huntgroup | boolean | default false | plain | |
firstname | text | | extended | |
lastname | text | | extended | |
calling_firstname | text | | extended | |
calling_lastname | text | | extended | |
foreign_userId | text | | extended | |
local_rate_plan_id | integer | | plain | |
national_rate_plan_id | integer | | plain | |
world_rate_plan_id | integer | | plain | |
foreign_pid | integer | | plain | |
rate_center_id | integer | | plain | |
human_validation_date | timestamp without time zone | | plain | |
service_type | character varying | | extended | |
line_of_business | character varying | | extended | |
incoming_toll | boolean | default false | plain | |
Indexes:
"phones_pkey" PRIMARY KEY, btree (id)
"index_phones_on_id" UNIQUE, btree (id)
"index_phones_on_btnid" btree (btn_id)
"index_phones_on_extension" btree (extension)
"index_phones_on_foreign_pid" btree (foreign_pid)回答 1
Stack Overflow用户
回答已采纳
发布于 2019-11-29 07:36:43
pg_column_size不测量烤好的值的大小，只测量吐司指针的大小。
但是，您可以通过比较它的输出和使用length()可以得到的实际列大小来利用它的优势。
它可能是varchar(255)列，但主要怀疑是text和character varying (没有长度)列。
如果你没有发现任何这样的方式，也有可能在吐司桌上膨胀。使用pgstattuple扩展来度量它。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59096852
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号