pev2
pev2 copied to clipboard
Published
20 hours ago •
dalibo
dalibo
Aggregate node name and explain format.
Depending on the source of the plan (json or text) the name of the aggregate nodes is different. This is not really pev's fault since that's the way the info is given in the json files.
The info is available in pev (misc panel) but I think the text format's node name provide more info at a glance.
Exemple : GroupAggregate / Node Type : Aggregate, Strategy : Sorted
[local]:5435 postgres@postgres=# EXPLAIN (ANALYZE) SELECT count(pk) FROM foo GROUP BY pk;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.42..20496.42 rows=500000 width=16) (actual time=0.110..162.504 rows=500000 loops=1)
Group Key: pk
-> Index Only Scan using foo_pkey on foo (cost=0.42..12996.42 rows=500000 width=8) (actual time=0.092..49.061 rows=500000 loops=1)
Heap Fetches: 0
Planning Time: 0.225 ms
Execution Time: 181.268 ms
(6 rows)
[local]:5435 postgres@postgres=# EXPLAIN (ANALYZE, FORMAT json) SELECT count(pk) FROM foo GROUP BY pk;
QUERY PLAN
-------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Aggregate", +
"Strategy": "Sorted", +
"Partial Mode": "Simple", +
"Parallel Aware": false, +
"Async Capable": false, +
"Startup Cost": 0.42, +
"Total Cost": 20496.42, +
"Plan Rows": 500000, +
"Plan Width": 16, +
"Actual Startup Time": 0.021, +
"Actual Total Time": 131.549, +
"Actual Rows": 500000, +
"Actual Loops": 1, +
"Group Key": ["pk"], +
"Plans": [ +
{ +
"Node Type": "Index Only Scan",+
"Parent Relationship": "Outer",+
"Parallel Aware": false, +
"Async Capable": false, +
"Scan Direction": "Forward", +
"Index Name": "foo_pkey", +
"Relation Name": "foo", +
"Alias": "foo", +
"Startup Cost": 0.42, +
"Total Cost": 12996.42, +
"Plan Rows": 500000, +
"Plan Width": 8, +
"Actual Startup Time": 0.016, +
"Actual Total Time": 39.653, +
"Actual Rows": 500000, +
"Actual Loops": 1, +
"Heap Fetches": 0 +
} +
] +
}, +
"Planning Time": 0.048, +
"Triggers": [ +
], +
"Execution Time": 146.992 +
} +
]
Exemple : Aggregate / Node Type : Aggregate, Strategy : Plain
[local]:5435 postgres@postgres=# EXPLAIN ANALYZE SELECT count(*) FROM (VALUES (1),(2),(3),(3),(4)) AS F(x);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Aggregate (cost=0.07..0.08 rows=1 width=8) (actual time=0.018..0.020 rows=1 loops=1)
-> Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=0) (actual time=0.003..0.007 rows=5 loops=1)
Planning Time: 0.087 ms
Execution Time: 0.060 ms
(4 rows)
[local]:5435 postgres@postgres=# EXPLAIN (ANALYZE, FORMAT json) SELECT count(*) FROM (VALUES (1),(2),(3),(3),(4)) AS F(x);
QUERY PLAN
-------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Aggregate", +
"Strategy": "Plain", +
"Partial Mode": "Simple", +
"Parallel Aware": false, +
"Async Capable": false, +
"Startup Cost": 0.07, +
"Total Cost": 0.08, +
"Plan Rows": 1, +
"Plan Width": 8, +
"Actual Startup Time": 0.016, +
"Actual Total Time": 0.018, +
"Actual Rows": 1, +
"Actual Loops": 1, +
"Plans": [ +
{ +
"Node Type": "Values Scan", +
"Parent Relationship": "Outer",+
"Parallel Aware": false, +
"Async Capable": false, +
"Alias": "*VALUES*", +
"Startup Cost": 0.00, +
"Total Cost": 0.06, +
"Plan Rows": 5, +
"Plan Width": 0, +
"Actual Startup Time": 0.003, +
"Actual Total Time": 0.007, +
"Actual Rows": 5, +
"Actual Loops": 1 +
} +
] +
}, +
"Planning Time": 0.105, +
"Triggers": [ +
], +
"Execution Time": 0.056 +
} +
]
(1 row)
Exemple : HashAggregate / Node Type : Aggregate, Strategy : Hashed
[local]:5435 postgres@postgres-# ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=125193.00..125193.10 rows=10 width=8) (actual time=1501.354..1501.430 rows=1001 loops=1)
Group Key: bar_pk.bar_pk
Batches: 1 Memory Usage: 153kB
Buffers: shared hit=7693
-> Nested Loop (cost=0.00..112693.00 rows=5000000 width=8) (actual time=0.025..1023.358 rows=4000000 loops=1)
Buffers: shared hit=7693
-> Seq Scan on foo (cost=0.00..12693.00 rows=500000 width=85) (actual time=0.008..34.325 rows=500000 loops=1)
Buffers: shared hit=7693
-> Function Scan on unnest bar_pk (cost=0.00..0.10 rows=10 width=8) (actual time=0.001..0.001 rows=8 loops=500000)
Planning:
Buffers: shared hit=95
Planning Time: 0.474 ms
Execution Time: 1501.521 ms
(13 rows)
[local]:5435 postgres@postgres=# EXPLAIN (ANALYZE, BUFFERS, FORMAT json) select distinct
bar_pk
from
foo
cross join lateral
unnest(foo.bar_pks) as bar_pk
;
QUERY PLAN
-----------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Aggregate", +
"Strategy": "Hashed", +
"Partial Mode": "Simple", +
"Parallel Aware": false, +
"Async Capable": false, +
"Startup Cost": 125193.00, +
"Total Cost": 125193.10, +
"Plan Rows": 10, +
"Plan Width": 8, +
"Actual Startup Time": 1479.791, +
"Actual Total Time": 1479.863, +
"Actual Rows": 1001, +
"Actual Loops": 1, +
"Group Key": ["bar_pk.bar_pk"], +
"Planned Partitions": 0, +
"HashAgg Batches": 1, +
"Peak Memory Usage": 153, +
"Disk Usage": 0, +
"Shared Hit Blocks": 7693, +
"Shared Read Blocks": 0, +
"Shared Dirtied Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Dirtied Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0, +
"Plans": [ +
{ +
"Node Type": "Nested Loop", +
"Parent Relationship": "Outer", +
"Parallel Aware": false, +
"Async Capable": false, +
"Join Type": "Inner", +
"Startup Cost": 0.00, +
"Total Cost": 112693.00, +
"Plan Rows": 5000000, +
"Plan Width": 8, +
"Actual Startup Time": 0.039, +
"Actual Total Time": 1010.174, +
"Actual Rows": 4000000, +
"Actual Loops": 1, +
"Inner Unique": false, +
"Shared Hit Blocks": 7693, +
"Shared Read Blocks": 0, +
"Shared Dirtied Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Dirtied Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0, +
"Plans": [ +
{ +
"Node Type": "Seq Scan", +
"Parent Relationship": "Outer",+
"Parallel Aware": false, +
"Async Capable": false, +
"Relation Name": "foo", +
"Alias": "foo", +
"Startup Cost": 0.00, +
"Total Cost": 12693.00, +
"Plan Rows": 500000, +
"Plan Width": 85, +
"Actual Startup Time": 0.015, +
"Actual Total Time": 33.875, +
"Actual Rows": 500000, +
"Actual Loops": 1, +
"Shared Hit Blocks": 7693, +
"Shared Read Blocks": 0, +
"Shared Dirtied Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Dirtied Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0 +
}, +
{ +
"Node Type": "Function Scan", +
"Parent Relationship": "Inner",+
"Parallel Aware": false, +
"Async Capable": false, +
"Function Name": "unnest", +
"Alias": "bar_pk", +
"Startup Cost": 0.00, +
"Total Cost": 0.10, +
"Plan Rows": 10, +
"Plan Width": 8, +
"Actual Startup Time": 0.001, +
"Actual Total Time": 0.001, +
"Actual Rows": 8, +
"Actual Loops": 500000, +
"Shared Hit Blocks": 0, +
"Shared Read Blocks": 0, +
"Shared Dirtied Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Dirtied Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0 +
} +
] +
} +
] +
}, +
"Planning": { +
"Shared Hit Blocks": 0, +
"Shared Read Blocks": 0, +
"Shared Dirtied Blocks": 0, +
"Shared Written Blocks": 0, +
"Local Hit Blocks": 0, +
"Local Read Blocks": 0, +
"Local Dirtied Blocks": 0, +
"Local Written Blocks": 0, +
"Temp Read Blocks": 0, +
"Temp Written Blocks": 0 +
}, +
"Planning Time": 0.170, +
"Triggers": [ +
], +
"Execution Time": 1479.974 +
} +
]
(1 row)
