explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QiE2

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 94,613.791 ↓ 0.0 0 1

Unique (cost=160,671,233.60..160,671,233.62 rows=1 width=144) (actual time=94,613.791..94,613.791 rows=0 loops=1)

2. 0.013 94,613.790 ↓ 0.0 0 1

Sort (cost=160,671,233.60..160,671,233.61 rows=1 width=144) (actual time=94,613.790..94,613.790 rows=0 loops=1)

  • Sort Key: (COALESCE(a.agency_discount, '0'::numeric)), a.clause_start, a.clause_end, a.timeband_start, a.timeband_end, a.sales_id, a.period_days
  • Sort Method: quicksort Memory: 25kB
3. 0.002 94,613.777 ↓ 0.0 0 1

Subquery Scan on a (cost=160,394,849.71..160,671,233.59 rows=1 width=144) (actual time=94,613.777..94,613.777 rows=0 loops=1)

4. 31.686 94,613.775 ↓ 0.0 0 1

Merge Join (cost=160,394,849.71..160,671,233.58 rows=1 width=293) (actual time=94,613.775..94,613.775 rows=0 loops=1)

  • Merge Cond: (bc.object_id = c.contract_id)
  • Join Filter: ((('20191201'::text >= (CASE WHEN ((COALESCE(bc.start_date, c.start_date))::text = '00000000'::text) THEN c.start_date ELSE bc.start_date END)::text) AND ('20191201'::text <= (CASE WHEN ((COALESCE(bc.end_date, c.end_date))::text = '00000000'::text) THEN c.end_date ELSE bc.end_date END)::text)) OR (('20200104'::text >= (CASE WHEN ((COALESCE(bc.start_date, c.start_date))::text = '00000000'::text) THEN c.start_date ELSE bc.start_date END)::text) AND ('20200104'::text <= (CASE WHEN ((COALESCE(bc.end_date, c.end_date))::text = '00000000'::text) THEN c.end_date ELSE bc.end_date END)::text)))
5.          

CTE channel_filter

6. 17.902 86.994 ↓ 10,641.0 10,641 1

Nested Loop (cost=3,163.29..7,589.81 rows=1 width=33) (actual time=5.174..86.994 rows=10,641 loops=1)

  • Join Filter: (cf.channel_grouping_id = gh.grouping_header_id)
7. 9.997 47.810 ↓ 10,641.0 10,641 1

Hash Join (cost=3,162.72..7,588.33 rows=1 width=24) (actual time=5.163..47.810 rows=10,641 loops=1)

  • Hash Cond: (gl.grouped_item_id = ssg.sales_schedule_group_id)
8. 10.900 37.766 ↓ 10.8 10,641 1

Nested Loop (cost=3,152.85..7,575.87 rows=987 width=20) (actual time=5.109..37.766 rows=10,641 loops=1)

9. 3.137 12.770 ↓ 3.5 1,762 1

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

10. 2.095 6.107 ↓ 3.5 1,763 1

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

  • Group Key: bc_1.clause_filter_id
11. 1.450 4.012 ↓ 3.6 1,800 1

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

12. 0.962 0.962 ↑ 19.1 20 1

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

  • Filter: (((status)::text = 'CONSTAAUTHOR'::text) AND (to_date((end_date)::text, 'YYYYMMDD'::text) >= CURRENT_DATE))
  • Rows Removed by Filter: 3001
13. 1.600 1.600 ↓ 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.007..0.080 rows=90 loops=20)

  • Index Cond: ((object_id = co_1.contract_id) AND (object_type = 'OC'::text))
  • Heap Fetches: 1800
14. 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)
15. 14.096 14.096 ↓ 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.005..0.008 rows=6 loops=1,762)

  • Index Cond: (grouping_header_id = cf.channel_grouping_id)
  • Heap Fetches: 10641
16. 0.013 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
17. 0.034 0.034 ↑ 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.034 rows=20 loops=1)

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

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

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

CTE rate_zone_filter

20. 4.060 1,062.399 ↑ 33,038.0 2,659 1

Gather (cost=12,397.23..160,387,046.06 rows=87,848,159 width=69) (actual time=928.585..1,062.399 rows=2,659 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
21. 25.082 1,058.339 ↑ 41,313.1 886 3

Hash Join (cost=11,397.23..150,960,670.67 rows=36,603,400 width=69) (actual time=930.024..1,058.339 rows=886 loops=3)

  • Hash Cond: (cf_1.clause_filter_id = bc_1_1.clause_filter_id)
22. 55.584 1,024.458 ↑ 253,446.6 42,440 3

Hash Join (cost=8,233.96..121,857,534.15 rows=10,756,273,500 width=41) (actual time=267.131..1,024.458 rows=42,440 loops=3)

  • Hash Cond: (rzp.period_start_time = p_1.period_start_time)
23. 59.295 839.305 ↑ 469.3 42,440 3

Hash Join (cost=4,542.08..795,967.83 rows=19,919,025 width=45) (actual time=137.295..839.305 rows=42,440 loops=3)

  • Hash Cond: (rzp.period_end_time = p.period_end_time)
24. 260.492 649.671 ↓ 1.2 42,441 3

Nested Loop (cost=134.96..566,308.96 rows=36,887 width=50) (actual time=6.321..649.671 rows=42,441 loops=3)

  • Join Filter: (cf_1.rate_zone_grouping_id = gh_1.grouping_header_id)
25. 47.079 389.175 ↓ 1.2 42,441 3

Hash Join (cost=134.39..511,070.70 rows=36,887 width=41) (actual time=6.054..389.175 rows=42,441 loops=3)

  • Hash Cond: (gl_1.grouped_item_id = rzp.rate_zone_id)
26. 293.187 336.693 ↑ 3.3 36,248 3

Nested Loop (cost=0.98..509,208.58 rows=120,875 width=20) (actual time=0.593..336.693 rows=36,248 loops=3)

27. 43.502 43.502 ↑ 1.3 49,154 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.293..43.502 rows=49,154 loops=3)

  • Heap Fetches: 52515
28. 0.004 0.004 ↑ 2.0 1 147,461

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.004..0.004 rows=1 loops=147,461)

  • Index Cond: (grouping_header_id = cf_1.rate_zone_grouping_id)
  • Heap Fetches: 36691
29. 2.862 5.403 ↑ 1.0 4,418 3

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

  • Buckets: 8192 Batches: 1 Memory Usage: 315kB
30. 2.541 2.541 ↑ 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.008..2.541 rows=4,418 loops=3)

31. 0.004 0.004 ↑ 1.0 1 127,324

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

  • Index Cond: (grouping_header_id = gl_1.grouping_header_id)
32. 0.219 130.339 ↑ 548.3 130 3

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

  • Buckets: 131072 Batches: 2 Memory Usage: 1030kB
33. 67.494 130.120 ↑ 548.3 130 3

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

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

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

35. 0.089 5.820 ↑ 1.0 132 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
36. 3.510 5.731 ↑ 1.0 132 3

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

  • Group Key: p.period_end_time
37. 2.221 2.221 ↑ 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.221 rows=4,418 loops=3)

38. 0.092 129.569 ↑ 547.0 77 3

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

  • Buckets: 65536 Batches: 1 Memory Usage: 516kB
39. 64.144 129.477 ↑ 547.0 77 3

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

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

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

41. 0.053 5.446 ↑ 1.0 78 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
42. 3.160 5.393 ↑ 1.0 78 3

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

  • Group Key: p_1.period_start_time
43. 2.233 2.233 ↑ 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.015..2.233 rows=4,418 loops=3)

44. 0.949 8.799 ↓ 3.5 1,763 3

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 78kB
45. 2.115 7.850 ↓ 3.5 1,763 3

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

  • Group Key: bc_1_1.clause_filter_id
46. 4.306 5.735 ↓ 3.6 1,800 3

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

47. 1.287 1.287 ↑ 19.1 20 3

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

  • Filter: (((status)::text = 'CONSTAAUTHOR'::text) AND (to_date((end_date)::text, 'YYYYMMDD'::text) >= CURRENT_DATE))
  • Rows Removed by Filter: 3001
48. 0.142 0.142 ↓ 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.032..0.142 rows=90 loops=60)

  • Index Cond: ((object_id = co_1_1.contract_id) AND (object_type = 'OC'::text))
  • Heap Fetches: 1800
49. 137.561 94,581.129 ↓ 231.6 77,371 1

Merge Join (cost=213.56..931,874.08 rows=334 width=75) (actual time=90,117.949..94,581.129 rows=77,371 loops=1)

  • Merge Cond: (bc.object_id = co.contract_id)
50. 696.665 94,242.480 ↓ 9.9 926 1

Nested Loop Left Join (cost=0.42..3,087,549.68 rows=94 width=71) (actual time=89,898.263..94,242.480 rows=926 loops=1)

  • Join Filter: (bc.clause_filter_id = rz.clause_filter_id)
  • Rows Removed by Join Filter: 1650369
  • Filter: ((COALESCE(rz.filter_type, ' '::character varying))::text <> 'FILTYPEXCLUD'::text)
51. 44,829.871 91,757.956 ↓ 621.0 621 1

Nested Loop (cost=0.42..12,864.12 rows=1 width=35) (actual time=88,831.578..91,757.956 rows=621 loops=1)

  • Join Filter: (bc.clause_filter_id = chn.clause_filter_id)
  • Rows Removed by Join Filter: 106516351
52. 66.594 66.594 ↓ 5.1 10,011 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.740..66.594 rows=10,011 loops=1)

  • Index Cond: (((object_type)::text = 'OC'::text) AND ((clause_type)::text = 'AGEDIS'::text) AND (applied_filters > 0))
53. 46,861.491 46,861.491 ↓ 10,640.0 10,640 10,011

CTE Scan on channel_filter chn (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..4.681 rows=10,640 loops=10,011)

54. 1,787.859 1,787.859 ↑ 33,038.0 2,659 621

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

55. 201.088 201.088 ↓ 1.4 250,788 1

Index Only Scan using contract_organisation_contract_id_idx on contract_organisation co (cost=0.42..11,733.18 rows=176,618 width=4) (actual time=0.016..201.088 rows=250,788 loops=1)

  • Heap Fetches: 250788
56. 0.960 0.960 ↓ 2.0 2 1

Index Scan using contract_pkey on contract c (cost=0.28..233.00 rows=1 width=22) (actual time=0.933..0.960 rows=2 loops=1)

  • Filter: (((status)::text = 'CONSTAAUTHOR'::text) AND ((contract_id = 36573) OR (contract_id = 36838)) AND (to_date((end_date)::text, 'YYYYMMDD'::text) >= CURRENT_DATE))
  • Rows Removed by Filter: 3019
Planning time : 10.905 ms
Execution time : 94,614.349 ms