Profiling

Profiling is essential to help understand why certain queries exhibit specific performance characteristics. SereneDB contains several built-in features to enable query profiling, which this page covers. For a high-level example of using EXPLAIN, see the “Inspect Query Plans” page.

Statements

The EXPLAIN Statement

The first step to profiling a query can include examining the query plan. The EXPLAIN statement shows the query plan and describes what is going on under the hood.

The EXPLAIN ANALYZE Statement

The query plan helps developers understand the performance characteristics of the query. However, it is often also necessary to examine the performance numbers of individual operators and the cardinalities that pass through them. Prepending a query with EXPLAIN ANALYZE both pretty-prints the query plan and executes it, providing run-time performance numbers for every operator, as well as the actual row counts flowing through each operator.

Query

EXPLAIN ANALYZE SELECT * FROM tbl;

Result

┌─────────────────────────────────────┐│┌───────────────────────────────────┐│││    Query Profiling Information    │││└───────────────────────────────────┘│└─────────────────────────────────────┘EXPLAIN ANALYZE SELECT * FROM tbl;┌────────────────────────────────────────────────┐│┌──────────────────────────────────────────────┐│││              Total Time: 0.0029s             │││└──────────────────────────────────────────────┘│└────────────────────────────────────────────────┘┌───────────────────────────┐│      RESULT_COLLECTOR     ││    ────────────────────   ││           0 rows          ││           0.00s           │└─────────────┬─────────────┘┌─────────────┴─────────────┐│      EXPLAIN_ANALYZE      ││    ────────────────────   ││           0 rows          ││           0.00s           │└─────────────┬─────────────┘┌─────────────┴─────────────┐│         TABLE_SCAN        ││    ────────────────────   ││         Table: tbl        ││   Type: Sequential Scan   ││                           ││        Projections:       ││             id            ││            name           ││            col1           ││                           ││           3 rows          ││           0.00s           │└───────────────────────────┘

Note that the cumulative wall-clock time that is spent on every operator is shown. When multiple threads are processing the query in parallel, the total processing time of the query may be lower than the sum of all the times spent on the individual operators.

For brevity, the samples on this page omit the optimizer and planner timing breakdown that EXPLAIN ANALYZE prints between the total time and the operator tree.

For multi-file reads (e.g., reading multiple Parquet files), the output includes the file names being read.

Below is an example of running EXPLAIN ANALYZE on a join query. The output is a profiling tree showing the Total Time, the per-operator wall-clock timings and the actual row counts flowing through each operator (the exact timings vary between runs):

Query

EXPLAIN ANALYZE    SELECT name    FROM students    JOIN exams USING (sid)    WHERE name LIKE 'Ma%';

Result

┌─────────────────────────────────────┐│┌───────────────────────────────────┐│││    Query Profiling Information    │││└───────────────────────────────────┘│└─────────────────────────────────────┘EXPLAIN ANALYZE SELECT name FROM students JOIN exams USING (sid) WHERE name LIKE 'Ma%';┌────────────────────────────────────────────────┐│┌──────────────────────────────────────────────┐│││              Total Time: 0.0085s             │││└──────────────────────────────────────────────┘│└────────────────────────────────────────────────┘┌───────────────────────────┐│      RESULT_COLLECTOR     ││    ────────────────────   ││           0 rows          ││           0.00s           │└─────────────┬─────────────┘┌─────────────┴─────────────┐│      EXPLAIN_ANALYZE      ││    ────────────────────   ││           0 rows          ││           0.00s           │└─────────────┬─────────────┘┌─────────────┴─────────────┐│         PROJECTION        ││    ────────────────────   ││            name           ││                           ││           2 rows          ││           0.00s           │└─────────────┬─────────────┘┌─────────────┴─────────────┐│         HASH_JOIN         ││    ────────────────────   ││      Join Type: INNER     ││   Conditions: sid = sid   ││                           ├──────────────┐│           2 rows          │              ││           0.00s           │              │└─────────────┬─────────────┘              │┌─────────────┴─────────────┐┌─────────────┴─────────────┐│         TABLE_SCAN        ││         TABLE_SCAN        ││    ────────────────────   ││    ────────────────────   ││        Table: exams       ││      Table: students      ││   Type: Sequential Scan   ││   Type: Sequential Scan   ││      Projections: sid     ││        Projections:       ││                           ││            sid            ││      Dynamic Filters:     ││            name           ││ ((optional: (sid IN (1, 3)││                           ││ ) AND optional: (sid >= 1)││          Filters:         ││) AND optional: (sid <= 3))││ ((name >= 'Ma') AND (name ││                           ││          < 'Mb'))         ││           3 rows          ││           2 rows          ││           0.00s           ││           0.00s           │└───────────────────────────┘└───────────────────────────┘

The FORMAT Option

The EXPLAIN [ANALYZE] statement allows exporting to several formats:

• text – default ASCII-art style output
• graphviz – produces a DOT output, which can be rendered with Graphviz
• html – produces an HTML output, which can be rendered with treeflex
• json – produces a JSON output
• mermaid – produces a Mermaid flowchart

To specify a format, use the FORMAT tag:

Query

EXPLAIN (FORMAT html) SELECT 42 AS x;

Pragmas

SereneDB supports several pragmas for turning profiling on and off and controlling the level of detail in the profiling output.

The following pragmas are available and can be set using either PRAGMA or SET. They can also be reset using RESET, followed by the setting name. For more information, see the “Profiling” section of the pragmas page.

Setting	Description	Default	Options
enable_profiling, enable_profile	Turn on profiling	query_tree	query_tree, json, query_tree_optimizer, no_output
profiling_coverage	Set the operators to profile	SELECT	SELECT, ALL
profiling_output	Set a profiling output file	Console	A filepath
profiling_mode	Toggle additional optimizer and planner metrics	standard	standard, detailed, all
configure_profiling	Enable or disable specific metrics	All metrics except those activated by detailed profiling	A JSON object that matches the following: {"METRIC_NAME": "boolean", ...}. (List of all available metrics)
disable_profiling, disable_profile	Turn off profiling

Table Functions

SereneDB provides table functions to enable and disable profiling, consolidating multiple settings into a single call.

enable_profiling()

The enable_profiling() function configures profiling with the specified options.

Query

CALL enable_profiling(    format := 'json',    save_location := '/path/to/output.json',    coverage := 'select',    mode := 'standard',    metrics := ['QUERY_NAME', 'LATENCY', 'OPERATOR_TIMING']);

Result

success

Parameter	Type	Description
metrics	LIST, STRUCT, or JSON	Specifies which metrics to enable
mode	VARCHAR	Profiling level: 'standard' or 'detailed'
save_location	VARCHAR	File path for profiling output
coverage	VARCHAR	Query coverage: 'select' or 'all'
format	VARCHAR	Output format: 'query_tree', 'json', 'query_tree_optimizer', 'no_output'

All parameters are optional and named. You can also pass metrics as an unnamed parameter:

Query

CALL enable_profiling(['LATENCY', 'RESULT_SET_SIZE']);

Result

success

disable_profiling()

The disable_profiling() function turns off profiling.

Query

CALL disable_profiling();

Result

success

Metrics

SereneDB supports a wide range of metrics that can be enabled or disabled independently.

Detailed Profiling

When the profiling_mode is set to detailed, an extra set of metrics are enabled, which are only available in the QUERY_ROOT node. These include all the metrics in the Phase timing metric group. It is possible to toggle each of these additional metrics individually.

Notation in Query Plans

In query plans, the hash join operators adhere to the following convention: the probe side of the join is the left operand, while the build side is the right operand.

Join operators in the query plan show the join type used:

• Inner joins are denoted as INNER.
• Left outer joins and right outer joins are denoted as LEFT and RIGHT, respectively.
• Full outer joins are denoted as FULL.