explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8hnu

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

Insert on b_tmp.booking_item (cost=1,678,329,772,522,179.00..862,882,407,476,612,992.00 rows=8,201,350,746,310,122,496 width=137) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* (cost=1,678,329,772,522,179.00..862,882,407,476,612,992.00 rows=8,201,350,746,310,122,496 width=137) (actual rows= loops=)

  • Output: "*SELECT*".pax_id, "*SELECT*".travel_id, "*SELECT*".service_group, "*SELECT*".description, "*SELECT*".unit_price, "*SELECT*".booking_confirmation_amount, "*SELECT*".total_invoiced_revenue, "*SELECT*"."bit", "*SELECT*".provision, "*SELECT*".is_cash
3. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1,678,329,772,522,179.00..739,862,146,281,961,216.00 rows=8,201,350,746,310,122,496 width=185) (actual rows= loops=)

  • Output: booking_item_1.pax_id, booking_item_1.travel_id, booking_item_1.service_group, booking_item_1.description, booking_item_1.unit_price, COALESCE(CASE WHEN ((booking_item_1.service_group <> 'Fakultative Reisezusatzleistungen'::text) AND (booking_item_1.is_cancelled IS FALSE)) THEN (((booking.booking_confirmation_amount)::numeric * (booking_item_1.unit_price)::numeric) / NULLIF((booking_item_line_amount.total_line_amount_not_cancelled_pax)::numeric, '0'::numeric)) WHEN ((booking_item_1.service_group <> 'Fakultative Reisezusatzleistungen'::text) AND (cancelled_booking.number_of_not_cancelled_pax = 0)) THEN (((booking.booking_confirmation_amount)::numeric * (booking_item_1.unit_price)::numeric) / NULLIF((booking_item_line_amount.total_line_amount)::numeric, '0'::numeric)) ELSE '0'::numeric END, '0'::numeric), COALESCE(CASE WHEN ((booking_item_1.service_group <> 'Fakultative Reisezusatzleistungen'::text) AND (booking_item_1.is_cancelled IS FALSE)) THEN (((booking.total_invoiced_revenue)::numeric * (booking_item_1.unit_price)::numeric) / NULLIF((booking_item_line_amount.total_line_amount_not_cancelled_pax)::numeric, '0'::numeric)) WHEN ((booking_item_1.service_group <> 'Fakultative Reisezusatzleistungen'::text) AND (cancelled_booking.number_of_not_cancelled_pax = 0)) THEN (((booking.total_invoiced_revenue)::numeric * (booking_item_1.unit_price)::numeric) / NULLIF((booking_item_line_amount.total_line_amount)::numeric, '0'::numeric)) ELSE '0'::numeric END, '0'::numeric), booking_item_1."bit", booking_item_1.provision, booking_item_1.is_cash
  • Merge Cond: (booking_item_1.booking_id = booking.booking_id)
4.          

CTE tmp_pax

5. 0.000 0.000 ↓ 0.0

Merge Join (cost=607,280.92..274,066,737.72 rows=18,230,279,120 width=34) (actual rows= loops=)

  • Output: pax.pax_id, pax.booking_id, pax.person_id, pax.is_valid, pax.is_cancelled, travel.travel_id, booking_1.booking_confirmation_amount, booking_1.total_invoiced_revenue
  • Merge Cond: (booking_1.booking_id = pax.booking_id)
6. 0.000 0.000 ↓ 0.0

Sort (cost=171,542.04..174,177.04 rows=1,054,000 width=28) (actual rows= loops=)

  • Output: booking_1.booking_confirmation_amount, booking_1.total_invoiced_revenue, booking_1.booking_id, travel.travel_id
  • Sort Key: booking_1.booking_id
7. 0.000 0.000 ↓ 0.0

Hash Join (cost=90.08..66,102.81 rows=1,054,000 width=28) (actual rows= loops=)

  • Output: booking_1.booking_confirmation_amount, booking_1.total_invoiced_revenue, booking_1.booking_id, travel.travel_id
  • Inner Unique: true
  • Hash Cond: (booking_1.travel_id = travel.travel_id)
8. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.booking booking_1 (cost=0.00..63,240.00 rows=1,054,000 width=28) (actual rows= loops=)

  • Output: booking_1.booking_id, booking_1.web_booking_number, booking_1.person_id, booking_1.main_booker, booking_1.main_booker_is_presenter, booking_1.travel_id, booking_1.booking_date, booking_1.booking_day_id, booking_1.tour_date, booking_1.tour_day_id, booking_1.cancel_date, booking_1.cancel_day_id, booking_1.initial_date, booking_1.initial_day_id, booking_1.status_id, booking_1.previous_status_id, booking_1.tour_number, booking_1.is_customer_cancelled, booking_1.is_company_cancelled, booking_1.is_waiting_list, booking_1.is_valid, booking_1.booking_category, booking_1.number_of_pax, booking_1.booking_confirmation_amount, booking_1.advertising_medium, booking_1.actions_code, booking_1.capture_source, booking_1.change_date, booking_1.total_invoiced_revenue, booking_1.diff_total_invoiced_revenue, booking_1.is_sap_adjustment, booking_1.is_first_time_booking, booking_1.bus_station_zip_code, booking_1.bus_station_place, booking_1.number_of_single_room_pax, booking_1.number_of_half_board_pax, booking_1.number_of_train_to_airport_pax, booking_1.number_of_feeder_pax, booking_1.total_discount_value, booking_1.total_cancellation_revenue, booking_1.open_cancellation_revenue, booking_1.paid_cancellation_revenue, booking_1.days_between_booking_and_travel_date
9. 0.000 0.000 ↓ 0.0

Hash (cost=65.59..65.59 rows=1,959 width=4) (actual rows= loops=)

  • Output: travel.travel_id
  • Buckets: 2048 Batches: 1 Memory Usage: 85kB
10. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.travel (cost=0.00..65.59 rows=1,959 width=4) (actual rows= loops=)

  • Output: travel.travel_id
11. 0.000 0.000 ↓ 0.0

Sort (cost=435,738.88..444,387.02 rows=3,459,256 width=14) (actual rows= loops=)

  • Output: pax.pax_id, pax.booking_id, pax.person_id, pax.is_valid, pax.is_cancelled
  • Sort Key: pax.booking_id
12. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.pax (cost=0.00..60,028.56 rows=3,459,256 width=14) (actual rows= loops=)

  • Output: pax.pax_id, pax.booking_id, pax.person_id, pax.is_valid, pax.is_cancelled
13.          

CTE possible_tours_of_main_travel

14. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=293,682.74..359,865.01 rows=2,595,911 width=20) (actual rows= loops=)

  • Output: rzb2.record_id, abs(date_part('epoch'::text, (rzb2.created_at - tour.tour_date))), pax_1.travel_id
  • Merge Cond: ((pax_1.travel_id = tour.travel_id) AND (pax_1.tour_number = tour.tour_number))
15. 0.000 0.000 ↓ 0.0

Sort (cost=221,042.64..221,470.80 rows=171,265 width=24) (actual rows= loops=)

  • Output: rzb2.record_id, rzb2.created_at, pax_1.travel_id, pax_1.tour_number
  • Sort Key: pax_1.travel_id, pax_1.tour_number
16. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=7,036.70..206,154.69 rows=171,265 width=24) (actual rows= loops=)

  • Output: rzb2.record_id, rzb2.created_at, pax_1.travel_id, pax_1.tour_number
  • Hash Cond: ((pax_1.person_id = rzb2.person_id) AND (COALESCE(travel_1.main_travel_id, pax_1.travel_id) = rzb2.main_travel_id))
17. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=90.08..69,218.83 rows=3,459,256 width=16) (actual rows= loops=)

  • Output: pax_1.travel_id, pax_1.tour_number, pax_1.person_id, travel_1.main_travel_id
  • Inner Unique: true
  • Hash Cond: (pax_1.travel_id = travel_1.travel_id)
18. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.pax pax_1 (cost=0.00..60,028.56 rows=3,459,256 width=12) (actual rows= loops=)

  • Output: pax_1.pax_id, pax_1.person_id, pax_1.travel_id, pax_1.tour_number, pax_1.booking_id, pax_1.is_valid, pax_1.is_cancelled, pax_1.is_waiting_list, pax_1.pax_is_new_customer, pax_1.booking_category
19. 0.000 0.000 ↓ 0.0

Hash (cost=65.59..65.59 rows=1,959 width=8) (actual rows= loops=)

  • Output: travel_1.travel_id, travel_1.main_travel_id
  • Buckets: 2048 Batches: 1 Memory Usage: 92kB
20. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.travel travel_1 (cost=0.00..65.59 rows=1,959 width=8) (actual rows= loops=)

  • Output: travel_1.travel_id, travel_1.main_travel_id
21. 0.000 0.000 ↓ 0.0

Hash (cost=4,377.65..4,377.65 rows=171,265 width=28) (actual rows= loops=)

  • Output: rzb2.record_id, rzb2.created_at, rzb2.person_id, rzb2.main_travel_id
  • Buckets: 262144 Batches: 1 Memory Usage: 12076kB
22. 0.000 0.000 ↓ 0.0

Seq Scan on b_data.rzb2 (cost=0.00..4,377.65 rows=171,265 width=28) (actual rows= loops=)

  • Output: rzb2.record_id, rzb2.created_at, rzb2.person_id, rzb2.main_travel_id
23. 0.000 0.000 ↓ 0.0

Sort (cost=72,640.10..74,155.83 rows=606,291 width=16) (actual rows= loops=)

  • Output: tour.tour_date, tour.travel_id, tour.tour_number
  • Sort Key: tour.travel_id, tour.tour_number
24. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.tour (cost=0.00..14,406.91 rows=606,291 width=16) (actual rows= loops=)

  • Output: tour.tour_date, tour.travel_id, tour.tour_number
25.          

CTE travel_id_mapping

26. 0.000 0.000 ↓ 0.0

HashAggregate (cost=393,381.88..393,781.88 rows=40,000 width=20) (actual rows= loops=)

  • Output: possible_tours_of_main_travel.record_id, (first_value(possible_tours_of_main_travel.travel_id) OVER (?)), possible_tours_of_main_travel.time_delta_service_date_tour_date
  • Group Key: possible_tours_of_main_travel.record_id, first_value(possible_tours_of_main_travel.travel_id) OVER (?)
27. 0.000 0.000 ↓ 0.0

WindowAgg (cost=328,484.11..380,402.33 rows=2,595,911 width=20) (actual rows= loops=)

  • Output: possible_tours_of_main_travel.record_id, first_value(possible_tours_of_main_travel.travel_id) OVER (?), possible_tours_of_main_travel.time_delta_service_date_tour_date
28. 0.000 0.000 ↓ 0.0

Sort (cost=328,484.11..334,973.88 rows=2,595,911 width=20) (actual rows= loops=)

  • Output: possible_tours_of_main_travel.record_id, possible_tours_of_main_travel.time_delta_service_date_tour_date, possible_tours_of_main_travel.travel_id
  • Sort Key: possible_tours_of_main_travel.record_id, possible_tours_of_main_travel.time_delta_service_date_tour_date
29. 0.000 0.000 ↓ 0.0

CTE Scan on possible_tours_of_main_travel (cost=0.00..51,918.22 rows=2,595,911 width=20) (actual rows= loops=)

  • Output: possible_tours_of_main_travel.record_id, possible_tours_of_main_travel.time_delta_service_date_tour_date, possible_tours_of_main_travel.travel_id
30.          

CTE booking_item

31. 0.000 0.000 ↓ 0.0

Append (cost=90.08..553,550,347,298,181.44 rows=1,556,233,538,199,264 width=127) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Result (cost=90.08..545,768,495,223,902.81 rows=1,556,233,505,319,627 width=127) (actual rows= loops=)

  • Output: pax_2.pax_id, pax_2.travel_id, pax_2.booking_id, pax_2.is_valid, pax_2.is_cancelled, ('Basispreis'::text), travel_2.travel_title, travel_2.base_price, (NULL::text), (0), (false)
33. 0.000 0.000 ↓ 0.0

Append (cost=90.08..526,315,576,407,407.50 rows=1,556,233,505,319,627 width=123) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=90.08..412,563,683.43 rows=18,230,279,120 width=123) (actual rows= loops=)

  • Output: pax_2.pax_id, pax_2.travel_id, pax_2.booking_id, pax_2.is_valid, pax_2.is_cancelled, 'Basispreis'::text, travel_2.travel_title, travel_2.base_price, NULL::text, 0, false
  • Inner Unique: true
  • Hash Cond: (pax_2.travel_id = travel_2.travel_id)
35. 0.000 0.000 ↓ 0.0

CTE Scan on tmp_pax pax_2 (cost=0.00..364,605,582.40 rows=18,230,279,120 width=14) (actual rows= loops=)

  • Output: pax_2.pax_id, pax_2.booking_id, pax_2.person_id, pax_2.is_valid, pax_2.is_cancelled, pax_2.travel_id, pax_2.booking_confirmation_amount, pax_2.total_invoiced_revenue
36. 0.000 0.000 ↓ 0.0

Hash (cost=65.59..65.59 rows=1,959 width=44) (actual rows= loops=)

  • Output: travel_2.travel_title, travel_2.base_price, travel_2.travel_id
  • Buckets: 2048 Batches: 1 Memory Usage: 170kB
37. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.travel travel_2 (cost=0.00..65.59 rows=1,959 width=44) (actual rows= loops=)

  • Output: travel_2.travel_title, travel_2.base_price, travel_2.travel_id
38. 0.000 0.000 ↓ 0.0

Merge Join (cost=3,733,446,779.76..3,588,524,144,255.76 rows=95,593,203,107,588 width=123) (actual rows= loops=)

  • Output: pax_3.pax_id, pax_3.travel_id, pax_3.booking_id, pax_3.is_valid, pax_3.is_cancelled, 'Rabatt'::text, ((((COALESCE(rwc.discount_text, ''::text) || ' ('::text) || rwc.advertising_medium) || rwc.actions_code) || ')'::text), CASE WHEN ((rwc.discount_value > '0'::double precision) AND (rwc.discount_value < '1'::double precision)) THEN ((rwc.discount_value * travel_3.base_price) * '-1'::double precision) ELSE (rwc.discount_value * '-1'::double precision) END, NULL::text, 0, false
  • Merge Cond: (booking_2.booking_id = pax_3.booking_id)
39. 0.000 0.000 ↓ 0.0

Sort (cost=295,398.28..298,020.10 rows=1,048,730 width=47) (actual rows= loops=)

  • Output: booking_2.booking_id, rwc.discount_text, rwc.advertising_medium, rwc.actions_code, rwc.discount_value
  • Sort Key: booking_2.booking_id
40. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=178,991.94..190,524.17 rows=1,048,730 width=47) (actual rows= loops=)

  • Output: booking_2.booking_id, rwc.discount_text, rwc.advertising_medium, rwc.actions_code, rwc.discount_value
  • Merge Cond: ((booking_2.advertising_medium = rwc.advertising_medium) AND (booking_2.actions_code = rwc.actions_code) AND (booking_2.travel_id = rwc.travel_id))
  • Filter: (COALESCE(rwc.discount_value, '0'::double precision) <> '0'::double precision)
41. 0.000 0.000 ↓ 0.0

Sort (cost=168,679.23..171,314.23 rows=1,054,000 width=76) (actual rows= loops=)

  • Output: booking_2.booking_id, booking_2.advertising_medium, booking_2.actions_code, booking_2.travel_id
  • Sort Key: booking_2.advertising_medium, booking_2.actions_code, booking_2.travel_id
42. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.booking booking_2 (cost=0.00..63,240.00 rows=1,054,000 width=76) (actual rows= loops=)

  • Output: booking_2.booking_id, booking_2.advertising_medium, booking_2.actions_code, booking_2.travel_id
43. 0.000 0.000 ↓ 0.0

Sort (cost=10,312.72..10,560.49 rows=99,111 width=43) (actual rows= loops=)

  • Output: rwc.discount_text, rwc.advertising_medium, rwc.actions_code, rwc.discount_value, rwc.travel_id
  • Sort Key: rwc.advertising_medium, rwc.actions_code, rwc.travel_id
44. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.rwc (cost=0.00..2,088.11 rows=99,111 width=43) (actual rows= loops=)

  • Output: rwc.discount_text, rwc.advertising_medium, rwc.actions_code, rwc.discount_value, rwc.travel_id
45. 0.000 0.000 ↓ 0.0

Materialize (cost=3,733,151,381.48..3,824,302,777.08 rows=18,230,279,120 width=22) (actual rows= loops=)

  • Output: pax_3.pax_id, pax_3.travel_id, pax_3.booking_id, pax_3.is_valid, pax_3.is_cancelled, travel_3.base_price
46. 0.000 0.000 ↓ 0.0

Sort (cost=3,733,151,381.48..3,778,727,079.28 rows=18,230,279,120 width=22) (actual rows= loops=)

  • Output: pax_3.pax_id, pax_3.travel_id, pax_3.booking_id, pax_3.is_valid, pax_3.is_cancelled, travel_3.base_price
  • Sort Key: pax_3.booking_id
47. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=90.08..412,563,683.43 rows=18,230,279,120 width=22) (actual rows= loops=)

  • Output: pax_3.pax_id, pax_3.travel_id, pax_3.booking_id, pax_3.is_valid, pax_3.is_cancelled, travel_3.base_price
  • Inner Unique: true
  • Hash Cond: (pax_3.travel_id = travel_3.travel_id)
48. 0.000 0.000 ↓ 0.0

CTE Scan on tmp_pax pax_3 (cost=0.00..364,605,582.40 rows=18,230,279,120 width=14) (actual rows= loops=)

  • Output: pax_3.pax_id, pax_3.booking_id, pax_3.person_id, pax_3.is_valid, pax_3.is_cancelled, pax_3.travel_id, pax_3.booking_confirmation_amount, pax_3.total_invoiced_revenue
49. 0.000 0.000 ↓ 0.0

Hash (cost=65.59..65.59 rows=1,959 width=12) (actual rows= loops=)

  • Output: travel_3.base_price, travel_3.travel_id
  • Buckets: 2048 Batches: 1 Memory Usage: 101kB
50. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.travel travel_3 (cost=0.00..65.59 rows=1,959 width=12) (actual rows= loops=)

  • Output: travel_3.base_price, travel_3.travel_id
51. 0.000 0.000 ↓ 0.0

Merge Join (cost=965,773,638.52..2,275,843,583.03 rows=74,457,017,496 width=123) (actual rows= loops=)

  • Output: pax_4.pax_id, pax_4.travel_id, pax_4.booking_id, pax_4.is_valid, pax_4.is_cancelled, 'Zimmer Zuschlag'::text, rso.option_description, rso.option_price, NULL::text, 0, false
  • Merge Cond: ((booking_3.booking_id = pax_4.booking_id) AND (booking_3.travel_id = rso.travel_id))
52. 0.000 0.000 ↓ 0.0

Sort (cost=168,679.23..171,314.23 rows=1,054,000 width=12) (actual rows= loops=)

  • Output: booking_3.booking_id, booking_3.travel_id
  • Sort Key: booking_3.booking_id, booking_3.travel_id
53. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.booking booking_3 (cost=0.00..63,240.00 rows=1,054,000 width=12) (actual rows= loops=)

  • Output: booking_3.booking_id, booking_3.travel_id
54. 0.000 0.000 ↓ 0.0

Materialize (cost=965,604,959.29..979,733,425.61 rows=2,825,693,264 width=58) (actual rows= loops=)

  • Output: pax_4.pax_id, pax_4.travel_id, pax_4.booking_id, pax_4.is_valid, pax_4.is_cancelled, rso.option_description, rso.option_price, rso.travel_id
55. 0.000 0.000 ↓ 0.0

Sort (cost=965,604,959.29..972,669,192.45 rows=2,825,693,264 width=58) (actual rows= loops=)

  • Output: pax_4.pax_id, pax_4.travel_id, pax_4.booking_id, pax_4.is_valid, pax_4.is_cancelled, rso.option_description, rso.option_price, rso.travel_id
  • Sort Key: pax_4.booking_id, rso.travel_id
56. 0.000 0.000 ↓ 0.0

Hash Join (cost=93,895.46..461,319,974.06 rows=2,825,693,264 width=58) (actual rows= loops=)

  • Output: pax_4.pax_id, pax_4.travel_id, pax_4.booking_id, pax_4.is_valid, pax_4.is_cancelled, rso.option_description, rso.option_price, rso.travel_id
  • Hash Cond: (pax_4.pax_id = pax2.pax_id)
57. 0.000 0.000 ↓ 0.0

CTE Scan on tmp_pax pax_4 (cost=0.00..364,605,582.40 rows=18,230,279,120 width=14) (actual rows= loops=)

  • Output: pax_4.pax_id, pax_4.booking_id, pax_4.person_id, pax_4.is_valid, pax_4.is_cancelled, pax_4.travel_id, pax_4.booking_confirmation_amount, pax_4.total_invoiced_revenue
58. 0.000 0.000 ↓ 0.0

Hash (cost=87,193.15..87,193.15 rows=536,185 width=48) (actual rows= loops=)

  • Output: pax2.pax_id, rso.option_description, rso.option_price, rso.travel_id
  • Buckets: 33554432 (originally 1048576) Batches: 256 (originally 1) Memory Usage: 2192690kB
59. 0.000 0.000 ↓ 0.0

Hash Join (cost=182.39..87,193.15 rows=536,185 width=48) (actual rows= loops=)

  • Output: pax2.pax_id, rso.option_description, rso.option_price, rso.travel_id
  • Hash Cond: (pax2.booking_category = rso.option_utype)
60. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.pax pax2 (cost=0.00..60,028.56 rows=3,459,256 width=36) (actual rows= loops=)

  • Output: pax2.pax_id, pax2.person_id, pax2.travel_id, pax2.tour_number, pax2.booking_id, pax2.is_valid, pax2.is_cancelled, pax2.is_waiting_list, pax2.pax_is_new_customer, pax2.booking_category
61. 0.000 0.000 ↓ 0.0

Hash (cost=182.00..182.00 rows=31 width=76) (actual rows= loops=)

  • Output: rso.option_description, rso.option_price, rso.travel_id, rso.option_utype
  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 602kB
62. 0.000 0.000 ↓ 0.0

Seq Scan on b_data.rso (cost=0.00..182.00 rows=31 width=76) (actual rows= loops=)

  • Output: rso.option_description, rso.option_price, rso.travel_id, rso.option_utype
  • Filter: (rso.option_type = 'RSZ'::text)
63. 0.000 0.000 ↓ 0.0

Merge Join (cost=3,649,779,388.62..79,227,264,257.73 rows=4,316,105,175,486 width=123) (actual rows= loops=)

  • Output: pax_5.pax_id, pax_5.travel_id, pax_5.booking_id, pax_5.is_valid, pax_5.is_cancelled, 'Flughafenzuschlag'::text, ('Flughafen '::text || rfd.airport_code_1), rfd.airport_surcharge, NULL::text, 0, false
  • Merge Cond: (booking_4.booking_id = pax_5.booking_id)
64. 0.000 0.000 ↓ 0.0

Sort (cost=192,122.16..192,240.54 rows=47,351 width=48) (actual rows= loops=)

  • Output: booking_4.booking_id, rfd.airport_code_1, rfd.airport_surcharge
  • Sort Key: booking_4.booking_id
65. 0.000 0.000 ↓ 0.0

Merge Join (cost=180,053.10..188,445.09 rows=47,351 width=48) (actual rows= loops=)

  • Output: booking_4.booking_id, rfd.airport_code_1, rfd.airport_surcharge
  • Merge Cond: ((rfd.travel_id = booking_4.travel_id) AND (rfd.tour_number = booking_4.tour_number))
66. 0.000 0.000 ↓ 0.0

Sort (cost=11,373.88..11,378.37 rows=1,797 width=48) (actual rows= loops=)

  • Output: rfd.airport_code_1, rfd.airport_surcharge, rfd.travel_id, rfd.tour_number
  • Sort Key: rfd.travel_id, rfd.tour_number
67. 0.000 0.000 ↓ 0.0

Seq Scan on b_data.rfd (cost=0.00..11,276.74 rows=1,797 width=48) (actual rows= loops=)

  • Output: rfd.airport_code_1, rfd.airport_surcharge, rfd.travel_id, rfd.tour_number
  • Filter: (rfd.round_trip = 'H'::text)
68. 0.000 0.000 ↓ 0.0

Sort (cost=168,679.23..171,314.23 rows=1,054,000 width=16) (actual rows= loops=)

  • Output: booking_4.booking_id, booking_4.travel_id, booking_4.tour_number
  • Sort Key: booking_4.travel_id, booking_4.tour_number
69. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.booking booking_4 (cost=0.00..63,240.00 rows=1,054,000 width=16) (actual rows= loops=)

  • Output: booking_4.booking_id, booking_4.travel_id, booking_4.tour_number
70. 0.000 0.000 ↓ 0.0

Materialize (cost=3,649,587,266.46..3,740,738,662.06 rows=18,230,279,120 width=14) (actual rows= loops=)

  • Output: pax_5.pax_id, pax_5.travel_id, pax_5.booking_id, pax_5.is_valid, pax_5.is_cancelled
71. 0.000 0.000 ↓ 0.0

Sort (cost=3,649,587,266.46..3,695,162,964.26 rows=18,230,279,120 width=14) (actual rows= loops=)

  • Output: pax_5.pax_id, pax_5.travel_id, pax_5.booking_id, pax_5.is_valid, pax_5.is_cancelled
  • Sort Key: pax_5.booking_id
72. 0.000 0.000 ↓ 0.0

CTE Scan on tmp_pax pax_5 (cost=0.00..364,605,582.40 rows=18,230,279,120 width=14) (actual rows= loops=)

  • Output: pax_5.pax_id, pax_5.travel_id, pax_5.booking_id, pax_5.is_valid, pax_5.is_cancelled
73. 0.000 0.000 ↓ 0.0

WindowAgg (cost=448,330,112,855,680.75..499,297,586,591,499.56 rows=1,456,213,535,309,112 width=123) (actual rows= loops=)

  • Output: pax_6.pax_id, pax_6.travel_id, pax_6.booking_id, pax_6.is_valid, pax_6.is_cancelled, 'Saisonzuschlag'::text, ((('Saison '::text || to_char(min(tour_1.tour_date) OVER (?), 'YYYY-MM-DD'::text)) || ' - '::text) || to_char(max(tour_1.tour_date) OVER (?), 'YYYY-MM-DD'::text)), tour_1.saisonal_surcharge, NULL::text, 0, false
74. 0.000 0.000 ↓ 0.0

Sort (cost=448,330,112,855,680.75..451,970,646,693,953.50 rows=1,456,213,535,309,112 width=30) (actual rows= loops=)

  • Output: pax_6.travel_id, tour_1.saisonal_surcharge, pax_6.pax_id, pax_6.booking_id, pax_6.is_valid, pax_6.is_cancelled, tour_1.tour_date
  • Sort Key: pax_6.travel_id, tour_1.saisonal_surcharge
75. 0.000 0.000 ↓ 0.0

Merge Join (cost=3,652,024,160.18..21,846,900,914,464.14 rows=1,456,213,535,309,112 width=30) (actual rows= loops=)

  • Output: pax_6.travel_id, tour_1.saisonal_surcharge, pax_6.pax_id, pax_6.booking_id, pax_6.is_valid, pax_6.is_cancelled, tour_1.tour_date
  • Merge Cond: (booking_5.booking_id = pax_6.booking_id)
76. 0.000 0.000 ↓ 0.0

Sort (cost=2,436,893.72..2,476,833.14 rows=15,975,768 width=24) (actual rows= loops=)

  • Output: booking_5.booking_id, tour_1.tour_date, tour_1.saisonal_surcharge
  • Sort Key: booking_5.booking_id
77. 0.000 0.000 ↓ 0.0

Merge Join (cost=241,319.33..525,442.45 rows=15,975,768 width=24) (actual rows= loops=)

  • Output: booking_5.booking_id, tour_1.tour_date, tour_1.saisonal_surcharge
  • Merge Cond: ((tour_1.travel_id = booking_5.travel_id) AND (tour_1.tour_number = booking_5.tour_number))
78. 0.000 0.000 ↓ 0.0

Sort (cost=72,640.10..74,155.83 rows=606,291 width=24) (actual rows= loops=)

  • Output: tour_1.tour_date, tour_1.saisonal_surcharge, tour_1.travel_id, tour_1.tour_number
  • Sort Key: tour_1.travel_id, tour_1.tour_number
79. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.tour tour_1 (cost=0.00..14,406.91 rows=606,291 width=24) (actual rows= loops=)

  • Output: tour_1.tour_date, tour_1.saisonal_surcharge, tour_1.travel_id, tour_1.tour_number
80. 0.000 0.000 ↓ 0.0

Sort (cost=168,679.23..171,314.23 rows=1,054,000 width=16) (actual rows= loops=)

  • Output: booking_5.booking_id, booking_5.travel_id, booking_5.tour_number
  • Sort Key: booking_5.travel_id, booking_5.tour_number
81. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.booking booking_5 (cost=0.00..63,240.00 rows=1,054,000 width=16) (actual rows= loops=)

  • Output: booking_5.booking_id, booking_5.travel_id, booking_5.tour_number
82. 0.000 0.000 ↓ 0.0

Materialize (cost=3,649,587,266.46..3,740,738,662.06 rows=18,230,279,120 width=14) (actual rows= loops=)

  • Output: pax_6.pax_id, pax_6.travel_id, pax_6.booking_id, pax_6.is_valid, pax_6.is_cancelled
83. 0.000 0.000 ↓ 0.0

Sort (cost=3,649,587,266.46..3,695,162,964.26 rows=18,230,279,120 width=14) (actual rows= loops=)

  • Output: pax_6.pax_id, pax_6.travel_id, pax_6.booking_id, pax_6.is_valid, pax_6.is_cancelled
  • Sort Key: pax_6.booking_id
84. 0.000 0.000 ↓ 0.0

CTE Scan on tmp_pax pax_6 (cost=0.00..364,605,582.40 rows=18,230,279,120 width=14) (actual rows= loops=)

  • Output: pax_6.pax_id, pax_6.travel_id, pax_6.booking_id, pax_6.is_valid, pax_6.is_cancelled
85. 0.000 0.000 ↓ 0.0

Merge Join (cost=3,685,371,252.02..4,047,420,333.56 rows=17,974,430,825 width=123) (actual rows= loops=)

  • Output: pax_7.pax_id, pax_7.travel_id, pax_7.booking_id, pax_7.is_valid, pax_7.is_cancelled, 'Reisezusatzleistungen'::text, vrab.description, vrab.unit_price, vmab."bit", 0, false
  • Merge Cond: ((vmab.primary_customer_number = pax_7.person_id) AND (vmab.travel_id = pax_7.travel_id))
86. 0.000 0.000 ↓ 0.0

Sort (cost=177,971.56..178,070.16 rows=39,439 width=84) (actual rows= loops=)

  • Output: vmab."bit", vmab.primary_customer_number, vmab.travel_id, vrab.description, vrab.unit_price, vrab.travel_id
  • Sort Key: vmab.primary_customer_number, vmab.travel_id
87. 0.000 0.000 ↓ 0.0

Merge Join (cost=164,314.07..174,960.92 rows=39,439 width=84) (actual rows= loops=)

  • Output: vmab."bit", vmab.primary_customer_number, vmab.travel_id, vrab.description, vrab.unit_price, vrab.travel_id
  • Merge Cond: ((vrab.travel_id = vmab.travel_id) AND (vrab."bit" = vmab."bit"))
88. 0.000 0.000 ↓ 0.0

Sort (cost=85.30..88.19 rows=1,155 width=76) (actual rows= loops=)

  • Output: vrab.description, vrab.unit_price, vrab.travel_id, vrab."bit
  • Sort Key: vrab.travel_id, vrab."bit
89. 0.000 0.000 ↓ 0.0

Seq Scan on b_data.vrab (cost=0.00..26.55 rows=1,155 width=76) (actual rows= loops=)

  • Output: vrab.description, vrab.unit_price, vrab.travel_id, vrab."bit
90. 0.000 0.000 ↓ 0.0

Sort (cost=164,228.76..167,643.36 rows=1,365,840 width=40) (actual rows= loops=)

  • Output: vmab."bit", vmab.primary_customer_number, vmab.travel_id
  • Sort Key: vmab.travel_id, vmab."bit
91. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.vmtf_activ_bits vmab (cost=0.00..25,040.40 rows=1,365,840 width=40) (actual rows= loops=)

  • Output: vmab."bit", vmab.primary_customer_number, vmab.travel_id
92. 0.000 0.000 ↓ 0.0

Materialize (cost=3,685,193,280.46..3,776,344,676.06 rows=18,230,279,120 width=18) (actual rows= loops=)

  • Output: pax_7.pax_id, pax_7.travel_id, pax_7.booking_id, pax_7.is_valid, pax_7.is_cancelled, pax_7.person_id
93. 0.000 0.000 ↓ 0.0

Sort (cost=3,685,193,280.46..3,730,768,978.26 rows=18,230,279,120 width=18) (actual rows= loops=)

  • Output: pax_7.pax_id, pax_7.travel_id, pax_7.booking_id, pax_7.is_valid, pax_7.is_cancelled, pax_7.person_id
  • Sort Key: pax_7.person_id, pax_7.travel_id
94. 0.000 0.000 ↓ 0.0

CTE Scan on tmp_pax pax_7 (cost=0.00..364,605,582.40 rows=18,230,279,120 width=18) (actual rows= loops=)

  • Output: pax_7.pax_id, pax_7.travel_id, pax_7.booking_id, pax_7.is_valid, pax_7.is_cancelled, pax_7.person_id
95. 0.000 0.000 ↓ 0.0

Hash Join (cost=8,023.46..684,054,485.93 rows=32,879,637 width=116) (actual rows= loops=)

  • Output: pax_8.pax_id, pax_8.travel_id, pax_8.booking_id, pax_8.is_valid, pax_8.is_cancelled, 'Fakultative Reisezusatzleistungen'::text, rzb2_1.travel_extra_description, rzb2_1.travel_extra_revenue, NULL::text, rzb2_1.provision, CASE WHEN (rzb2_1.bit_cash = 'Y'::text) THEN true ELSE false END
  • Hash Cond: ((pax_8.person_id = rzb2_1.person_id) AND (pax_8.travel_id = travel_id_mapping.travel_id))
96. 0.000 0.000 ↓ 0.0

CTE Scan on tmp_pax pax_8 (cost=0.00..364,605,582.40 rows=18,230,279,120 width=18) (actual rows= loops=)

  • Output: pax_8.pax_id, pax_8.booking_id, pax_8.person_id, pax_8.is_valid, pax_8.is_cancelled, pax_8.travel_id, pax_8.booking_confirmation_amount, pax_8.total_invoiced_revenue
97. 0.000 0.000 ↓ 0.0

Hash (cost=7,423.46..7,423.46 rows=40,000 width=51) (actual rows= loops=)

  • Output: rzb2_1.travel_extra_description, rzb2_1.travel_extra_revenue, rzb2_1.provision, rzb2_1.bit_cash, rzb2_1.person_id, travel_id_mapping.travel_id
  • Buckets: 262144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 17586kB
98. 0.000 0.000 ↓ 0.0

Hash Join (cost=6,518.46..7,423.46 rows=40,000 width=51) (actual rows= loops=)

  • Output: rzb2_1.travel_extra_description, rzb2_1.travel_extra_revenue, rzb2_1.provision, rzb2_1.bit_cash, rzb2_1.person_id, travel_id_mapping.travel_id
  • Inner Unique: true
  • Hash Cond: (travel_id_mapping.record_id = rzb2_1.record_id)
99. 0.000 0.000 ↓ 0.0

CTE Scan on travel_id_mapping (cost=0.00..800.00 rows=40,000 width=12) (actual rows= loops=)

  • Output: travel_id_mapping.record_id, travel_id_mapping.travel_id
100. 0.000 0.000 ↓ 0.0

Hash (cost=4,377.65..4,377.65 rows=171,265 width=55) (actual rows= loops=)

  • Output: rzb2_1.travel_extra_description, rzb2_1.travel_extra_revenue, rzb2_1.provision, rzb2_1.bit_cash, rzb2_1.record_id, rzb2_1.person_id
  • Buckets: 262144 Batches: 1 Memory Usage: 18264kB
101. 0.000 0.000 ↓ 0.0

Seq Scan on b_data.rzb2 rzb2_1 (cost=0.00..4,377.65 rows=171,265 width=55) (actual rows= loops=)

  • Output: rzb2_1.travel_extra_description, rzb2_1.travel_extra_revenue, rzb2_1.provision, rzb2_1.bit_cash, rzb2_1.record_id, rzb2_1.person_id
102.          

CTE booking_item_line_amount

103. 0.000 0.000 ↓ 0.0

HashAggregate (cost=46,628,647,388,295.45..46,628,647,388,297.45 rows=200 width=20) (actual rows= loops=)

  • Output: booking_item_2.booking_id, sum(CASE WHEN (booking_item_2.is_cancelled IS FALSE) THEN booking_item_2.unit_price ELSE '0'::double precision END), sum(booking_item_2.unit_price)
  • Group Key: booking_item_2.booking_id
104. 0.000 0.000 ↓ 0.0

CTE Scan on booking_item booking_item_2 (cost=0.00..35,015,254,609,483.44 rows=1,548,452,370,508,268 width=13) (actual rows= loops=)

  • Output: booking_item_2.pax_id, booking_item_2.travel_id, booking_item_2.booking_id, booking_item_2.is_valid, booking_item_2.is_cancelled, booking_item_2.service_group, booking_item_2.description, booking_item_2.unit_price, booking_item_2."bit", booking_item_2.provision, booking_item_2.is_cash
  • Filter: (booking_item_2.service_group <> 'Fakultative Reisezusatzleistungen'::text)
105.          

CTE cancelled_booking

106. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=469,409,484,439,854.25..481,081,235,976,350.81 rows=200 width=12) (actual rows= loops=)

  • Output: booking_item_3.booking_id, count(DISTINCT CASE WHEN (booking_item_3.is_cancelled IS FALSE) THEN booking_item_3.pax_id ELSE NULL::integer END)
  • Group Key: booking_item_3.booking_id
107. 0.000 0.000 ↓ 0.0

Sort (cost=469,409,484,439,854.25..473,300,068,285,352.44 rows=1,556,233,538,199,264 width=9) (actual rows= loops=)

  • Output: booking_item_3.booking_id, booking_item_3.is_cancelled, booking_item_3.pax_id
  • Sort Key: booking_item_3.booking_id
108. 0.000 0.000 ↓ 0.0

CTE Scan on booking_item booking_item_3 (cost=0.00..31,124,670,763,985.28 rows=1,556,233,538,199,264 width=9) (actual rows= loops=)

  • Output: booking_item_3.booking_id, booking_item_3.is_cancelled, booking_item_3.pax_id
109. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=597,069,266,870,285.50..655,428,024,552,757.88 rows=1,556,233,538,199,264 width=150) (actual rows= loops=)

  • Output: booking_item_1.pax_id, booking_item_1.travel_id, booking_item_1.service_group, booking_item_1.description, booking_item_1.unit_price, booking_item_1.is_cancelled, booking_item_1."bit", booking_item_1.provision, booking_item_1.is_cash, booking_item_1.booking_id, booking_item_line_amount.total_line_amount_not_cancelled_pax, booking_item_line_amount.total_line_amount, cancelled_booking.number_of_not_cancelled_pax
  • Merge Cond: (booking_item_1.booking_id = cancelled_booking.booking_id)
110. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=597,069,266,870,273.88..628,193,937,634,259.12 rows=1,556,233,538,199,264 width=142) (actual rows= loops=)

  • Output: booking_item_1.pax_id, booking_item_1.travel_id, booking_item_1.service_group, booking_item_1.description, booking_item_1.unit_price, booking_item_1.is_cancelled, booking_item_1."bit", booking_item_1.provision, booking_item_1.is_cash, booking_item_1.booking_id, booking_item_line_amount.total_line_amount_not_cancelled_pax, booking_item_line_amount.total_line_amount
  • Merge Cond: (booking_item_1.booking_id = booking_item_line_amount.booking_id)
111. 0.000 0.000 ↓ 0.0

Sort (cost=597,069,266,870,262.25..600,959,850,715,760.38 rows=1,556,233,538,199,264 width=126) (actual rows= loops=)

  • Output: booking_item_1.pax_id, booking_item_1.travel_id, booking_item_1.service_group, booking_item_1.description, booking_item_1.unit_price, booking_item_1.is_cancelled, booking_item_1."bit", booking_item_1.provision, booking_item_1.is_cash, booking_item_1.booking_id
  • Sort Key: booking_item_1.booking_id
112. 0.000 0.000 ↓ 0.0

CTE Scan on booking_item booking_item_1 (cost=0.00..31,124,670,763,985.28 rows=1,556,233,538,199,264 width=126) (actual rows= loops=)

  • Output: booking_item_1.pax_id, booking_item_1.travel_id, booking_item_1.service_group, booking_item_1.description, booking_item_1.unit_price, booking_item_1.is_cancelled, booking_item_1."bit", booking_item_1.provision, booking_item_1.is_cash, booking_item_1.booking_id
113. 0.000 0.000 ↓ 0.0

Sort (cost=11.64..12.14 rows=200 width=20) (actual rows= loops=)

  • Output: booking_item_line_amount.total_line_amount_not_cancelled_pax, booking_item_line_amount.total_line_amount, booking_item_line_amount.booking_id
  • Sort Key: booking_item_line_amount.booking_id
114. 0.000 0.000 ↓ 0.0

CTE Scan on booking_item_line_amount (cost=0.00..4.00 rows=200 width=20) (actual rows= loops=)

  • Output: booking_item_line_amount.total_line_amount_not_cancelled_pax, booking_item_line_amount.total_line_amount, booking_item_line_amount.booking_id
115. 0.000 0.000 ↓ 0.0

Sort (cost=11.64..12.14 rows=200 width=12) (actual rows= loops=)

  • Output: cancelled_booking.number_of_not_cancelled_pax, cancelled_booking.booking_id
  • Sort Key: cancelled_booking.booking_id
116. 0.000 0.000 ↓ 0.0

CTE Scan on cancelled_booking (cost=0.00..4.00 rows=200 width=12) (actual rows= loops=)

  • Output: cancelled_booking.number_of_not_cancelled_pax, cancelled_booking.booking_id
117. 0.000 0.000 ↓ 0.0

Sort (cost=168,679.23..171,314.23 rows=1,054,000 width=24) (actual rows= loops=)

  • Output: booking.booking_confirmation_amount, booking.total_invoiced_revenue, booking.booking_id
  • Sort Key: booking.booking_id
118. 0.000 0.000 ↓ 0.0

Seq Scan on b_tmp.booking (cost=0.00..63,240.00 rows=1,054,000 width=24) (actual rows= loops=)

  • Output: booking.booking_confirmation_amount, booking.total_invoiced_revenue, booking.booking_id