The EXPLAIN ANALYZE statement provides insight into how a given index or materialized view is running.
We’ve expanded EXPLAIN ANALYZE to let you analyze your current cluster, not just an individual object.
EXPLAIN ANALYZE CLUSTER) presents a summary of every object installed on your current cluster.
It’s possible to summarize CPU time spent and memory consumed per object on the current cluster.
It’s also possible to see whether any objects on the current cluster have skewed operators, where work isn’t evenly distributed among workers.
If we run EXPLAIN ANALYZE CLUSTER CPU WITH SKEW on our introspection debugging blogpost’s queries, we get output like the following:
| object | global_id | worker_id | max_operator_cpu_ratio | worker_elapsed | avg_elapsed | total_elapsed |
|---|---|---|---|---|---|---|
| materialize.public.idx_sales_by_product | u85492 | 0 | 1.18 | 00:00:00.094447 | 00:00:00.079829 | 00:00:00.159659 |
| materialize.public.idx_top_buyers | u85495 | 0 | 1.15 | 00:00:01.371221 | 00:00:01.363659 | 00:00:02.727319 |
| materialize.public.idx_top_buyers | u85495 | 1 | 1.03 | 00:00:01.356098 | 00:00:01.363659 | 00:00:02.727319 |
| materialize.public.idx_top_buyers | u85496 | 1 | 1.01 | 00:00:00.021163 | 00:00:00.021048 | 00:00:00.042096 |
| materialize.public.idx_top_buyers | u85496 | 0 | 0.99 | 00:00:00.020932 | 00:00:00.021048 | 00:00:00.042096 |
| materialize.public.idx_sales_by_product | u85492 | 1 | 0.82 | 00:00:00.065211 | 00:00:00.079829 | 00:00:00.159659 |
The max_operator_cpu_ratios here are perfectly reasonable.
Ratios above above 2 merit investigation using EXPLAIN ANALYZE on that particular object.
Objects comprise many operators, and we’re reporting the worst ratio for any operator---a high CPU or memory ratio in one operator is something to look at, but not inherently problematic.
Using EXPLAIN ANALYZE CLUSTER ... WITH SKEW, you can quickly identify which objects are worth looking at more deeply.
If we run EXPLAIN ANALYZE CLUSTER MEMORY, we get output like the following:
| object | global_id | total_memory | total_records |
|---|---|---|---|
| materialize.public.idx_top_buyers | u85496 | 2086 bytes | 25 |
| materialize.public.idx_sales_by_product | u85492 | 1909 kB | 148607 |
| materialize.public.idx_top_buyers | u85495 | 1332 kB | 77133 |
Just like other EXPLAIN ANALYZE statements, you can run EXPLAIN ANALYZE CLUSTER ... AS SQL to get the SQL query on our introspection infrastructure that generates the summary.
If you have a particular measure of cluster health you’re interested in, those queries make a good starting point.
Since EXPLAIN ANALYZE statements query our introspection infrastructure, these statements implicitly work on your current cluster;
if you have more than one cluster, be sure to SET CLUSTER appropriately before running these commands.
Finally, the EXPLAIN syntax isn’t a stable interface, and we’ll be improving on the output here over time;
we’d be happy to hear your feedback.
Until then, you can read more about EXPLAIN ANALYZE CLUSTER in the docs!