explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wSwm

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2,716.933 245,578.007 ↑ 1.0 1 1

Aggregate (cost=32,383,681.25..32,383,681.26 rows=1 width=8) (actual time=245,578.007..245,578.007 rows=1 loops=1)

2. 11,870.502 242,861.074 ↓ 1.7 51,123,960 1

Hash Left Join (cost=23,703,681.20..32,307,001.12 rows=30,672,049 width=0) (actual time=173,798.126..242,861.074 rows=51,123,960 loops=1)

  • Hash Cond: (cs.row_id = ph2.cost_settlement_id)
3. 4,369.630 130,041.804 ↓ 1.3 17,041,320 1

Hash Left Join (cost=9,203,962.61..16,955,829.45 rows=13,215,003 width=4) (actual time=72,842.793..130,041.804 rows=17,041,320 loops=1)

  • Hash Cond: (cs.row_id = lh2.cost_settlement_id)
4. 4,797.570 123,108.652 ↓ 1.3 17,041,320 1

Hash Left Join (cost=8,600,104.04..16,167,208.76 rows=13,215,003 width=4) (actual time=70,279.146..123,108.652 rows=17,041,320 loops=1)

  • Hash Cond: (cs.row_id = lh1.cost_settlement_id)
5. 11,249.821 115,564.830 ↓ 1.3 17,041,320 1

Hash Right Join (cost=7,996,245.48..15,378,588.06 rows=13,215,003 width=4) (actual time=67,532.540..115,564.830 rows=17,041,320 loops=1)

  • Hash Cond: (ph1.cost_settlement_id = cs.row_id)
6. 10,491.186 95,346.711 ↓ 1.6 31,991,995 1

Hash Join (cost=7,020,317.45..14,171,800.07 rows=19,987,402 width=4) (actual time=58,563.135..95,346.711 rows=31,991,995 loops=1)

  • Hash Cond: (ph1.category_id = c1.row_id)
7. 20,032.243 84,855.026 ↓ 1.2 63,983,990 1

Gather Merge (cost=7,020,250.24..13,238,987.41 rows=53,299,740 width=52) (actual time=58,562.608..84,855.026 rows=63,983,990 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 53,102.090 64,822.783 ↑ 1.2 21,327,997 3 / 3

Sort (cost=7,019,250.22..7,085,874.89 rows=26,649,870 width=52) (actual time=57,741.255..64,822.783 rows=21,327,997 loops=3)

  • Sort Key: ph1.cost_settlement_id, ph1.insert_ts DESC
  • Sort Method: external merge Disk: 558,176kB
  • Worker 0: Sort Method: external merge Disk: 532,048kB
  • Worker 1: Sort Method: external merge Disk: 537,712kB
9. 11,720.693 11,720.693 ↑ 1.2 21,327,997 3 / 3

Parallel Seq Scan on pred_history ph1 (cost=0.00..999,648.70 rows=26,649,870 width=52) (actual time=0.005..11,720.693 rows=21,327,997 loops=3)

10. 0.059 0.499 ↑ 1.2 486 1

Hash (cost=60.04..60.04 rows=573 width=4) (actual time=0.499..0.499 rows=486 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
11. 0.242 0.440 ↑ 1.2 486 1

Hash Join (cost=2.30..60.04 rows=573 width=4) (actual time=0.108..0.440 rows=486 loops=1)

  • Hash Cond: (c1.scheme_id = s1.row_id)
12. 0.110 0.110 ↑ 1.0 1,528 1

Seq Scan on category c1 (cost=0.00..46.28 rows=1,528 width=8) (actual time=0.003..0.110 rows=1,528 loops=1)

13. 0.003 0.088 ↑ 1.0 3 1

Hash (cost=2.26..2.26 rows=3 width=4) (actual time=0.088..0.088 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.001 0.085 ↑ 1.0 3 1

Subquery Scan on s1 (cost=2.21..2.26 rows=3 width=4) (actual time=0.082..0.085 rows=3 loops=1)

15. 0.003 0.084 ↑ 1.0 3 1

Unique (cost=2.21..2.23 rows=3 width=12) (actual time=0.082..0.084 rows=3 loops=1)

16. 0.048 0.081 ↑ 1.0 3 1

Sort (cost=2.21..2.22 rows=3 width=12) (actual time=0.081..0.081 rows=3 loops=1)

  • Sort Key: scheme.row_id, scheme.scheme_name
  • Sort Method: quicksort Memory: 25kB
17. 0.019 0.033 ↑ 1.0 3 1

Hash Join (cost=1.07..2.19 rows=3 width=12) (actual time=0.029..0.033 rows=3 loops=1)

  • Hash Cond: (scheme.bu_id = business_unit.row_id)
18. 0.007 0.007 ↑ 1.0 3 1

Seq Scan on scheme (cost=0.00..1.10 rows=3 width=16) (actual time=0.005..0.007 rows=3 loops=1)

  • Filter: (is_current AND ((scheme_name)::text ~~ '%Activity%'::text))
  • Rows Removed by Filter: 5
19. 0.004 0.007 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=4) (actual time=0.006..0.007 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.003 0.003 ↑ 1.0 3 1

Seq Scan on business_unit (cost=0.00..1.03 rows=3 width=4) (actual time=0.003..0.003 rows=3 loops=1)

21. 804.586 8,968.298 ↑ 1.0 5,680,440 1

Hash (cost=882,516.25..882,516.25 rows=5,693,663 width=4) (actual time=8,968.298..8,968.298 rows=5,680,440 loops=1)

  • Buckets: 131,072 Batches: 128 Memory Usage: 2,578kB
22. 8,163.712 8,163.712 ↑ 1.0 5,680,440 1

Seq Scan on cost_settlements cs (cost=0.00..882,516.25 rows=5,693,663 width=4) (actual time=11.942..8,163.712 rows=5,680,440 loops=1)

  • Filter: ((bu)::text = 'L48'::text)
  • Rows Removed by Filter: 2,990,135
23. 285.218 2,746.252 ↓ 1.9 1,540,072 1

Hash (cost=590,698.04..590,698.04 rows=802,122 width=4) (actual time=2,746.252..2,746.252 rows=1,540,072 loops=1)

  • Buckets: 131,072 (originally 131072) Batches: 32 (originally 16) Memory Usage: 3,073kB
24. 460.120 2,461.034 ↓ 1.9 1,540,072 1

Hash Join (cost=303,699.19..590,698.04 rows=802,122 width=4) (actual time=970.839..2,461.034 rows=1,540,072 loops=1)

  • Hash Cond: (lh1.category_id = c2.row_id)
25. 789.002 2,000.494 ↓ 1.2 2,566,790 1

Gather Merge (cost=303,631.98..553,198.47 rows=2,138,992 width=150) (actual time=970.405..2,000.494 rows=2,566,790 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
26. 849.614 1,211.492 ↑ 1.2 855,597 3 / 3

Sort (cost=302,631.96..305,305.70 rows=1,069,496 width=150) (actual time=950.342..1,211.492 rows=855,597 loops=3)

  • Sort Key: lh1.cost_settlement_id, lh1.insert_ts DESC
  • Sort Method: external merge Disk: 23,768kB
  • Worker 0: Sort Method: external merge Disk: 23,496kB
  • Worker 1: Sort Method: external merge Disk: 18,192kB
27. 361.878 361.878 ↑ 1.2 855,597 3 / 3

Parallel Seq Scan on label_history lh1 (cost=0.00..34,683.96 rows=1,069,496 width=150) (actual time=0.006..361.878 rows=855,597 loops=3)

28. 0.050 0.420 ↑ 1.2 486 1

Hash (cost=60.04..60.04 rows=573 width=4) (actual time=0.420..0.420 rows=486 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
29. 0.228 0.370 ↑ 1.2 486 1

Hash Join (cost=2.30..60.04 rows=573 width=4) (actual time=0.047..0.370 rows=486 loops=1)

  • Hash Cond: (c2.scheme_id = s2.row_id)
30. 0.112 0.112 ↑ 1.0 1,528 1

Seq Scan on category c2 (cost=0.00..46.28 rows=1,528 width=8) (actual time=0.006..0.112 rows=1,528 loops=1)

31. 0.001 0.030 ↑ 1.0 3 1

Hash (cost=2.26..2.26 rows=3 width=4) (actual time=0.030..0.030 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
32. 0.002 0.029 ↑ 1.0 3 1

Subquery Scan on s2 (cost=2.21..2.26 rows=3 width=4) (actual time=0.026..0.029 rows=3 loops=1)

33. 0.002 0.027 ↑ 1.0 3 1

Unique (cost=2.21..2.23 rows=3 width=12) (actual time=0.025..0.027 rows=3 loops=1)

34. 0.008 0.025 ↑ 1.0 3 1

Sort (cost=2.21..2.22 rows=3 width=12) (actual time=0.024..0.025 rows=3 loops=1)

  • Sort Key: scheme_1.row_id, scheme_1.scheme_name
  • Sort Method: quicksort Memory: 25kB
35. 0.006 0.017 ↑ 1.0 3 1

Hash Join (cost=1.07..2.19 rows=3 width=12) (actual time=0.014..0.017 rows=3 loops=1)

  • Hash Cond: (scheme_1.bu_id = business_unit_1.row_id)
36. 0.006 0.006 ↑ 1.0 3 1

Seq Scan on scheme scheme_1 (cost=0.00..1.10 rows=3 width=16) (actual time=0.004..0.006 rows=3 loops=1)

  • Filter: (is_current AND ((scheme_name)::text ~~ '%Activity%'::text))
  • Rows Removed by Filter: 5
37. 0.002 0.005 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=4) (actual time=0.005..0.005 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
38. 0.003 0.003 ↑ 1.0 3 1

Seq Scan on business_unit business_unit_1 (cost=0.00..1.03 rows=3 width=4) (actual time=0.002..0.003 rows=3 loops=1)

39. 191.309 2,563.522 ↓ 1.3 1,026,718 1

Hash (cost=590,698.04..590,698.04 rows=802,122 width=4) (actual time=2,563.522..2,563.522 rows=1,026,718 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 3,279kB
40. 398.594 2,372.213 ↓ 1.3 1,026,718 1

Hash Join (cost=303,699.19..590,698.04 rows=802,122 width=4) (actual time=967.364..2,372.213 rows=1,026,718 loops=1)

  • Hash Cond: (lh2.category_id = c4.row_id)
41. 762.874 1,972.819 ↓ 1.2 2,566,790 1

Gather Merge (cost=303,631.98..553,198.47 rows=2,138,992 width=150) (actual time=966.544..1,972.819 rows=2,566,790 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
42. 860.076 1,209.945 ↑ 1.2 855,597 3 / 3

Sort (cost=302,631.96..305,305.70 rows=1,069,496 width=150) (actual time=950.510..1,209.945 rows=855,597 loops=3)

  • Sort Key: lh2.cost_settlement_id, lh2.insert_ts DESC
  • Sort Method: external merge Disk: 20,984kB
  • Worker 0: Sort Method: external merge Disk: 16,792kB
  • Worker 1: Sort Method: external merge Disk: 27,688kB
43. 349.869 349.869 ↑ 1.2 855,597 3 / 3

Parallel Seq Scan on label_history lh2 (cost=0.00..34,683.96 rows=1,069,496 width=150) (actual time=0.006..349.869 rows=855,597 loops=3)

44. 0.138 0.800 ↓ 1.2 660 1

Hash (cost=60.04..60.04 rows=573 width=4) (actual time=0.800..0.800 rows=660 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 32kB
45. 0.408 0.662 ↓ 1.2 660 1

Hash Join (cost=2.30..60.04 rows=573 width=4) (actual time=0.141..0.662 rows=660 loops=1)

  • Hash Cond: (c4.scheme_id = s4.row_id)
46. 0.193 0.193 ↑ 1.0 1,528 1

Seq Scan on category c4 (cost=0.00..46.28 rows=1,528 width=8) (actual time=0.011..0.193 rows=1,528 loops=1)

47. 0.004 0.061 ↑ 1.0 3 1

Hash (cost=2.26..2.26 rows=3 width=4) (actual time=0.061..0.061 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
48. 0.001 0.057 ↑ 1.0 3 1

Subquery Scan on s4 (cost=2.21..2.26 rows=3 width=4) (actual time=0.054..0.057 rows=3 loops=1)

49. 0.004 0.056 ↑ 1.0 3 1

Unique (cost=2.21..2.23 rows=3 width=12) (actual time=0.053..0.056 rows=3 loops=1)

50. 0.013 0.052 ↑ 1.0 3 1

Sort (cost=2.21..2.22 rows=3 width=12) (actual time=0.052..0.052 rows=3 loops=1)

  • Sort Key: scheme_2.row_id, scheme_2.scheme_name
  • Sort Method: quicksort Memory: 25kB
51. 0.020 0.039 ↑ 1.0 3 1

Hash Join (cost=1.07..2.19 rows=3 width=12) (actual time=0.036..0.039 rows=3 loops=1)

  • Hash Cond: (scheme_2.bu_id = business_unit_2.row_id)
52. 0.010 0.010 ↑ 1.0 3 1

Seq Scan on scheme scheme_2 (cost=0.00..1.10 rows=3 width=16) (actual time=0.007..0.010 rows=3 loops=1)

  • Filter: (is_current AND ((scheme_name)::text ~~ '%Element%'::text))
  • Rows Removed by Filter: 5
53. 0.004 0.009 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=4) (actual time=0.009..0.009 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
54. 0.005 0.005 ↑ 1.0 3 1

Seq Scan on business_unit business_unit_2 (cost=0.00..1.03 rows=3 width=4) (actual time=0.004..0.005 rows=3 loops=1)

55. 6,566.044 100,948.768 ↓ 1.6 31,991,995 1

Hash (cost=14,171,800.07..14,171,800.07 rows=19,987,402 width=4) (actual time=100,948.768..100,948.768 rows=31,991,995 loops=1)

  • Buckets: 131,072 Batches: 512 Memory Usage: 3,197kB
56. 11,118.228 94,382.724 ↓ 1.6 31,991,995 1

Hash Join (cost=7,020,317.45..14,171,800.07 rows=19,987,402 width=4) (actual time=58,128.690..94,382.724 rows=31,991,995 loops=1)

  • Hash Cond: (ph2.category_id = c3.row_id)
57. 19,452.467 83,263.743 ↓ 1.2 63,983,990 1

Gather Merge (cost=7,020,250.24..13,238,987.41 rows=53,299,740 width=52) (actual time=58,127.920..83,263.743 rows=63,983,990 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
58. 52,478.794 63,811.276 ↑ 1.2 21,327,997 3 / 3

Sort (cost=7,019,250.22..7,085,874.89 rows=26,649,870 width=52) (actual time=57,287.500..63,811.276 rows=21,327,997 loops=3)

  • Sort Key: ph2.cost_settlement_id, ph2.insert_ts DESC
  • Sort Method: external merge Disk: 545,872kB
  • Worker 0: Sort Method: external merge Disk: 563,272kB
  • Worker 1: Sort Method: external merge Disk: 518,792kB
59. 11,332.482 11,332.482 ↑ 1.2 21,327,997 3 / 3

Parallel Seq Scan on pred_history ph2 (cost=0.00..999,648.70 rows=26,649,870 width=52) (actual time=0.005..11,332.482 rows=21,327,997 loops=3)

60. 0.122 0.753 ↓ 1.2 660 1

Hash (cost=60.04..60.04 rows=573 width=4) (actual time=0.753..0.753 rows=660 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 32kB
61. 0.401 0.631 ↓ 1.2 660 1

Hash Join (cost=2.30..60.04 rows=573 width=4) (actual time=0.113..0.631 rows=660 loops=1)

  • Hash Cond: (c3.scheme_id = s3.row_id)
62. 0.190 0.190 ↑ 1.0 1,528 1

Seq Scan on category c3 (cost=0.00..46.28 rows=1,528 width=8) (actual time=0.009..0.190 rows=1,528 loops=1)

63. 0.002 0.040 ↑ 1.0 3 1

Hash (cost=2.26..2.26 rows=3 width=4) (actual time=0.039..0.040 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
64. 0.002 0.038 ↑ 1.0 3 1

Subquery Scan on s3 (cost=2.21..2.26 rows=3 width=4) (actual time=0.033..0.038 rows=3 loops=1)

65. 0.004 0.036 ↑ 1.0 3 1

Unique (cost=2.21..2.23 rows=3 width=12) (actual time=0.032..0.036 rows=3 loops=1)

66. 0.008 0.032 ↑ 1.0 3 1

Sort (cost=2.21..2.22 rows=3 width=12) (actual time=0.032..0.032 rows=3 loops=1)

  • Sort Key: scheme_3.row_id, scheme_3.scheme_name
  • Sort Method: quicksort Memory: 25kB
67. 0.007 0.024 ↑ 1.0 3 1

Hash Join (cost=1.07..2.19 rows=3 width=12) (actual time=0.021..0.024 rows=3 loops=1)

  • Hash Cond: (scheme_3.bu_id = business_unit_3.row_id)
68. 0.009 0.009 ↑ 1.0 3 1

Seq Scan on scheme scheme_3 (cost=0.00..1.10 rows=3 width=16) (actual time=0.007..0.009 rows=3 loops=1)

  • Filter: (is_current AND ((scheme_name)::text ~~ '%Element%'::text))
  • Rows Removed by Filter: 5
69. 0.003 0.008 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=4) (actual time=0.007..0.008 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
70. 0.005 0.005 ↑ 1.0 3 1

Seq Scan on business_unit business_unit_3 (cost=0.00..1.03 rows=3 width=4) (actual time=0.004..0.005 rows=3 loops=1)

Planning time : 2.258 ms
Execution time : 245,722.966 ms