PROFILE

Profiles a single SQL statement.

Syntax

PROFILE { sql-statement }

Parameters

sql-statement: A query (SELECT) statement or DML statement--for example, you can profile INSERT, UPDATE, COPY, and MERGE.

Output

Writes profile summary to stderr, saves details to system catalog V_MONITOR.EXECUTION_ENGINE_PROFILES.

Privileges

The same privileges required to run the profiled statement

Description

PROFILE generates detailed information about how the target statement executes, and saves that information in the system catalog V_MONITOR.EXECUTION_ENGINE_PROFILES. Query output is preceded by a profile summary: profile identifiers transaction_id and statement_id, initiator memory for the query, and total memory required. For example:

=> 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=45035996274683334 and statement_id=7;
NOTICE 3557:  Initiator memory for query: [on pool general: 708421 KB, minimum: 554324 KB]
NOTICE 5077:  Total memory required by query: [708421 KB]
  customer_name   | annual_income
------------------+---------------
 Emily G. Vogel   |        999998
 James M. McNulty |        999979
(2 rows)

Use profile identifiers to query the table for profile information on a given query.