EXPLAIN ANALYZE

EXPLAIN ANALYZE:

• Summarizes cluster status.
• Reports on the performance of indexes and materialized views.
• Provide the execution plan annotated with TopK hints. The TopK query pattern groups by some key and return the first K elements within each group according to some ordering.

{{< warning >}} EXPLAIN is not part of Materialize's stable interface and is not subject to our backwards compatibility guarantee. The syntax and output of EXPLAIN may change arbitrarily in future versions of Materialize. {{< /warning >}}

Syntax

EXPLAIN ANALYZE
      CPU [, MEMORY] [WITH SKEW]
    | MEMORY [, CPU] [WITH SKEW]
    | HINTS
FOR INDEX <name> | MATERIALIZED VIEW <name>
[ AS SQL ]
;

EXPLAIN ANALYZE CLUSTER
      CPU [, MEMORY] [WITH SKEW]
    | MEMORY [, CPU] [WITH SKEW]
[ AS SQL ]
;

{{< tip >}} If you want to specify both CPU or MEMORY, they may be listed in any order; however, each may appear only once. {{</ tip >}}

Privileges

The privileges required to execute this statement are:

{{% include-headless "/headless/sql-command-privileges/explain-analyze" %}}

Examples

EXPLAIN ANALYZE attributes runtime metrics to PHYSICAL PLAN operators.

The attribution examples in this section reference the wins_by_item index (and the underlying winning_bids view) from the quickstart guide:

CREATE SOURCE auction_house
FROM LOAD GENERATOR AUCTION
(TICK INTERVAL '1s', AS OF 100000)
FOR ALL TABLES;

CREATE VIEW winning_bids AS
  SELECT DISTINCT ON (a.id) b.*, a.item, a.seller
    FROM auctions AS a
    JOIN bids AS b
      ON a.id = b.auction_id
   WHERE b.bid_time < a.end_time
     AND mz_now() >= a.end_time
   ORDER BY a.id, b.amount DESC, b.bid_time, b.buyer;

CREATE INDEX wins_by_item ON winning_bids (item);

EXPLAIN ANALYZE MEMORY

The following examples reports on the memory usage of the index wins_by_item:

EXPLAIN ANALYZE MEMORY FOR INDEX wins_by_item;

For the index, EXPLAIN ANALYZE MEMORY reports on the memory usage and the number of records for each operator in the dataflow:

The results show the TopK operator is overwhelmingly responsible for memory usage.

EXPLAIN ANALYZE CPU

The following examples reports on the cpu usage of the index wins_by_item:

EXPLAIN ANALYZE CPU FOR INDEX wins_by_item;

For the index, EXPLAIN ANALYZE CPU reports on total time spent in each operator (not inclusive of its child operators) in the dataflow:

EXPLAIN ANALYZE CPU, MEMORY

You can report on both CPU and memory usage simultaneously:

EXPLAIN ANALYZE CPU, MEMORY FOR INDEX wins_by_item;

You can specify both CPU or MEMORY in any order; however, each may appear only once. The order of CPU and MEMORY in the statement determines the order of the output columns

For example, in the above example where the CPU was listed before MEMORY, the CPU time (total_elasped) column is listed before the MEMORY information total_memory and total_records.

EXPLAIN ANALYZE ... WITH SKEW

In clusters with more than one worker, worker skew can occur when data is unevenly distributed across workers. Extreme cases of skew can seriously impact performance. You can use EXPLAIN ANALYZE ... WITH SKEW to identify this scenario. The WITH SKEW option includes the per worker and average worker performance numbers for each operator, along with each worker's ratio compared to the average.

For the below example, assume there are 2 workers in the cluster.

{{< tip >}}

To determine how many workers a given cluster size has, you can query mz_catalog.mz_cluster_replica_sizes.

{{</ tip >}}

You can explain MEMORY and/or CPU with the WITH SKEW option. For example, the following runs EXPLAIN ANALYZE MEMORY WITH SKEW:

EXPLAIN ANALYZE MEMORY WITH SKEW FOR INDEX wins_by_item;

The results include the per worker and average worker performance numbers for each operator, along with each worker's ratio compared to the average:

The ratio column tells you whether a worker is particularly over- or under-loaded:

• a ratio below 1 indicates a worker doing a below average amount of work.

• a ratio above 1 indicates a worker doing an above average amount of work.

While there will always be some amount of variation, very high ratios indicate a skewed workload. Here the memory ratios are mostly close to 1, indicating there is very little worker skew everywhere but at the top level arrangement, where worker 3 has no records.

EXPLAIN ANALYZE HINTS

EXPLAIN ANALYZE HINTS can annotate your plan (specifically, each TopK operator) with suggested TopK hints; i.e., DISTINCT ON INPUT GROUP SIZE= value.

For example, the following runs EXPLAIN ANALYZE HINTS on the wins_by_item index:

EXPLAIN ANALYZE HINTS FOR INDEX wins_by_item;

The result shows that the wins_by_item index has only one TopK operator and suggests the hint (i.e, the DISTINCT ON INPUT GROUP SIZE= value) of 255.0.

With the hint information, you can recreate the view and index to improve memory usage:

DROP VIEW winning_bids CASCADE;

CREATE VIEW winning_bids AS
    SELECT DISTINCT ON (a.id) b.*, a.item, a.seller
      FROM auctions AS a
      JOIN bids AS b
        ON a.id = b.auction_id
     WHERE b.bid_time < a.end_time
       AND mz_now() >= a.end_time
   OPTIONS (DISTINCT ON INPUT GROUP SIZE = 255) -- use hint!
  ORDER BY a.id,
    b.amount DESC,
    b.bid_time,
    b.buyer;

CREATE INDEX wins_by_item ON winning_bids (item);

Re-running the TopK-hints query will show only null hints; i.e., there are no hints because our TopK is now appropriately sized.

To see if the indexe's memory usage has improved with the hint, rerun the following EXPLAIN ANALYZE MEMORY command:

EXPLAIN ANALYZE MEMORY FOR INDEX wins_by_item;

The results show that the TopK operator uses 11MB of memory, less than a third of the ~36MB of memory it was using before:

EXPLAIN ANALYZE CLUSTER ...

It is possible to look at overall cluster status, rather than individual indexes or materialized views. This is useful for quickly identifying skewed dataflows as well as which dataflows are taking up the most resources.

Running EXPLAIN ANALYZE CLUSTER MEMORY, CPU will identify which dataflows are using the most resources. Running this statement on a small cluster with 4 workers, we find:

Note that the output is sorted by total_elapsed---the output is ordered by whichever property is listed first. Here it also happens to be sorted by total_memory and total_records: the dataflows processing the most data took the most time. On a cluster with dozens of indexes and materialized views, EXPLAIN ANALYZE CLUSTER reveals which dataflows are consuming the most resources.

We can quickly find skewed dataflows on a cluster by running EXPLAIN ANALYZE CLUSTER MEMORY WITH SKEW; here is an example on a small cluster with 4 workers:

The u9 and u8 dataflows make up the wins_by_item dataflow (where u8 does the work and u9 arranges it). Both dataflows run on all four workers. We report the max_operator_memory_ratio for each worker on each dataflow: what is the ratio of that dataflow's memory usage to the average memory usage across all workers? Note that the output results are sorted by max_operator_memory_ratio, making it easy to spot skew. Here, workers 1 and 2 hold most of the records; worker 0 has half as many, and worker 3 has none at all.

EXPLAIN ANALYZE ... AS SQL

Under the hood:

• For returning Memory/CPU information, EXPLAIN ANALYZE runs SQL queries that correlate mz_introspection performance information with the LIR operators in mz_introspection.mz_lir_mapping.

• For TopK hints, EXPLAIN ANALYZE uses mz_introspection.mz_expected_group_size_advice introspection source to offer hints on sizing TopK operators.

You can append AS SQL to any EXPLAIN ANALYZE statement to see the SQL that would be run (without running it). You can then customize this SQL to report finer grained or other information. For example:

EXPLAIN ANALYZE HINTS FOR INDEX wins_by_item AS SQL;

The results show the SQL that EXPLAIN ANALYZE would run to get the TopK hints for the wins_by_items index.