統計對象大小函數效能比較，對象函數效能比較

統計對象大小函數效能比較，對象函數效能比較

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 msgtgpdb=# 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 msgtgpdb=# 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 msgtgpdb=# 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 msgtgpdb=# 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 msgtgpdb=# 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 msgtgpdb=# 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 msgtgpdb=# vacuum analyze gpmg.manager_table;VACUUMTime: 2734.580 msgtgpdb=# 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 msgtgpdb=# 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 msgtgpdb=# 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 msgtgpdb=# 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 msgtgpdb=# 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 msgtgpdb=# 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-