explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 73Z

Settings
# exclusive inclusive rows x rows loops node
1. 644.486 18,532.084 ↓ 10.2 3,734,652 1

Append (cost=124,147.46..133,306.66 rows=366,368 width=56) (actual time=74.007..18,532.084 rows=3,734,652 loops=1)

2.          

CTE z

3. 0.000 14,448.571 ↓ 10.2 1,867,326 1

Gather (cost=6,657.92..124,147.46 rows=183,184 width=84) (actual time=73.996..14,448.571 rows=1,867,326 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
4. 12,238.205 15,192.807 ↓ 8.7 933,663 2

Hash Join (cost=5,657.92..104,829.06 rows=107,755 width=84) (actual time=65.965..15,192.807 rows=933,663 loops=2)

  • Hash Cond: ((x.zo_id = z_2.zo_id) AND ("*SELECT* 2_1".re2_id = z_2.re_id))
5. 2,452.222 2,932.026 ↓ 2.6 2,800,989 2

Parallel Hash Join (cost=4,041.60..46,641.33 rows=1,077,554 width=116) (actual time=42.878..2,932.026 rows=2,800,989 loops=2)

  • Hash Cond: (x.co_id = n.co_id)
6. 438.358 438.358 ↑ 1.2 934,807 2

Parallel Seq Scan on rtpezocoav x (cost=0.00..32,132.84 rows=1,100,532 width=24) (actual time=1.325..438.358 rows=934,807 loops=2)

  • Filter: ((pe_id = 3) AND (ad_id = 6))
  • Rows Removed by Filter: 5850
7. 20.884 41.446 ↓ 2.6 2,824 2

Parallel Hash (cost=4,027.87..4,027.87 rows=1,098 width=104) (actual time=41.446..41.446 rows=2,824 loops=2)

  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 752kB
8. 2.524 20.562 ↓ 2.6 2,824 2

Hash Join (cost=3,987.98..4,027.87 rows=1,098 width=104) (actual time=16.796..20.562 rows=2,824 loops=2)

  • Hash Cond: (n.shop_concept = (sh.sh_id)::numeric)
9. 0.000 17.962 ↓ 2.5 2,824 2

Hash Join (cost=3,986.81..4,010.19 rows=1,108 width=90) (actual time=16.635..17.962 rows=2,824 loops=2)

  • Hash Cond: ("*SELECT* 2_1".re_id = n.re_id)
10. 0.000 0.075 ↑ 1.5 4 2

Parallel Append (cost=1.36..5.36 rows=6 width=21) (actual time=0.068..0.075 rows=4 loops=2)

11. 0.000 0.112 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=1.36..4.24 rows=1 width=21) (actual time=0.112..0.112 rows=0 loops=1)

12. 0.001 0.112 ↓ 0.0 0 1

Hash Anti Join (cost=1.36..4.23 rows=1 width=61) (actual time=0.111..0.112 rows=0 loops=1)

  • Hash Cond: ((rtpe.id = av_1.pe_id) AND (av.se_id = av_1.se_id) AND (av.ad_id = av_1.ad_id) AND (av.re_id = av_1.re_id) AND (av.re2_id = av_1.re2_id))
13. 0.007 0.111 ↓ 0.0 0 1

Nested Loop (cost=0.00..2.58 rows=9 width=25) (actual time=0.111..0.111 rows=0 loops=1)

  • Join Filter: (av.pe_id = rtpe.baseline_id)
  • Rows Removed by Join Filter: 9
14. 0.066 0.066 ↑ 1.0 1 1

Seq Scan on rtpe (cost=0.00..1.34 rows=1 width=8) (actual time=0.062..0.066 rows=1 loops=1)

  • Filter: (id = 3)
  • Rows Removed by Filter: 26
15. 0.038 0.038 ↑ 1.0 9 1

Seq Scan on rtseperereav av (cost=0.00..1.14 rows=9 width=25) (actual time=0.035..0.038 rows=9 loops=1)

  • Filter: ((se_id = 3025) AND (ad_id = 76))
16. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.16..1.16 rows=9 width=20) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtseperereav av_1 (cost=0.00..1.16 rows=9 width=20) (never executed)

  • Filter: ((pe_id = 3) AND (se_id = 3025) AND (ad_id = 76))
18. 0.016 0.016 ↑ 1.2 4 2

Parallel Seq Scan on rtseperereav (cost=0.00..1.09 rows=5 width=21) (actual time=0.011..0.016 rows=4 loops=2)

  • Filter: ((pe_id = 3) AND (se_id = 3025) AND (ad_id = 76))
19. 1.652 33.110 ↑ 1.0 1,883 1

Hash (cost=3,961.91..3,961.91 rows=1,883 width=81) (actual time=33.110..33.110 rows=1,883 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 178kB
20. 8.233 31.458 ↑ 1.0 1,883 1

Hash Right Join (cost=97.25..3,961.91 rows=1,883 width=81) (actual time=3.134..31.458 rows=1,883 loops=1)

  • Hash Cond: ((rtsepezobrav.se_id = n.se_id) AND (rtsepezobrav.pe_id = n.pe_id) AND (rtsepezobrav.zo_id = n.zo_id) AND (rtsepezobrav.br_id = n.br_id))
21. 2.975 20.139 ↑ 1.0 19,783 1

Append (cost=0.00..2,577.01 rows=19,784 width=21) (actual time=0.012..20.139 rows=19,783 loops=1)

22. 9.171 9.171 ↑ 1.0 19,783 1

Seq Scan on rtsepezobrav (cost=0.00..492.20 rows=19,783 width=21) (actual time=0.011..9.171 rows=19,783 loops=1)

  • Filter: ((se_id = 3025) AND (pe_id = 3) AND (ad_id = 75))
23. 0.001 7.993 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=937.32..1,985.89 rows=1 width=21) (actual time=7.993..7.993 rows=0 loops=1)

24. 0.001 7.992 ↓ 0.0 0 1

Hash Anti Join (cost=937.32..1,985.88 rows=1 width=61) (actual time=7.992..7.992 rows=0 loops=1)

  • Hash Cond: ((rtpe_1.id = av_1_1.pe_id) AND (av_2.se_id = av_1_1.se_id) AND (av_2.ad_id = av_1_1.ad_id) AND (av_2.zo_id = av_1_1.zo_id) AND (av_2.br_id = av_1_1.br_id))
25. 2.475 7.991 ↓ 0.0 0 1

Nested Loop (cost=0.00..691.37 rows=19,050 width=25) (actual time=7.991..7.991 rows=0 loops=1)

  • Join Filter: (av_2.pe_id = rtpe_1.baseline_id)
  • Rows Removed by Join Filter: 19783
26. 0.024 0.024 ↑ 1.0 1 1

Seq Scan on rtpe rtpe_1 (cost=0.00..1.34 rows=1 width=8) (actual time=0.020..0.024 rows=1 loops=1)

  • Filter: (id = 3)
  • Rows Removed by Filter: 26
27. 5.492 5.492 ↑ 1.0 19,783 1

Seq Scan on rtsepezobrav av_2 (cost=0.00..442.75 rows=19,783 width=25) (actual time=0.009..5.492 rows=19,783 loops=1)

  • Filter: ((se_id = 3025) AND (ad_id = 75))
28. 0.000 0.000 ↓ 0.0 0

Hash (cost=492.20..492.20 rows=19,783 width=20) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Seq Scan on rtsepezobrav av_1_1 (cost=0.00..492.20 rows=19,783 width=20) (never executed)

  • Filter: ((pe_id = 3) AND (se_id = 3025) AND (ad_id = 75))
30. 1.643 3.086 ↑ 1.0 1,883 1

Hash (cost=59.59..59.59 rows=1,883 width=84) (actual time=3.086..3.086 rows=1,883 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 192kB
31. 1.443 1.443 ↑ 1.0 1,883 1

Seq Scan on calc_nodes n (cost=0.00..59.59 rows=1,883 width=84) (actual time=0.030..1.443 rows=1,883 loops=1)

  • Filter: ((pe_id = 3) AND (se_id = 3025))
  • Rows Removed by Filter: 23
32. 0.024 0.076 ↑ 1.0 6 2

Hash (cost=1.09..1.09 rows=6 width=18) (actual time=0.076..0.076 rows=6 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.052 0.052 ↑ 1.0 6 2

Seq Scan on calc_shopconcepts sh (cost=0.00..1.09 rows=6 width=18) (actual time=0.049..0.052 rows=6 loops=2)

  • Filter: ((pe_id = 3) AND (se_id = 3025))
34. 8.597 22.576 ↑ 1.3 19,782 2

Hash (cost=1,221.16..1,221.16 rows=26,344 width=12) (actual time=22.576..22.576 rows=19,782 loops=2)

  • Buckets: 32768 Batches: 1 Memory Usage: 1107kB
35. 13.979 13.979 ↑ 1.3 19,782 2

Seq Scan on calc_zones z_2 (cost=0.00..1,221.16 rows=26,344 width=12) (actual time=0.037..13.979 rows=19,782 loops=2)

  • Filter: ((pe_id = 3) AND (se_id = 3025))
36. 17,151.245 17,151.245 ↓ 10.2 1,867,326 1

CTE Scan on z (cost=0.00..3,663.68 rows=183,184 width=56) (actual time=74.006..17,151.245 rows=1,867,326 loops=1)

37. 736.353 736.353 ↓ 10.2 1,867,326 1

CTE Scan on z z_1 (cost=0.00..3,663.68 rows=183,184 width=56) (actual time=0.047..736.353 rows=1,867,326 loops=1)

Planning time : 23.458 ms
Execution time : 18,953.030 ms