首页 > 代码库 > 统计对象大小函数性能比较
统计对象大小函数性能比较
gtgpdb=# select * from gp_toolkit.gp_size_of_table_disk t where sotdtablename like 'rt_ok%';
sotdoid | sotdsize | sotdtoastsize | sotdadditionalsize | sotdschemaname | sotdtablename
---------+-------------+---------------+--------------------+----------------+---------------------------------------
1503170 | 0 | 1081344 | 1441792 | gt | rt_ok
1503197 | 32879790712 | 1081344 | 7634944 | gt | rt_ok_1_prt_event_time20150105
1503225 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20150106
1503300 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20150107
1503351 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20150108
1503402 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20150109
1503453 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20141220
1503504 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20141221
1503555 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20141222
1503606 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20141223
1503657 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20141224
1503708 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20141225
1503759 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20141226
1503810 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20141227
1503861 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20141228
1503912 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20141229
1503963 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20141230
1504014 | 0 | 1081344 | 1081344 | gt | rt_ok_1_prt_event_time20141231
(18 rows)
Time: 103713.791 ms
gtgpdb=# select * from gp_toolkit.gp_size_of_table_disk t where sotdtablename like 'manager_table';
sotdoid | sotdsize | sotdtoastsize | sotdadditionalsize | sotdschemaname | sotdtablename
---------+----------+---------------+--------------------+----------------+---------------
1441033 | 327680 | 0 | 0 | gpmg | manager_table
(1 row)
Time: 35004.159 ms
gtgpdb=# select select a.schemaname ,a.tablename ,a.tableowner,pg_total_relation_size(a.schemaname||'.'||a.tablename) size from pg_tables a where a.tablename like 'manager_table';
ERROR: syntax error at or near "select"
LINE 1: select select a.schemaname ,a.tablename ,a.tableowner,pg_tot...
^
gtgpdb=# select a.schemaname ,a.tablename ,a.tableowner,pg_total_relation_size(a.schemaname||'.'||a.tablename) size from pg_tables a where a.tablename like 'manager_table';
schemaname | tablename | tableowner | size
------------+---------------+------------+---------
gpmg | manager_table | gpadmin | 1736704
(1 row)
Time: 341.430 ms
gtgpdb=# select a.schemaname ,a.tablename ,a.tableowner,pg_total_relation_size(a.schemaname||'.'||a.tablename) size from pg_tables a where a.tablename like 'rt_ok%';
schemaname | tablename | tableowner | size
------------+---------------------------------------+------------+-------------
gt | rt_ok | gt | 2523136
gt | rt_ok_1_prt_event_time20150105 | gt | 33318650192
gt | rt_ok_1_prt_event_time20150106 | gt | 2162688
gt | rt_ok_1_prt_event_time20150107 | gt | 2162688
gt | rt_ok_1_prt_event_time20150108 | gt | 2162688
gt | rt_ok_1_prt_event_time20150109 | gt | 2162688
gt | rt_ok_1_prt_event_time20141220 | gt | 2162688
gt | rt_ok_1_prt_event_time20141221 | gt | 2162688
gt | rt_ok_1_prt_event_time20141222 | gt | 2162688
gt | rt_ok_1_prt_event_time20141223 | gt | 2162688
gt | rt_ok_1_prt_event_time20141224 | gt | 2162688
gt | rt_ok_1_prt_event_time20141225 | gt | 2162688
gt | rt_ok_1_prt_event_time20141226 | gt | 2162688
gt | rt_ok_1_prt_event_time20141227 | gt | 2162688
gt | rt_ok_1_prt_event_time20141228 | gt | 2162688
gt | rt_ok_1_prt_event_time20141229 | gt | 2162688
gt | rt_ok_1_prt_event_time20141230 | gt | 2162688
gt | rt_ok_1_prt_event_time20141231 | gt | 2162688
(18 rows)
Time: 414.039 ms
gtgpdb=# select a.schemaname ,a.tablename ,a.tableowner,pg_total_relation_size(a.schemaname||'.'||a.tablename) size from pg_tables a where a.tablename like 'manager_table';
schemaname | tablename | tableowner | size
------------+---------------+------------+---------
gpmg | manager_table | gpadmin | 1736704
(1 row)
Time: 330.197 ms
gtgpdb=# select * from gp_toolkit.gp_size_of_table_disk t where sotdtablename like 'manager_table'; sotdoid | sotdsize | sotdtoastsize | sotdadditionalsize | sotdschemaname | sotdtablename
---------+----------+---------------+--------------------+----------------+---------------
1441033 | 327680 | 0 | 0 | gpmg | manager_table
(1 row)
Time: 26882.409 ms
gtgpdb=# select * from gp_toolkit.gp_size_of_table_disk t where sotdtablename like 'manager_table';
sotdoid | sotdsize | sotdtoastsize | sotdadditionalsize | sotdschemaname | sotdtablename
---------+----------+---------------+--------------------+----------------+---------------
1441033 | 327680 | 0 | 0 | gpmg | manager_table
(1 row)
Time: 25308.524 ms
gtgpdb=# vacuum analyze gpmg.manager_table;
VACUUM
Time: 2734.580 ms
gtgpdb=# select * from gp_toolkit.gp_size_of_table_disk t where sotdtablename like 'manager_table';
sotdoid | sotdsize | sotdtoastsize | sotdadditionalsize | sotdschemaname | sotdtablename
---------+----------+---------------+--------------------+----------------+---------------
1441033 | 327680 | 0 | 0 | gpmg | manager_table
(1 row)
Time: 25073.932 ms
gtgpdb=# select * from gp_toolkit.gp_size_of_table_disk t where sotdtablename like 'manager_table';
sotdoid | sotdsize | sotdtoastsize | sotdadditionalsize | sotdschemaname | sotdtablename
---------+----------+---------------+--------------------+----------------+---------------
1441033 | 327680 | 0 | 0 | gpmg | manager_table
(1 row)
Time: 25596.315 ms
gtgpdb=# select a.schemaname ,a.tablename ,a.tableowner,pg_total_relation_size(a.schemaname||'.'||a.tablename) size from pg_tables a where a.tablename like 'manager_table';
schemaname | tablename | tableowner | size
------------+---------------+------------+---------
gpmg | manager_table | gpadmin | 1736704
(1 row)
Time: 28.949 ms
gtgpdb=# select a.schemaname ,a.tablename ,a.tableowner,pg_total_relation_size(a.schemaname||'.'||a.tablename) size from pg_tables a where a.tablename like 'manager_table';
schemaname | tablename | tableowner | size
------------+---------------+------------+---------
gpmg | manager_table | gpadmin | 1736704
(1 row)
Time: 21.122 ms
gtgpdb=# select a.schemaname ,a.tablename ,a.tableowner,pg_relation_size(a.schemaname||'.'||a.tablename) size from pg_tables a where a.tablename like 'manager_table';
schemaname | tablename | tableowner | size
------------+---------------+------------+--------
gpmg | manager_table | gpadmin | 327680
(1 row)
Time: 313.303 ms
gtgpdb=# select a.schemaname ,a.tablename ,a.tableowner,pg_relation_size(a.schemaname||'.'||a.tablename) size from pg_tables a where a.tablename like 'manager_table';
schemaname | tablename | tableowner | size
------------+---------------+------------+--------
gpmg | manager_table | gpadmin | 327680
(1 row)结果非常明显，能够直接使用函数的还是直接使用，避免使用封装的视图，即使是系统的视图。
-EOF-
统计对象大小函数性能比较
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。