改善 QUERY_PLAN_PROFILES 输出的可读性

QUERY_PLAN_PROFILES 表的输出会因 path_line 列而非常宽。为提高可读性,使用下面的一个或多个选项查询 QUERY_PLAN_PROFILES

  • transaction_idstatement_idpath_idpath_line_index 对输出排序:

    => SELECT ... FROM query_plan_profiles
         WHERE ...
        ORDER BY transaction_id, statement_id, path_id, path_line_index;
    
  • 使用列别名减小列宽:

    => SELECT statement_id AS sid, path_id AS id, path_line_index AS order,
         is_started AS start, is_completed AS end, is_executing AS exe,
         running_time AS run, memory_allocated_bytes AS mem,
         read_from_disk_bytes AS read, received_bytes AS rec,
         sent_bytes AS sent, FROM query_plan_profiles
         WHERE transaction_id=45035996273910558 AND statement_id=3
         ORDER BY transaction_id, statement_id, path_id, path_line_index;
    
  • 使用 vsql \o 命令将 EXPLAIN 输出重定向至文件:

    => \o /home/dbadmin/long-queries
    => EXPLAIN SELECT * FROM customer_dimension;
    => \o