explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ewUI : packrest jit

Settings
# exclusive inclusive rows x rows loops node
1. 0.687 1,532.872 ↑ 31.0 1 1

Hash Join (cost=3,150,506.08..3,803,438.32 rows=31 width=168) (actual time=1,532.798..1,532.872 rows=1 loops=1)

  • Hash Cond: ((a.config_file = per_stanza.config_file) AND (a.stanza = per_stanza.stanza) AND ((a.backup_data ->> 'type'::text) = ((per_stanza.backup_data -
  • Functions: 38
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 6.648 ms, Inlining 89.703 ms, Optimization 382.182 ms, Emission 280.157 ms, Total 758.690 ms
2.          

CTE all_backups

3. 0.095 1,531.687 ↑ 100,000.0 1 1

ProjectSet (cost=0.25..517.75 rows=100,000 width=64) (actual time=1,531.682..1,531.687 rows=1 loops=1)

4. 1,531.592 1,531.592 ↑ 1,000.0 1 1

Function Scan on pgbackrest_info (cost=0.25..10.25 rows=1,000 width=64) (actual time=1,531.588..1,531.592 rows=1 loops=1)

5.          

CTE per_stanza

6. 0.072 1,531.771 ↑ 10,000,000.0 1 1

ProjectSet (cost=0.00..53,250.00 rows=10,000,000 width=96) (actual time=1,531.764..1,531.771 rows=1 loops=1)

7. 1,531.699 1,531.699 ↑ 100,000.0 1 1

CTE Scan on all_backups (cost=0.00..2,000.00 rows=100,000 width=64) (actual time=1,531.694..1,531.699 rows=1 loops=1)

8. 1,531.772 1,531.772 ↑ 10,000,000.0 1 1

CTE Scan on per_stanza a (cost=0.00..200,000.00 rows=10,000,000 width=96) (actual time=1,531.772..1,531.772 rows=1 loops=1)

9. 0.228 0.413 ↑ 1,000,000.0 1 1

Hash (cost=3,051,777.33..3,051,777.33 rows=1,000,000 width=160) (actual time=0.412..0.413 rows=1 loops=1)

  • Buckets: 32,768 Batches: 64 Memory Usage: 256kB
10. 0.051 0.185 ↑ 1,000,000.0 1 1

GroupAggregate (cost=2,779,277.33..3,041,777.33 rows=1,000,000 width=160) (actual time=0.184..0.185 rows=1 loops=1)

  • Group Key: per_stanza.config_file, per_stanza.stanza, ((per_stanza.backup_data ->> 'type'::text))
11. 0.102 0.134 ↑ 10,000,000.0 1 1

Sort (cost=2,779,277.33..2,804,277.33 rows=10,000,000 width=128) (actual time=0.134..0.134 rows=1 loops=1)

  • Sort Key: per_stanza.config_file, per_stanza.stanza, ((per_stanza.backup_data ->> 'type'::text))
  • Sort Method: quicksort Memory: 26kB
12. 0.032 0.032 ↑ 10,000,000.0 1 1

CTE Scan on per_stanza (cost=0.00..225,000.00 rows=10,000,000 width=128) (actual time=0.024..0.032 rows=1 loops=1)

Planning time : 0.789 ms
Execution time : 1,712.725 ms