统计对象大小函数性能比较

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-