explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PjLs

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.049 ↓ 0.0 0 1

Update on mediawise_inventory_rates i (cost=295,838,845.28..295,839,542.46 rows=1 width=438) (actual time=0.049..0.049 rows=0 loops=1)

2.          

CTE discounts

3. 0.000 0.047 ↓ 0.0 0 1

Unique (cost=295,838,844.64..295,838,844.72 rows=4 width=84) (actual time=0.047..0.047 rows=0 loops=1)

4. 0.014 0.047 ↓ 0.0 0 1

Sort (cost=295,838,844.64..295,838,844.65 rows=4 width=84) (actual time=0.047..0.047 rows=0 loops=1)

  • Sort Key: (COALESCE(a.agency_discount, '0'::numeric)), ((a.clause_start)::date), ((a.clause_end)::date), a.timeband_start, a.timeband_end, a.sales_id, a.period_days
  • Sort Method: quicksort Memory: 25kB
5. 0.000 0.033 ↓ 0.0 0 1

Subquery Scan on a (cost=294,108,764.22..295,838,844.60 rows=4 width=84) (actual time=0.033..0.033 rows=0 loops=1)

6. 0.000 0.033 ↓ 0.0 0 1

Nested Loop (cost=294,108,764.22..295,838,844.52 rows=4 width=293) (actual time=0.033..0.033 rows=0 loops=1)

7.          

CTE channel_filter

8. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=3,158.22..7,328.74 rows=1 width=33) (never executed)

  • Hash Cond: (gl.grouped_item_id = ssg.sales_schedule_group_id)
9. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3,154.34..7,322.67 rows=833 width=29) (never executed)

  • Join Filter: (cf.channel_grouping_id = gl.grouping_header_id)
10. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3,153.78..6,992.73 rows=492 width=25) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=3,153.21..3,462.52 rows=492 width=8) (never executed)

12. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=3,152.79..3,157.71 rows=492 width=4) (never executed)

  • Group Key: bc_1.clause_filter_id
13. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..3,151.56 rows=492 width=4) (never executed)

14. 0.000 0.000 ↓ 0.0 0

Seq Scan on contract co_1 (cost=0.00..138.12 rows=378 width=4) (never executed)

  • Filter: (((status)::text = 'CONSTAAUTHOR'::text) AND (to_date((end_date)::text, 'YYYYMMDD'::text) >= CURRENT_DATE))
15. 0.000 0.000 ↓ 0.0 0

Index Only Scan using business_clause_object_id_idx on business_clause bc_1 (cost=0.42..7.96 rows=1 width=8) (never executed)

  • Index Cond: ((object_id = co_1.contract_id) AND (object_type = 'OC'::text))
  • Heap Fetches: 0
16. 0.000 0.000 ↓ 0.0 0

Index Only Scan using clause_filter_clause_filter_id_channel_idx on clause_filter cf (cost=0.42..0.62 rows=1 width=8) (never executed)

  • Index Cond: (clause_filter_id = bc_1.clause_filter_id)
  • Heap Fetches: 0
17. 0.000 0.000 ↓ 0.0 0

Index Scan using grouping_header_pkey on grouping_header gh (cost=0.56..7.18 rows=1 width=17) (never executed)

  • Index Cond: (grouping_header_id = cf.channel_grouping_id)
18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using grouping_line_grouping_header_id_idx on grouping_line gl (cost=0.56..0.65 rows=2 width=12) (never executed)

  • Index Cond: (grouping_header_id = gh.grouping_header_id)
  • Heap Fetches: 0
19. 0.000 0.000 ↓ 0.0 0

Hash (cost=3.63..3.63 rows=20 width=8) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Seq Scan on sales_schedule_group ssg (cost=0.00..3.63 rows=20 width=8) (never executed)

  • Filter: (single_sales_channel_id IS NOT NULL)
21.          

CTE rate_zone_filter

22. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=11,397.40..294,101,424.53 rows=72,814,868 width=69) (never executed)

  • Hash Cond: (cf_1.clause_filter_id = bc_1_1.clause_filter_id)
23. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8,233.54..237,377,365.11 rows=20,952,626,400 width=41) (never executed)

  • Hash Cond: (rzp.period_start_time = p_1.period_start_time)
24. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=4,541.66..1,559,623.33 rows=38,801,160 width=45) (never executed)

  • Hash Cond: (rzp.period_end_time = p.period_end_time)
25. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=134.53..1,116,770.52 rows=71,854 width=50) (never executed)

  • Hash Cond: (gl_1.grouped_item_id = rzp.rate_zone_id)
26. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.13..1,113,224.09 rows=239,510 width=29) (never executed)

  • Join Filter: (cf_1.rate_zone_grouping_id = gl_1.grouping_header_id)
27. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..1,018,283.60 rows=141,574 width=25) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Seq Scan on clause_filter cf_1 (cost=0.00..2,456.74 rows=141,574 width=8) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using grouping_header_pkey on grouping_header gh_1 (cost=0.56..7.18 rows=1 width=17) (never executed)

  • Index Cond: (grouping_header_id = cf_1.rate_zone_grouping_id)
30. 0.000 0.000 ↓ 0.0 0

Index Only Scan using grouping_line_grouping_header_id_idx on grouping_line gl_1 (cost=0.56..0.65 rows=2 width=12) (never executed)

  • Index Cond: (grouping_header_id = gh_1.grouping_header_id)
  • Heap Fetches: 0
31. 0.000 0.000 ↓ 0.0 0

Hash (cost=78.18..78.18 rows=4,418 width=25) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Seq Scan on rate_zone_period rzp (cost=0.00..78.18 rows=4,418 width=25) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Hash (cost=3,167.13..3,167.13 rows=71,280 width=13) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=93.52..3,167.13 rows=71,280 width=13) (never executed)

  • Hash Cond: ((se.seconds)::numeric = p.period_end_time)
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on seconds se (cost=0.00..2,770.00 rows=108,000 width=6) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Hash (cost=91.86..91.86 rows=132 width=7) (never executed)

37. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=89.23..90.55 rows=132 width=7) (never executed)

  • Group Key: p.period_end_time
38. 0.000 0.000 ↓ 0.0 0

Seq Scan on rate_zone_period p (cost=0.00..78.18 rows=4,418 width=7) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Hash (cost=3,165.38..3,165.38 rows=42,120 width=12) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=91.76..3,165.38 rows=42,120 width=12) (never executed)

  • Hash Cond: ((s.seconds)::numeric = p_1.period_start_time)
41. 0.000 0.000 ↓ 0.0 0

Seq Scan on seconds s (cost=0.00..2,770.00 rows=108,000 width=6) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Hash (cost=90.79..90.79 rows=78 width=6) (never executed)

43. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=89.23..90.01 rows=78 width=6) (never executed)

  • Group Key: p_1.period_start_time
44. 0.000 0.000 ↓ 0.0 0

Seq Scan on rate_zone_period p_1 (cost=0.00..78.18 rows=4,418 width=6) (never executed)

45. 0.000 0.000 ↓ 0.0 0

Hash (cost=3,157.71..3,157.71 rows=492 width=4) (never executed)

46. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=3,152.79..3,157.71 rows=492 width=4) (never executed)

  • Group Key: bc_1_1.clause_filter_id
47. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..3,151.56 rows=492 width=4) (never executed)

48. 0.000 0.000 ↓ 0.0 0

Seq Scan on contract co_1_1 (cost=0.00..138.12 rows=378 width=4) (never executed)

  • Filter: (((status)::text = 'CONSTAAUTHOR'::text) AND (to_date((end_date)::text, 'YYYYMMDD'::text) >= CURRENT_DATE))
49. 0.000 0.000 ↓ 0.0 0

Index Only Scan using business_clause_object_id_idx on business_clause bc_1_1 (cost=0.42..7.96 rows=1 width=8) (never executed)

  • Index Cond: ((object_id = co_1_1.contract_id) AND (object_type = 'OC'::text))
  • Heap Fetches: 0
50. 0.001 0.033 ↓ 0.0 0 1

Nested Loop (cost=10.54..1,729,393.94 rows=1 width=89) (actual time=0.033..0.033 rows=0 loops=1)

  • Join Filter: ((('20191223'::text >= (CASE WHEN ((COALESCE(bc.start_date, c.start_date))::text = '00000000'::text) THEN c.start_date ELSE bc.start_date END)::text) AND ('20191223'::text <= (CASE WHEN ((COALESCE(bc.end_date, c.end_date))::text = '00000000'::text) THEN c.end_date ELSE bc.end_date END)::text)) OR (('20191227'::text >= (CASE WHEN ((COALESCE(bc.start_date, c.start_date))::text = '00000000'::text) THEN c.start_date ELSE bc.start_date END)::text) AND ('20191227'::text <= (CASE WHEN ((COALESCE(bc.end_date, c.end_date))::text = '00000000'::text) THEN c.end_date ELSE bc.end_date END)::text)))
51. 0.010 0.032 ↓ 0.0 0 1

Hash Right Join (cost=10.26..1,729,385.60 rows=1 width=71) (actual time=0.032..0.032 rows=0 loops=1)

  • Hash Cond: (rz.clause_filter_id = bc.clause_filter_id)
  • Filter: ((COALESCE(rz.filter_type, ' '::character varying))::text <> 'FILTYPEXCLUD'::text)
52. 0.000 0.000 ↓ 0.0 0

CTE Scan on rate_zone_filter rz (cost=0.00..1,456,297.36 rows=72,814,868 width=86) (never executed)

53. 0.000 0.022 ↓ 0.0 0 1

Hash (cost=10.25..10.25 rows=1 width=35) (actual time=0.022..0.022 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
54. 0.000 0.022 ↓ 0.0 0 1

Nested Loop (cost=0.42..10.25 rows=1 width=35) (actual time=0.022..0.022 rows=0 loops=1)

  • Join Filter: (bc.clause_filter_id = chn.clause_filter_id)
55. 0.022 0.022 ↓ 0.0 0 1

Index Scan using business_clause_object_id_idx on business_clause bc (cost=0.42..10.21 rows=1 width=31) (actual time=0.022..0.022 rows=0 loops=1)

  • Index Cond: ((object_id = 36573) AND ((object_type)::text = 'OC'::text) AND ((clause_type)::text = 'AGEDIS'::text) AND (applied_filters > 0))
56. 0.000 0.000 ↓ 0.0 0

CTE Scan on channel_filter chn (cost=0.00..0.02 rows=1 width=8) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Index Scan using contract_contract_id_idx on contract c (cost=0.28..8.31 rows=1 width=22) (never executed)

  • Index Cond: ((contract_id = 36573) AND ((status)::text = 'CONSTAAUTHOR'::text))
  • Filter: (to_date((end_date)::text, 'YYYYMMDD'::text) >= CURRENT_DATE)
58. 0.000 0.000 ↓ 0.0 0

Index Only Scan using contract_organisation_contract_id_idx on contract_organisation co (cost=0.42..693.20 rows=410 width=4) (never executed)

  • Index Cond: (contract_id = 36573)
  • Heap Fetches: 0
59. 0.000 0.049 ↓ 0.0 0 1

Nested Loop (cost=0.56..697.74 rows=1 width=438) (actual time=0.049..0.049 rows=0 loops=1)

60. 0.049 0.049 ↓ 0.0 0 1

CTE Scan on discounts v (cost=0.00..0.08 rows=4 width=192) (actual time=0.049..0.049 rows=0 loops=1)

61. 0.000 0.000 ↓ 0.0 0

Index Scan using mediawise_inventory_rates_req_id_idx on mediawise_inventory_rates i (cost=0.56..174.40 rows=1 width=294) (never executed)

  • Index Cond: ((request_id = 3645) AND (tx_date >= v.clause_start) AND (tx_date <= v.clause_end) AND (sales_channel_id = v.sales_id) AND (start_time_timeband_id >= COALESCE(v.timeband_start, 1)) AND (start_time_timeband_id <= COALESCE(v.timeband_end, 48)))
  • Filter: ((NOT is_special_event) AND (substr(COALESCE(v.period_days, '1111111'::text), (weekday_sort)::integer, 1) = '1'::text))
Planning time : 6.185 ms
Execution time : 0.457 ms