explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hzSE

Settings
# exclusive inclusive rows x rows loops node
1. 97.466 95,842.572 ↓ 2,491.8 102,165 1

Merge Join (cost=160,394,880.99..160,650,233.77 rows=41 width=193) (actual time=90,050.261..95,842.572 rows=102,165 loops=1)

  • Merge Cond: (bc.object_id = c.contract_id)
2.          

CTE channel_filter

3. 18.010 85.214 ↓ 10,641.0 10,641 1

Nested Loop (cost=3,163.29..7,589.89 rows=1 width=33) (actual time=5.167..85.214 rows=10,641 loops=1)

  • Join Filter: (cf.channel_grouping_id = gh.grouping_header_id)
4. 9.941 45.922 ↓ 10,641.0 10,641 1

Hash Join (cost=3,162.72..7,588.41 rows=1 width=24) (actual time=5.155..45.922 rows=10,641 loops=1)

  • Hash Cond: (gl.grouped_item_id = ssg.sales_schedule_group_id)
5. 10.750 35.934 ↓ 10.8 10,641 1

Nested Loop (cost=3,152.85..7,575.95 rows=987 width=20) (actual time=5.100..35.934 rows=10,641 loops=1)

6. 3.230 12.850 ↓ 3.5 1,762 1

Nested Loop (cost=3,152.29..3,463.44 rows=507 width=8) (actual time=5.088..12.850 rows=1,762 loops=1)

7. 2.107 6.094 ↓ 3.5 1,763 1

HashAggregate (cost=3,151.87..3,156.94 rows=507 width=4) (actual time=5.079..6.094 rows=1,763 loops=1)

  • Group Key: bc_1.clause_filter_id
8. 1.457 3.987 ↓ 3.6 1,800 1

Nested Loop (cost=0.42..3,150.60 rows=507 width=4) (actual time=0.927..3.987 rows=1,800 loops=1)

9. 0.950 0.950 ↑ 19.1 20 1

Seq Scan on contract co_1 (cost=0.00..139.42 rows=382 width=4) (actual time=0.908..0.950 rows=20 loops=1)

  • Filter: (((status)::text = 'CONSTAAUTHOR'::text) AND (to_date((end_date)::text, 'YYYYMMDD'::text) >= CURRENT_DATE))
  • Rows Removed by Filter: 3001
10. 1.580 1.580 ↓ 90.0 90 20

Index Only Scan using business_clause_object_id_idx on business_clause bc_1 (cost=0.42..7.87 rows=1 width=8) (actual time=0.006..0.079 rows=90 loops=20)

  • Index Cond: ((object_id = co_1.contract_id) AND (object_type = 'OC'::text))
  • Heap Fetches: 1800
11. 3.526 3.526 ↑ 1.0 1 1,763

Index Scan using clause_filter_clause_filter_id_rz_idx on clause_filter cf (cost=0.42..0.61 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,763)

  • Index Cond: (clause_filter_id = bc_1.clause_filter_id)
12. 12.334 12.334 ↓ 3.0 6 1,762

Index Only Scan using grouping_line_grouping_header_id_idx on grouping_line gl (cost=0.56..8.09 rows=2 width=12) (actual time=0.004..0.007 rows=6 loops=1,762)

  • Index Cond: (grouping_header_id = cf.channel_grouping_id)
  • Heap Fetches: 10641
13. 0.012 0.047 ↑ 1.0 20 1

Hash (cost=9.62..9.62 rows=20 width=8) (actual time=0.047..0.047 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.035 0.035 ↑ 1.0 20 1

Seq Scan on sales_schedule_group ssg (cost=0.00..9.62 rows=20 width=8) (actual time=0.007..0.035 rows=20 loops=1)

  • Filter: (single_sales_channel_id IS NOT NULL)
  • Rows Removed by Filter: 42
15. 21.282 21.282 ↑ 1.0 1 10,641

Index Scan using grouping_header_pkey on grouping_header gh (cost=0.56..1.47 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=10,641)

  • Index Cond: (grouping_header_id = gl.grouping_header_id)
16.          

CTE rate_zone_filter

17. 4.317 938.392 ↑ 33,038.0 2,659 1

Gather (cost=12,397.23..160,387,077.26 rows=87,848,159 width=69) (actual time=823.738..938.392 rows=2,659 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 24.296 934.075 ↑ 41,313.1 886 3

Hash Join (cost=11,397.23..150,960,701.87 rows=36,603,400 width=69) (actual time=827.543..934.075 rows=886 loops=3)

  • Hash Cond: (cf_1.clause_filter_id = bc_1_1.clause_filter_id)
19. 52.011 901.261 ↑ 253,446.6 42,440 3

Hash Join (cost=8,233.96..121,857,565.35 rows=10,756,273,500 width=41) (actual time=258.291..901.261 rows=42,440 loops=3)

  • Hash Cond: (rzp.period_start_time = p_1.period_start_time)
20. 55.454 721.192 ↑ 469.3 42,440 3

Hash Join (cost=4,542.08..795,999.04 rows=19,919,025 width=45) (actual time=129.987..721.192 rows=42,440 loops=3)

  • Hash Cond: (rzp.period_end_time = p.period_end_time)
21. 202.713 541.769 ↓ 1.2 42,441 3

Nested Loop (cost=134.96..566,340.16 rows=36,887 width=50) (actual time=5.291..541.769 rows=42,441 loops=3)

  • Join Filter: (cf_1.rate_zone_grouping_id = gh_1.grouping_header_id)
22. 44.083 339.053 ↓ 1.2 42,441 3

Hash Join (cost=134.39..511,081.02 rows=36,887 width=41) (actual time=5.252..339.053 rows=42,441 loops=3)

  • Hash Cond: (gl_1.grouped_item_id = rzp.rate_zone_id)
23. 252.885 289.905 ↑ 3.3 36,248 3

Nested Loop (cost=0.98..509,218.90 rows=120,875 width=20) (actual time=0.143..289.905 rows=36,248 loops=3)

24. 37.017 37.017 ↑ 1.3 49,156 3

Parallel Index Only Scan using clause_filter_clause_filter_id_rz_idx on clause_filter cf_1 (cost=0.42..5,674.87 rows=62,078 width=8) (actual time=0.039..37.017 rows=49,156 loops=3)

  • Heap Fetches: 52784
25. 0.003 0.003 ↑ 2.0 1 147,467

Index Only Scan using grouping_line_grouping_header_id_idx on grouping_line gl_1 (cost=0.56..8.09 rows=2 width=12) (actual time=0.003..0.003 rows=1 loops=147,467)

  • Index Cond: (grouping_header_id = cf_1.rate_zone_grouping_id)
  • Heap Fetches: 40216
26. 2.692 5.065 ↑ 1.0 4,418 3

Hash (cost=78.18..78.18 rows=4,418 width=25) (actual time=5.064..5.065 rows=4,418 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 315kB
27. 2.373 2.373 ↑ 1.0 4,418 3

Seq Scan on rate_zone_period rzp (cost=0.00..78.18 rows=4,418 width=25) (actual time=0.013..2.373 rows=4,418 loops=3)

28. 0.003 0.003 ↑ 1.0 1 127,324

Index Scan using grouping_header_pkey on grouping_header gh_1 (cost=0.56..1.49 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=127,324)

  • Index Cond: (grouping_header_id = gl_1.grouping_header_id)
29. 0.256 123.969 ↑ 548.3 130 3

Hash (cost=3,167.13..3,167.13 rows=71,280 width=13) (actual time=123.969..123.969 rows=130 loops=3)

  • Buckets: 131072 Batches: 2 Memory Usage: 1030kB
30. 63.957 123.713 ↑ 548.3 130 3

Hash Join (cost=93.52..3,167.13 rows=71,280 width=13) (actual time=26.783..123.713 rows=130 loops=3)

  • Hash Cond: ((se.seconds)::numeric = p.period_end_time)
31. 54.390 54.390 ↑ 1.0 108,000 3

Seq Scan on seconds se (cost=0.00..2,770.00 rows=108,000 width=6) (actual time=0.006..54.390 rows=108,000 loops=3)

32. 0.085 5.366 ↑ 1.0 132 3

Hash (cost=91.86..91.86 rows=132 width=7) (actual time=5.366..5.366 rows=132 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
33. 3.212 5.281 ↑ 1.0 132 3

HashAggregate (cost=89.23..90.55 rows=132 width=7) (actual time=5.212..5.281 rows=132 loops=3)

  • Group Key: p.period_end_time
34. 2.069 2.069 ↑ 1.0 4,418 3

Seq Scan on rate_zone_period p (cost=0.00..78.18 rows=4,418 width=7) (actual time=0.005..2.069 rows=4,418 loops=3)

35. 0.093 128.058 ↑ 547.0 77 3

Hash (cost=3,165.38..3,165.38 rows=42,120 width=12) (actual time=128.057..128.058 rows=77 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 516kB
36. 63.239 127.965 ↑ 547.0 77 3

Hash Join (cost=91.76..3,165.38 rows=42,120 width=12) (actual time=14.347..127.965 rows=77 loops=3)

  • Hash Cond: ((s.seconds)::numeric = p_1.period_start_time)
37. 59.244 59.244 ↑ 1.0 108,000 3

Seq Scan on seconds s (cost=0.00..2,770.00 rows=108,000 width=6) (actual time=0.017..59.244 rows=108,000 loops=3)

38. 0.052 5.482 ↑ 1.0 78 3

Hash (cost=90.79..90.79 rows=78 width=6) (actual time=5.482..5.482 rows=78 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
39. 3.163 5.430 ↑ 1.0 78 3

HashAggregate (cost=89.23..90.01 rows=78 width=6) (actual time=5.389..5.430 rows=78 loops=3)

  • Group Key: p_1.period_start_time
40. 2.267 2.267 ↑ 1.0 4,418 3

Seq Scan on rate_zone_period p_1 (cost=0.00..78.18 rows=4,418 width=6) (actual time=0.014..2.267 rows=4,418 loops=3)

41. 0.952 8.518 ↓ 3.5 1,763 3

Hash (cost=3,156.94..3,156.94 rows=507 width=4) (actual time=8.518..8.518 rows=1,763 loops=3)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 78kB
42. 2.098 7.566 ↓ 3.5 1,763 3

HashAggregate (cost=3,151.87..3,156.94 rows=507 width=4) (actual time=6.671..7.566 rows=1,763 loops=3)

  • Group Key: bc_1_1.clause_filter_id
43. 4.030 5.468 ↓ 3.6 1,800 3

Nested Loop (cost=0.42..3,150.60 rows=507 width=4) (actual time=1.311..5.468 rows=1,800 loops=3)

44. 1.311 1.311 ↑ 19.1 20 3

Seq Scan on contract co_1_1 (cost=0.00..139.42 rows=382 width=4) (actual time=1.251..1.311 rows=20 loops=3)

  • Filter: (((status)::text = 'CONSTAAUTHOR'::text) AND (to_date((end_date)::text, 'YYYYMMDD'::text) >= CURRENT_DATE))
  • Rows Removed by Filter: 3001
45. 0.127 0.127 ↓ 90.0 90 60

Index Only Scan using business_clause_object_id_idx on business_clause bc_1_1 (cost=0.42..7.87 rows=1 width=8) (actual time=0.013..0.127 rows=90 loops=60)

  • Index Cond: ((object_id = co_1_1.contract_id) AND (object_type = 'OC'::text))
  • Heap Fetches: 1800
46. 158.007 95,743.580 ↓ 304.1 102,165 1

Merge Join (cost=213.56..857,462.88 rows=336 width=121) (actual time=90,048.864..95,743.580 rows=102,165 loops=1)

  • Merge Cond: (bc.object_id = co.contract_id)
47. 927.339 95,396.985 ↓ 12.9 1,225 1

Nested Loop Left Join (cost=0.42..2,867,929.28 rows=95 width=113) (actual time=89,859.632..95,396.985 rows=1,225 loops=1)

  • Join Filter: (bc.clause_filter_id = rz.clause_filter_id)
  • Rows Removed by Join Filter: 2205830
48. 45,181.963 92,573.096 ↓ 830.0 830 1

Nested Loop (cost=0.42..12,864.12 rows=1 width=35) (actual time=88,916.980..92,573.096 rows=830 loops=1)

  • Join Filter: (bc.clause_filter_id = chn.clause_filter_id)
  • Rows Removed by Join Filter: 107356219
49. 23.278 23.278 ↓ 5.2 10,089 1

Index Scan using business_clause_object_id_idx on business_clause bc (cost=0.42..12,820.30 rows=1,947 width=31) (actual time=1.759..23.278 rows=10,089 loops=1)

  • Index Cond: (((object_type)::text = 'OC'::text) AND ((clause_type)::text = 'AGEDIS'::text) AND (applied_filters > 0))
50. 47,367.855 47,367.855 ↓ 10,641.0 10,641 10,089

CTE Scan on channel_filter chn (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..4.695 rows=10,641 loops=10,089)

51. 1,896.550 1,896.550 ↑ 33,038.0 2,659 830

CTE Scan on rate_zone_filter rz (cost=0.00..1,756,963.18 rows=87,848,159 width=86) (actual time=0.993..2.285 rows=2,659 loops=830)

52. 188.588 188.588 ↓ 1.6 275,842 1

Index Only Scan using contract_organisation_contract_id_idx on contract_organisation co (cost=0.42..11,733.18 rows=176,618 width=8) (actual time=0.037..188.588 rows=275,842 loops=1)

  • Heap Fetches: 275842
53. 1.526 1.526 ↑ 19.1 20 1

Index Scan using contract_pkey on contract c (cost=0.28..217.89 rows=382 width=22) (actual time=1.385..1.526 rows=20 loops=1)

  • Filter: (((status)::text = 'CONSTAAUTHOR'::text) AND (to_date((end_date)::text, 'YYYYMMDD'::text) >= CURRENT_DATE))
  • Rows Removed by Filter: 3001
Planning time : 8.933 ms
Execution time : 95,882.474 ms