explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DdnP

Settings
# exclusive inclusive rows x rows loops node
1. 1.622 188.722 ↓ 1.0 496 1

Sort (cost=1,286,912.50..1,286,913.69 rows=476 width=161) (actual time=188.337..188.722 rows=496 loops=1)

  • Sort Key: a.code
  • Sort Method: quicksort Memory: 158kB
2.          

CTE tree

3. 3.065 11.786 ↑ 57.2 1,985 1

Recursive Union (cost=0.00..8,247.45 rows=113,506 width=20) (actual time=0.016..11.786 rows=1,985 loops=1)

4. 0.411 0.411 ↓ 1.0 496 1

Seq Scan on acct (cost=0.00..15.76 rows=476 width=20) (actual time=0.013..0.411 rows=496 loops=1)

5. 3.315 8.310 ↑ 37.9 298 5

Hash Join (cost=154.70..596.16 rows=11,303 width=20) (actual time=0.799..1.662 rows=298 loops=5)

  • Hash Cond: (a_1.parent = t_2.id)
6. 1.860 1.860 ↓ 1.0 496 5

Seq Scan on acct a_1 (cost=0.00..15.76 rows=476 width=16) (actual time=0.003..0.372 rows=496 loops=5)

7. 1.605 3.135 ↑ 12.0 397 5

Hash (cost=95.20..95.20 rows=4,760 width=20) (actual time=0.627..0.627 rows=397 loops=5)

  • Buckets: 8192 Batches: 1 Memory Usage: 66kB
8. 1.530 1.530 ↑ 12.0 397 5

WorkTable Scan on tree t_2 (cost=0.00..95.20 rows=4,760 width=20) (actual time=0.002..0.306 rows=397 loops=5)

9. 13.363 187.100 ↓ 1.0 496 1

HashAggregate (cost=62,303.22..1,278,643.87 rows=476 width=161) (actual time=96.201..187.100 rows=496 loops=1)

  • Group Key: a.id
10. 19.889 84.457 ↑ 173.2 11,911 1

Merge Right Join (cost=20,741.84..51,988.36 rows=2,062,972 width=129) (actual time=50.841..84.457 rows=11,911 loops=1)

  • Merge Cond: (x.account = t.id)
11. 4.437 31.946 ↑ 1.4 2,690 1

Sort (cost=761.61..770.69 rows=3,635 width=40) (actual time=29.823..31.946 rows=2,690 loops=1)

  • Sort Key: x.account
  • Sort Method: quicksort Memory: 223kB
12. 3.899 27.509 ↑ 1.4 2,690 1

Subquery Scan on x (cost=501.20..546.64 rows=3,635 width=40) (actual time=21.518..27.509 rows=2,690 loops=1)

13. 4.814 23.610 ↑ 1.4 2,690 1

Sort (cost=501.20..510.29 rows=3,635 width=152) (actual time=21.516..23.610 rows=2,690 loops=1)

  • Sort Key: t_1.postdate DESC
  • Sort Method: quicksort Memory: 307kB
14. 8.422 18.796 ↑ 1.4 2,690 1

HashAggregate (cost=222.62..286.23 rows=3,635 width=152) (actual time=15.743..18.796 rows=2,690 loops=1)

  • Group Key: t_1.id, gl.account
15. 6.099 10.374 ↑ 1.0 3,632 1

Hash Join (cost=28.02..168.09 rows=3,635 width=31) (actual time=1.447..10.374 rows=3,632 loops=1)

  • Hash Cond: (gl.transaction = t_1.id)
16. 2.860 2.860 ↑ 1.0 3,632 1

Seq Scan on transentry gl (cost=0.00..92.35 rows=3,635 width=23) (actual time=0.008..2.860 rows=3,632 loops=1)

17. 0.719 1.415 ↓ 1.1 872 1

Hash (cost=18.01..18.01 rows=801 width=16) (actual time=1.414..1.415 rows=872 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
18. 0.696 0.696 ↓ 1.1 872 1

Seq Scan on transacc t_1 (cost=0.00..18.01 rows=801 width=16) (actual time=0.006..0.696 rows=872 loops=1)

19. 10.046 32.622 ↑ 9.5 11,911 1

Materialize (cost=19,980.23..20,547.76 rows=113,506 width=105) (actual time=21.013..32.622 rows=11,911 loops=1)

20. 3.513 22.576 ↑ 57.2 1,985 1

Sort (cost=19,980.23..20,264.00 rows=113,506 width=105) (actual time=21.010..22.576 rows=1,985 loops=1)

  • Sort Key: t.id
  • Sort Method: quicksort Memory: 247kB
21. 3.269 19.063 ↑ 57.2 1,985 1

Hash Join (cost=21.71..3,852.54 rows=113,506 width=105) (actual time=0.911..19.063 rows=1,985 loops=1)

  • Hash Cond: (t.anc = a.id)
22. 14.914 14.914 ↑ 57.2 1,985 1

CTE Scan on tree t (cost=0.00..2,270.12 rows=113,506 width=16) (actual time=0.020..14.914 rows=1,985 loops=1)

23. 0.449 0.880 ↓ 1.0 496 1

Hash (cost=15.76..15.76 rows=476 width=97) (actual time=0.879..0.880 rows=496 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 73kB
24. 0.431 0.431 ↓ 1.0 496 1

Seq Scan on acct a (cost=0.00..15.76 rows=476 width=97) (actual time=0.004..0.431 rows=496 loops=1)

25.          

SubPlan (for HashAggregate)

26. 2.480 89.280 ↑ 1.0 1 496

Aggregate (cost=2,555.30..2,555.32 rows=1 width=4) (actual time=0.180..0.180 rows=1 loops=496)

27. 86.800 86.800 ↑ 142.0 4 496

CTE Scan on tree t2 (cost=0.00..2,553.88 rows=568 width=4) (actual time=0.022..0.175 rows=4 loops=496)

  • Filter: (id = a.id)
  • Rows Removed by Filter: 1981
Planning time : 0.501 ms
Execution time : 189.309 ms