EXPLAIN ANALYZE

EXPLAIN ANALYZE:

• 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.

Syntax

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

Privileges

The privileges required to execute this statement are:

• USAGE privileges on the schemas that all relations in the explainee are contained in.

Examples

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.

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 ... 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:

SELECT
    repeat(' ', nesting * 2) || operator AS operator,
    megsa.levels AS levels,
    megsa.to_cut AS to_cut,
    megsa.hint AS hint,
    pg_size_pretty(savings) AS savings
FROM
    mz_introspection.mz_lir_mapping AS mlm
        JOIN
            mz_introspection.mz_dataflow_global_ids AS mdgi
            ON (mlm.global_id = mdgi.global_id)
        LEFT JOIN
            mz_introspection.mz_expected_group_size_advice AS megsa
            ON
                (
                    megsa.dataflow_id = mdgi.id
                        AND
                    mlm.operator_id_start <= megsa.region_id
                        AND
                    megsa.region_id < mlm.operator_id_end
                )
        JOIN
            mz_introspection.mz_mappable_objects AS mo
            ON (mlm.global_id = mo.global_id)
WHERE mo.name = 'materialize.public.wins_by_item'
ORDER BY mlm.lir_id DESC;