分析单个语句
要分析单个语句，请在其前面加上
PROFILE 前缀。可以分析查询 (SELECT) 语句或任何 DML 语句，例如
INSERT、
UPDATE、
COPY 和
MERGE。语句返回分析摘要：
-
分析标识符
transaction_id和statement_id -
查询的启动内存
-
所需的总内存
例如：
=> PROFILE SELECT customer_name, annual_income FROM public.customer_dimension
WHERE (customer_gender, annual_income) IN (SELECT customer_gender, MAX(annual_income)
FROM public.customer_dimension GROUP BY customer_gender);NOTICE 4788: Statement is being profiled
HINT: Select * from v_monitor.execution_engine_profiles where transaction_id=45035996274760535 and statement_id=1;
NOTICE 3557: Initiator memory for query: [on pool general: 2783428 KB, minimum: 2312914 KB]
NOTICE 5077: Total memory required by query: [2783428 KB]
customer_name | annual_income
------------------+---------------
James M. McNulty | 999979
Emily G. Vogel | 999998
(2 rows)
可以使用分析标识符 transaction_id 和 statement_id 从系统表
执行_引擎_配置文件 和
QUERY_PLAN_PROFILES 中获取此查询的详细分析信息。还可以使用这些标识符从系统表
QUERY_CONSUMPTION 中获取资源消耗数据。
例如：
=> SELECT path_id, path_line::VARCHAR(68), running_time FROM v_monitor.query_plan_profiles
WHERE transaction_id=45035996274760535 AND statement_id=1 ORDER BY path_id, path_line_index;
path_id | path_line | running_time
---------+----------------------------------------------------------------------+-----------------
1 | +-JOIN HASH [Semi] [Cost: 631, Rows: 25K (NO STATISTICS)] (PATH ID: | 00:00:00.052478
1 | | Join Cond: (customer_dimension.customer_gender = VAL(2)) AND (cus |
1 | | Materialize at Output: customer_dimension.customer_name |
1 | | Execute on: All Nodes |
2 | | +-- Outer -> STORAGE ACCESS for customer_dimension [Cost: 30, Rows | 00:00:00.051598
2 | | | Projection: public.customer_dimension_b0 |
2 | | | Materialize: customer_dimension.customer_gender, customer_d |
2 | | | Execute on: All Nodes |
2 | | | Runtime Filters: (SIP1(HashJoin): customer_dimension.custom |
4 | | | +---> GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GR | 00:00:00.050566
4 | | | | Aggregates: max(customer_dimension.annual_income) |
4 | | | | Group By: customer_dimension.customer_gender |
4 | | | | Execute on: All Nodes |
5 | | | | +---> STORAGE ACCESS for customer_dimension [Cost: 30, Rows: 5 | 00:00:00.09234
5 | | | | | Projection: public.customer_dimension_b0 |
5 | | | | | Materialize: customer_dimension.customer_gender, custom |
5 | | | | | Execute on: All Nodes |
(17 rows)