explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LB6g

Settings
# exclusive inclusive rows x rows loops node
1. 0.245 4,505,129.574 ↑ 24,431,878.9 155 1

Unique (cost=576,183,336,839,517.12..576,184,283,574,823.38 rows=3,786,941,225 width=1,933) (actual time=4,505,129.315..4,505,129.574 rows=155 loops=1)

2.          

CTE preselected_customers

3. 0.749 7.709 ↓ 3.6 155 1

Nested Loop Left Join (cost=4.01..688.11 rows=43 width=257) (actual time=0.560..7.709 rows=155 loops=1)

4.          

Initplan (forNested Loop Left Join)

5. 0.005 0.005 ↑ 1.0 1 1

Index Scan using config_name_agency_id_cobrand_name_uniq on configuration conf_1 (cost=0.28..3.30 rows=1 width=11) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (config_name = 'system.default_country_code'::text)
  • Filter: (cobrand_name IS NULL)
  • Rows Removed by Filter: 1
6. 3.235 3.235 ↓ 3.6 155 1

Index Scan using customer_consolidated_entered_date_idx on customer c_1 (cost=0.42..81.51 rows=43 width=257) (actual time=0.071..3.235 rows=155 loops=1)

  • Index Cond: ((entered_date >= '2019-09-04 17:00:00'::timestamp without time zone) AND (entered_date <= '2019-09-05 17:00:00'::timestamp without time zone))
  • Filter: ((original_id IS NULL) AND (cancelled_date IS NULL) AND (NOT cancelled) AND (status = 'booked'::text) AND (nav = ANY ('{auaboENG,auarwENG,aubomENG,aubsaENG,aucusENG,austgENG,default,demoauENG,igodemohkCHI,igodemohkENG,igodemohkNTS,"i\\
  • Rows Removed by Filter: 48
7. 0.620 0.620 ↑ 1.0 1 155

Index Only Scan using redemption_bookings_custid_idx on redemption_bookings rb (cost=0.29..1.78 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=155)

  • Index Cond: (custid = c_1.custid)
  • Heap Fetches: 154
8.          

SubPlan (forNested Loop Left Join)

9. 0.000 0.310 ↑ 1.0 1 155

Aggregate (cost=3.32..3.33 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=155)

10. 0.310 0.310 ↓ 0.0 0 155

Index Scan using activity_segment_custid_idx on activity_segment activities (cost=0.28..3.30 rows=1 width=40) (actual time=0.002..0.002 rows=0 loops=155)

  • Index Cond: (custid = c_1.custid)
11. 0.155 0.775 ↑ 1.0 1 155

Aggregate (cost=3.34..3.35 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=155)

12. 0.155 0.620 ↓ 0.0 0 155

HashAggregate (cost=3.31..3.32 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=155)

  • Group Key: room.custid, room.room_segment_id
13. 0.465 0.465 ↓ 0.0 0 155

Index Scan using room_segment_custid_idx on room_segment room (cost=0.29..3.30 rows=1 width=20) (actual time=0.003..0.003 rows=0 loops=155)

  • Index Cond: (custid = c_1.custid)
14. 0.620 0.930 ↑ 1.0 1 155

Aggregate (cost=1.05..1.06 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=155)

15. 0.310 0.310 ↓ 0.0 0 155

Seq Scan on cruise_traveler cruise_travelers (cost=0.00..1.05 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=155)

  • Filter: (custid = c_1.custid)
  • Rows Removed by Filter: 4
16. 0.620 0.620 ↓ 0.0 0 155

Index Scan using config_name_agency_id_cobrand_name_uniq on configuration conf (cost=0.28..3.30 rows=1 width=11) (actual time=0.004..0.004 rows=0 loops=155)

  • Index Cond: (config_name = 'system.default_country_code'::text)
  • Filter: (cobrand_name = c_1.nav)
  • Rows Removed by Filter: 2
17. 0.465 0.465 ↓ 0.0 0 155

Index Only Scan using customer_consolidated_original_id_idx on customer (cost=0.42..3.49 rows=4 width=0) (actual time=0.003..0.003 rows=0 loops=155)

  • Index Cond: (original_id = c_1.custid)
  • Heap Fetches: 10
18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using customer_consolidated_original_id_idx on customer customer_1 (cost=0.42..3,733.83 rows=116,013 width=4) (never executed)

  • Heap Fetches: 0
19.          

CTE cobrand_config

20. 0.006 0.229 ↑ 11.0 15 1

Nested Loop Left Join (cost=5.10..38.35 rows=165 width=32) (actual time=0.128..0.229 rows=15 loops=1)

21. 0.018 0.103 ↑ 1.0 15 1

Hash Right Join (cost=3.22..18.83 rows=15 width=21) (actual time=0.085..0.103 rows=15 loops=1)

  • Hash Cond: (cobrand_config.cobrand_name = cobrand.cobrand_name)
22. 0.036 0.073 ↑ 1.0 13 1

Bitmap Heap Scan on configuration cobrand_config (cost=1.88..17.43 rows=13 width=20) (actual time=0.065..0.073 rows=13 loops=1)

  • Recheck Cond: (config_name = 'accounting_code'::text)
  • Filter: (agency_id IS NULL)
  • Heap Blocks: exact=10
23. 0.037 0.037 ↑ 1.0 13 1

Bitmap Index Scan on config_name_agency_id_cobrand_name_uniq (cost=0.00..1.88 rows=13 width=0) (actual time=0.037..0.037 rows=13 loops=1)

  • Index Cond: (config_name = 'accounting_code'::text)
24. 0.007 0.012 ↑ 1.0 15 1

Hash (cost=1.15..1.15 rows=15 width=10) (actual time=0.012..0.012 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
25. 0.005 0.005 ↑ 1.0 15 1

Seq Scan on cobrand (cost=0.00..1.15 rows=15 width=10) (actual time=0.004..0.005 rows=15 loops=1)

26. 0.003 0.120 ↑ 11.0 1 15

Materialize (cost=1.88..17.48 rows=11 width=11) (actual time=0.003..0.008 rows=1 loops=15)

27. 0.103 0.117 ↑ 11.0 1 1

Bitmap Heap Scan on configuration system_config (cost=1.88..17.43 rows=11 width=11) (actual time=0.040..0.117 rows=1 loops=1)

  • Recheck Cond: (config_name = 'accounting_code'::text)
  • Filter: ((cobrand_name IS NULL) AND (agency_id IS NULL))
  • Rows Removed by Filter: 12
  • Heap Blocks: exact=10
28. 0.014 0.014 ↑ 1.0 13 1

Bitmap Index Scan on config_name_agency_id_cobrand_name_uniq (cost=0.00..1.88 rows=13 width=0) (actual time=0.014..0.014 rows=13 loops=1)

  • Index Cond: (config_name = 'accounting_code'::text)
29.          

CTE car_summary

30. 37.936 47.733 ↓ 1.0 2,379 1

GroupAggregate (cost=562.64..804.57 rows=2,337 width=105) (actual time=8.782..47.733 rows=2,379 loops=1)

  • Group Key: car.custid
31. 0.861 9.797 ↓ 1.0 2,395 1

Merge Left Join (cost=562.64..575.02 rows=2,362 width=105) (actual time=8.684..9.797 rows=2,395 loops=1)

  • Merge Cond: (car.custid = c_2.bookingnumber)
32. 1.782 8.872 ↓ 1.0 2,395 1

Sort (cost=560.61..566.52 rows=2,362 width=105) (actual time=8.626..8.872 rows=2,395 loops=1)

  • Sort Key: car.custid
  • Sort Method: quicksort Memory: 572kB
33. 7.090 7.090 ↓ 1.0 2,395 1

Seq Scan on car_segment car (cost=0.00..428.27 rows=2,362 width=105) (actual time=0.023..7.090 rows=2,395 loops=1)

  • Filter: (original_id IS NULL)
  • Rows Removed by Filter: 469
34. 0.032 0.064 ↓ 3.6 155 1

Sort (cost=2.03..2.13 rows=43 width=4) (actual time=0.054..0.064 rows=155 loops=1)

  • Sort Key: c_2.bookingnumber
  • Sort Method: quicksort Memory: 32kB
35. 0.032 0.032 ↓ 3.6 155 1

CTE Scan on preselected_customers c_2 (cost=0.00..0.86 rows=43 width=4) (actual time=0.002..0.032 rows=155 loops=1)

36.          

CTE activities_summary

37. 18.298 23.944 ↓ 1.0 1,383 1

GroupAggregate (cost=396.54..535.12 rows=1,351 width=96) (actual time=5.075..23.944 rows=1,383 loops=1)

  • Group Key: activities_1.custid
38. 0.486 5.646 ↓ 1.0 1,553 1

Merge Left Join (cost=396.54..404.85 rows=1,544 width=96) (actual time=4.998..5.646 rows=1,553 loops=1)

  • Merge Cond: (activities_1.custid = c_3.bookingnumber)
39. 1.075 5.086 ↓ 1.0 1,553 1

Sort (cost=394.51..398.37 rows=1,544 width=96) (actual time=4.926..5.086 rows=1,553 loops=1)

  • Sort Key: activities_1.custid
  • Sort Method: quicksort Memory: 335kB
40. 4.011 4.011 ↓ 1.0 1,553 1

Seq Scan on activity_segment activities_1 (cost=0.00..312.74 rows=1,544 width=96) (actual time=0.022..4.011 rows=1,553 loops=1)

  • Filter: (original_id IS NULL)
  • Rows Removed by Filter: 528
41. 0.031 0.074 ↓ 2.0 88 1

Sort (cost=2.03..2.13 rows=43 width=4) (actual time=0.069..0.074 rows=88 loops=1)

  • Sort Key: c_3.bookingnumber
  • Sort Method: quicksort Memory: 32kB
42. 0.043 0.043 ↓ 3.6 155 1

CTE Scan on preselected_customers c_3 (cost=0.00..0.86 rows=43 width=4) (actual time=0.003..0.043 rows=155 loops=1)

43.          

CTE room_summary

44. 132.385 189.337 ↓ 1.0 13,119 1

GroupAggregate (cost=4,317.99..5,730.05 rows=12,968 width=131) (actual time=49.572..189.337 rows=13,119 loops=1)

  • Group Key: room_1.custid
45. 5.438 56.952 ↓ 1.0 13,727 1

Merge Left Join (cost=4,317.99..4,386.77 rows=13,632 width=131) (actual time=49.482..56.952 rows=13,727 loops=1)

  • Merge Cond: (room_1.custid = c_4.bookingnumber)
46. 12.122 51.424 ↓ 1.0 13,727 1

Sort (cost=4,315.97..4,350.05 rows=13,632 width=131) (actual time=49.401..51.424 rows=13,727 loops=1)

  • Sort Key: room_1.custid
  • Sort Method: quicksort Memory: 3849kB
47. 39.302 39.302 ↓ 1.0 13,727 1

Seq Scan on room_segment room_1 (cost=0.00..3,379.81 rows=13,632 width=131) (actual time=0.021..39.302 rows=13,727 loops=1)

  • Filter: (original_id IS NULL)
  • Rows Removed by Filter: 1843
48. 0.044 0.090 ↓ 3.6 155 1

Sort (cost=2.03..2.13 rows=43 width=4) (actual time=0.077..0.090 rows=155 loops=1)

  • Sort Key: c_4.bookingnumber
  • Sort Method: quicksort Memory: 32kB
49. 0.046 0.046 ↓ 3.6 155 1

CTE Scan on preselected_customers c_4 (cost=0.00..0.86 rows=43 width=4) (actual time=0.003..0.046 rows=155 loops=1)

50.          

CTE booking_fees

51. 159.341 207.633 ↓ 1.3 42,054 1

HashAggregate (cost=6,460.66..7,264.01 rows=32,134 width=36) (actual time=162.685..207.633 rows=42,054 loops=1)

  • Group Key: fee.custid
52. 8.788 48.292 ↓ 1.1 47,016 1

Hash Left Join (cost=1.40..5,241.61 rows=44,329 width=36) (actual time=0.091..48.292 rows=47,016 loops=1)

  • Hash Cond: (fee.custid = c_5.bookingnumber)
53. 39.435 39.435 ↓ 1.1 47,016 1

Seq Scan on fee_segment fee (cost=0.00..5,073.59 rows=44,329 width=36) (actual time=0.013..39.435 rows=47,016 loops=1)

  • Filter: ((original_id IS NULL) AND (client_fee_type = ANY ('{booking,crs_booking}'::text[])))
  • Rows Removed by Filter: 20982
54. 0.030 0.069 ↓ 3.6 155 1

Hash (cost=0.86..0.86 rows=43 width=4) (actual time=0.069..0.069 rows=155 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
55. 0.039 0.039 ↓ 3.6 155 1

CTE Scan on preselected_customers c_5 (cost=0.00..0.86 rows=43 width=4) (actual time=0.002..0.039 rows=155 loops=1)

56.          

CTE modification_fees

57. 1.835 71.234 ↑ 1.3 705 1

HashAggregate (cost=5,095.55..5,109.31 rows=917 width=28) (actual time=70.926..71.234 rows=705 loops=1)

  • Group Key: fee_1.custid
58. 0.261 69.399 ↑ 1.6 785 1

Hash Left Join (cost=1.40..5,079.74 rows=1,265 width=28) (actual time=0.267..69.399 rows=785 loops=1)

  • Hash Cond: (fee_1.custid = c_6.bookingnumber)
59. 69.072 69.072 ↑ 1.6 785 1

Seq Scan on fee_segment fee_1 (cost=0.00..5,073.59 rows=1,265 width=28) (actual time=0.181..69.072 rows=785 loops=1)

  • Filter: ((original_id IS NULL) AND (client_fee_type = ANY ('{modification,crs_modification}'::text[])))
  • Rows Removed by Filter: 67213
60. 0.031 0.066 ↓ 3.6 155 1

Hash (cost=0.86..0.86 rows=43 width=4) (actual time=0.066..0.066 rows=155 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
61. 0.035 0.035 ↓ 3.6 155 1

CTE Scan on preselected_customers c_6 (cost=0.00..0.86 rows=43 width=4) (actual time=0.002..0.035 rows=155 loops=1)

62.          

CTE cancellation_fees

63. 7.876 42.861 ↑ 1.4 3,258 1

HashAggregate (cost=5,179.86..5,250.00 rows=4,676 width=28) (actual time=41.379..42.861 rows=3,258 loops=1)

  • Group Key: fee_2.custid
64. 0.954 34.985 ↑ 1.4 4,703 1

Hash Left Join (cost=1.40..5,099.23 rows=6,450 width=28) (actual time=0.077..34.985 rows=4,703 loops=1)

  • Hash Cond: (fee_2.custid = c_7.bookingnumber)
65. 33.975 33.975 ↑ 1.4 4,703 1

Seq Scan on fee_segment fee_2 (cost=0.00..5,073.59 rows=6,450 width=28) (actual time=0.013..33.975 rows=4,703 loops=1)

  • Filter: ((original_id IS NULL) AND (client_fee_type = ANY ('{cancellation,crs_cancellation}'::text[])))
  • Rows Removed by Filter: 63295
66. 0.016 0.056 ↓ 3.6 155 1

Hash (cost=0.86..0.86 rows=43 width=4) (actual time=0.056..0.056 rows=155 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
67. 0.040 0.040 ↓ 3.6 155 1

CTE Scan on preselected_customers c_7 (cost=0.00..0.86 rows=43 width=4) (actual time=0.001..0.040 rows=155 loops=1)

68.          

CTE traveler_type_count

69. 0.093 142.144 ↓ 3.8 163 1

HashAggregate (cost=933.39..933.82 rows=43 width=164) (actual time=142.120..142.144 rows=163 loops=1)

  • Group Key: customer_6.bookingnumber, COALESCE(att.traveler_ptc, rtt.traveler_ptc, actt.traveler_ptc, ctt.traveler_ptc)
70.          

CTE rtt

71. 0.033 0.562 ↑ 6.4 14 1

GroupAggregate (cost=263.75..265.53 rows=89 width=12) (actual time=0.540..0.562 rows=14 loops=1)

  • Group Key: roomlist.custid, booking_traveler.traveler_ptc
72. 0.020 0.529 ↑ 3.4 26 1

Sort (cost=263.75..263.97 rows=89 width=12) (actual time=0.529..0.529 rows=26 loops=1)

  • Sort Key: roomlist.custid, booking_traveler.traveler_ptc
  • Sort Method: quicksort Memory: 26kB
73. 0.003 0.509 ↑ 3.4 26 1

Nested Loop (cost=0.71..260.86 rows=89 width=12) (actual time=0.209..0.509 rows=26 loops=1)

74. 0.038 0.376 ↑ 3.6 26 1

Nested Loop (cost=0.29..186.54 rows=94 width=8) (actual time=0.171..0.376 rows=26 loops=1)

75. 0.028 0.028 ↓ 3.6 155 1

CTE Scan on preselected_customers customer_2 (cost=0.00..0.86 rows=43 width=4) (actual time=0.001..0.028 rows=155 loops=1)

76. 0.310 0.310 ↓ 0.0 0 155

Index Scan using room_segment_traveler_custid_idx on room_segment_traveler roomlist (cost=0.29..4.30 rows=2 width=8) (actual time=0.002..0.002 rows=0 loops=155)

  • Index Cond: (custid = customer_2.bookingnumber)
77. 0.130 0.130 ↑ 1.0 1 26

Index Scan using booking_traveler_roomlist_id_idx on booking_traveler (cost=0.42..0.78 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=26)

  • Index Cond: (roomlist_id = roomlist.room_segment_traveler_id)
78.          

CTE att

79. 0.192 2.522 ↓ 2.6 145 1

GroupAggregate (cost=211.36..212.46 rows=55 width=12) (actual time=2.331..2.522 rows=145 loops=1)

  • Group Key: air_pnrs.custid, booking_traveler_1.traveler_ptc
80. 0.090 2.330 ↓ 3.4 186 1

Sort (cost=211.36..211.50 rows=55 width=12) (actual time=2.319..2.330 rows=186 loops=1)

  • Sort Key: air_pnrs.custid, booking_traveler_1.traveler_ptc
  • Sort Method: quicksort Memory: 33kB
81. 0.129 2.240 ↓ 3.4 186 1

Nested Loop (cost=1.26..209.77 rows=55 width=12) (actual time=0.141..2.240 rows=186 loops=1)

82. 0.083 1.181 ↓ 2.7 186 1

Nested Loop (cost=0.84..173.49 rows=69 width=8) (actual time=0.102..1.181 rows=186 loops=1)

83. 0.029 0.687 ↓ 3.1 137 1

Nested Loop (cost=0.42..147.60 rows=44 width=8) (actual time=0.073..0.687 rows=137 loops=1)

84. 0.038 0.038 ↓ 3.6 155 1

CTE Scan on preselected_customers customer_3 (cost=0.00..0.86 rows=43 width=4) (actual time=0.000..0.038 rows=155 loops=1)

85. 0.620 0.620 ↑ 1.0 1 155

Index Scan using air_pnr_consolidated_custid_idx on air_pnr air_pnrs (cost=0.42..3.40 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=155)

  • Index Cond: (custid = customer_3.bookingnumber)
86. 0.411 0.411 ↑ 2.0 1 137

Index Scan using air_pnr_traveler_air_pnrid_idx on air_pnr_traveler air_travelers (cost=0.42..0.57 rows=2 width=8) (actual time=0.003..0.003 rows=1 loops=137)

  • Index Cond: (air_pnr_id = air_pnrs.air_pnr_id)
87. 0.930 0.930 ↑ 1.0 1 186

Index Scan using booking_traveler_air_traveler_id_idx on booking_traveler booking_traveler_1 (cost=0.42..0.52 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=186)

  • Index Cond: (air_traveler_id = air_travelers.air_pnr_traveler_id)
88.          

CTE ctt

89. 0.000 0.256 ↓ 0.0 0 1

GroupAggregate (cost=198.46..198.48 rows=1 width=12) (actual time=0.256..0.256 rows=0 loops=1)

  • Group Key: car_1.custid, booking_traveler_2.traveler_ptc
90. 0.003 0.256 ↓ 0.0 0 1

Sort (cost=198.46..198.46 rows=1 width=12) (actual time=0.256..0.256 rows=0 loops=1)

  • Sort Key: car_1.custid, booking_traveler_2.traveler_ptc
  • Sort Method: quicksort Memory: 25kB
91. 0.005 0.253 ↓ 0.0 0 1

Nested Loop (cost=0.70..198.45 rows=1 width=12) (actual time=0.253..0.253 rows=0 loops=1)

92. 0.044 0.222 ↑ 21.5 2 1

Nested Loop (cost=0.28..101.19 rows=43 width=8) (actual time=0.131..0.222 rows=2 loops=1)

93. 0.023 0.023 ↓ 3.6 155 1

CTE Scan on preselected_customers customer_4 (cost=0.00..0.86 rows=43 width=4) (actual time=0.001..0.023 rows=155 loops=1)

94. 0.155 0.155 ↓ 0.0 0 155

Index Scan using car_segment_custid_idx on car_segment car_1 (cost=0.28..2.32 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=155)

  • Index Cond: (custid = customer_4.bookingnumber)
95. 0.026 0.026 ↓ 0.0 0 2

Index Scan using booking_traveler_car_id_idx on booking_traveler booking_traveler_2 (cost=0.42..2.25 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=2)

  • Index Cond: (car_id = car_1.car_segment_id)
96.          

CTE actt

97. 0.016 138.323 ↑ 82.0 1 1

GroupAggregate (cost=245.62..247.26 rows=82 width=12) (actual time=138.323..138.323 rows=1 loops=1)

  • Group Key: activity_travelerlist.custid, booking_traveler_3.traveler_ptc
98. 0.006 138.307 ↑ 82.0 1 1

Sort (cost=245.62..245.83 rows=82 width=12) (actual time=138.307..138.307 rows=1 loops=1)

  • Sort Key: activity_travelerlist.custid, booking_traveler_3.traveler_ptc
  • Sort Method: quicksort Memory: 25kB
99. 0.618 138.301 ↑ 82.0 1 1

Merge Join (cost=112.29..243.02 rows=82 width=12) (actual time=138.232..138.301 rows=1 loops=1)

  • Merge Cond: (booking_traveler_3.activity_travelerlistid = activity_travelerlist.activity_segment_traveler_id)
100. 137.461 137.461 ↑ 104.9 3,236 1

Index Scan using booking_traveler_activity_travelerlistid_idx1 on booking_traveler booking_traveler_3 (cost=0.42..13,738.73 rows=339,362 width=8) (actual time=0.055..137.461 rows=3,236 loops=1)

101. 0.005 0.222 ↑ 98.0 1 1

Sort (cost=111.86..112.11 rows=98 width=8) (actual time=0.221..0.222 rows=1 loops=1)

  • Sort Key: activity_travelerlist.activity_segment_traveler_id
  • Sort Method: quicksort Memory: 25kB
102. 0.042 0.217 ↑ 98.0 1 1

Nested Loop (cost=0.28..108.62 rows=98 width=8) (actual time=0.139..0.217 rows=1 loops=1)

103. 0.020 0.020 ↓ 3.6 155 1

CTE Scan on preselected_customers customer_5 (cost=0.00..0.86 rows=43 width=4) (actual time=0.001..0.020 rows=155 loops=1)

104. 0.155 0.155 ↓ 0.0 0 155

Index Scan using activity_segment_traveler_custid_idx on activity_segment_traveler activity_travelerlist (cost=0.28..2.49 rows=2 width=8) (actual time=0.001..0.001 rows=0 loops=155)

  • Index Cond: (custid = customer_5.bookingnumber)
105. 0.042 142.051 ↓ 3.8 163 1

Hash Left Join (cost=6.62..9.33 rows=43 width=164) (actual time=141.959..142.051 rows=163 loops=1)

  • Hash Cond: (customer_6.bookingnumber = ctt.bookingnumber)
106. 0.047 141.751 ↓ 3.8 163 1

Hash Right Join (cost=6.59..9.13 rows=43 width=124) (actual time=141.690..141.751 rows=163 loops=1)

  • Hash Cond: (rtt.bookingnumber = customer_6.bookingnumber)
107. 0.571 0.571 ↑ 6.4 14 1

CTE Scan on rtt (cost=0.00..1.78 rows=89 width=44) (actual time=0.542..0.571 rows=14 loops=1)

108. 0.043 141.133 ↓ 3.8 163 1

Hash (cost=6.05..6.05 rows=43 width=84) (actual time=141.133..141.133 rows=163 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
109. 0.042 141.090 ↓ 3.8 163 1

Hash Right Join (cost=3.67..6.05 rows=43 width=84) (actual time=141.058..141.090 rows=163 loops=1)

  • Hash Cond: (actt.bookingnumber = customer_6.bookingnumber)
110. 138.327 138.327 ↑ 82.0 1 1

CTE Scan on actt (cost=0.00..1.64 rows=82 width=44) (actual time=138.326..138.327 rows=1 loops=1)

111. 0.027 2.721 ↓ 3.8 163 1

Hash (cost=3.13..3.13 rows=43 width=44) (actual time=2.721..2.721 rows=163 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
112. 0.066 2.694 ↓ 3.8 163 1

Hash Right Join (cost=1.40..3.13 rows=43 width=44) (actual time=2.400..2.694 rows=163 loops=1)

  • Hash Cond: (att.bookingnumber = customer_6.bookingnumber)
113. 2.569 2.569 ↓ 2.6 145 1

CTE Scan on att (cost=0.00..1.10 rows=55 width=44) (actual time=2.333..2.569 rows=145 loops=1)

114. 0.032 0.059 ↓ 3.6 155 1

Hash (cost=0.86..0.86 rows=43 width=4) (actual time=0.059..0.059 rows=155 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
115. 0.027 0.027 ↓ 3.6 155 1

CTE Scan on preselected_customers customer_6 (cost=0.00..0.86 rows=43 width=4) (actual time=0.002..0.027 rows=155 loops=1)

116. 0.000 0.258 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=44) (actual time=0.258..0.258 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
117. 0.258 0.258 ↓ 0.0 0 1

CTE Scan on ctt (cost=0.00..0.02 rows=1 width=44) (actual time=0.258..0.258 rows=0 loops=1)

118.          

CTE air_summary

119. 0.729 13.886 ↓ 3.2 137 1

GroupAggregate (cost=289.05..291.09 rows=43 width=25) (actual time=13.184..13.886 rows=137 loops=1)

  • Group Key: customer_7.bookingnumber
120. 0.103 13.157 ↓ 2.7 213 1

Sort (cost=289.05..289.25 rows=80 width=25) (actual time=13.144..13.157 rows=213 loops=1)

  • Sort Key: customer_7.bookingnumber
  • Sort Method: quicksort Memory: 41kB
121. 0.189 13.054 ↓ 2.7 213 1

Nested Loop (cost=2.39..286.52 rows=80 width=25) (actual time=0.337..13.054 rows=213 loops=1)

122. 0.064 12.226 ↓ 2.7 213 1

Nested Loop (cost=1.97..250.17 rows=80 width=25) (actual time=0.274..12.226 rows=213 loops=1)

123. 0.083 11.002 ↓ 2.8 145 1

Nested Loop (cost=1.55..221.03 rows=51 width=25) (actual time=0.220..11.002 rows=145 loops=1)

  • Join Filter: (pricing.air_leg_id = air_segments.air_leg_id)
124. 0.252 9.614 ↓ 3.0 145 1

Nested Loop (cost=1.13..196.94 rows=49 width=29) (actual time=0.176..9.614 rows=145 loops=1)

125. 0.134 8.858 ↓ 3.1 252 1

Nested Loop (cost=0.84..170.02 rows=82 width=25) (actual time=0.105..8.858 rows=252 loops=1)

126. 0.208 8.313 ↓ 3.1 137 1

Nested Loop (cost=0.42..147.60 rows=44 width=25) (actual time=0.064..8.313 rows=137 loops=1)

127. 7.485 7.485 ↓ 3.6 155 1

CTE Scan on preselected_customers customer_7 (cost=0.00..0.86 rows=43 width=4) (actual time=0.004..7.485 rows=155 loops=1)

128. 0.620 0.620 ↑ 1.0 1 155

Index Scan using air_pnr_consolidated_custid_idx on air_pnr air_pnrs_1 (cost=0.42..3.40 rows=1 width=25) (actual time=0.004..0.004 rows=1 loops=155)

  • Index Cond: (custid = customer_7.bookingnumber)
129. 0.411 0.411 ↑ 1.0 2 137

Index Scan using air_leg_consolidated_air_pnrid_idx on air_leg air_legs (cost=0.42..0.49 rows=2 width=8) (actual time=0.003..0.003 rows=2 loops=137)

  • Index Cond: (air_pnr_id = air_pnrs_1.air_pnr_id)
130. 0.504 0.504 ↑ 1.0 1 252

Index Only Scan using air_pricing_consolidated_air_legid_idx on air_pricing pricing (cost=0.29..0.32 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=252)

  • Index Cond: (air_leg_id = air_legs.air_leg_id)
  • Heap Fetches: 145
131. 1.305 1.305 ↑ 1.0 1 145

Index Scan using air_segment_consolidated_air_legid_idx on air_segment air_segments (cost=0.42..0.48 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=145)

  • Index Cond: (air_leg_id = air_legs.air_leg_id)
  • Filter: (segment = 1)
  • Rows Removed by Filter: 0
132. 1.160 1.160 ↑ 2.0 1 145

Index Scan using air_pnr_traveler_segment_air_segmentid_idx on air_pnr_traveler_segment air_traveler_segments (cost=0.42..0.55 rows=2 width=8) (actual time=0.007..0.008 rows=1 loops=145)

  • Index Cond: (air_segment_id = air_segments.air_segment_id)
133. 0.639 0.639 ↑ 1.0 1 213

Index Only Scan using air_pnr_traveler_pkey on air_pnr_traveler air_travelers_1 (cost=0.42..0.44 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=213)

  • Index Cond: (air_pnr_traveler_id = air_traveler_segments.air_pnr_traveler_id)
  • Heap Fetches: 213
134.          

CTE air_pricing_summary

135. 1.314 3.038 ↓ 3.2 137 1

HashAggregate (cost=230.40..231.15 rows=43 width=52) (actual time=2.978..3.038 rows=137 loops=1)

  • Group Key: customer_8.bookingnumber
136. 0.158 1.724 ↓ 2.6 186 1

Nested Loop (cost=1.55..226.80 rows=72 width=52) (actual time=0.038..1.724 rows=186 loops=1)

  • Join Filter: (pricing_1.traveler_ptc = traveler.traveler_ptc)
  • Rows Removed by Join Filter: 27
137. 0.220 1.276 ↓ 3.0 145 1

Nested Loop (cost=1.13..200.19 rows=49 width=64) (actual time=0.017..1.276 rows=145 loops=1)

138. 0.101 0.804 ↓ 3.1 252 1

Nested Loop (cost=0.84..170.02 rows=82 width=16) (actual time=0.010..0.804 rows=252 loops=1)

139. 0.084 0.429 ↓ 3.1 137 1

Nested Loop (cost=0.42..147.60 rows=44 width=8) (actual time=0.007..0.429 rows=137 loops=1)

140. 0.035 0.035 ↓ 3.6 155 1

CTE Scan on preselected_customers customer_8 (cost=0.00..0.86 rows=43 width=4) (actual time=0.001..0.035 rows=155 loops=1)

141. 0.310 0.310 ↑ 1.0 1 155

Index Scan using air_pnr_consolidated_custid_idx on air_pnr air_pnrs_2 (cost=0.42..3.40 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=155)

  • Index Cond: (custid = customer_8.bookingnumber)
142. 0.274 0.274 ↑ 1.0 2 137

Index Scan using air_leg_consolidated_air_pnrid_idx on air_leg air_legs_1 (cost=0.42..0.49 rows=2 width=8) (actual time=0.002..0.002 rows=2 loops=137)

  • Index Cond: (air_pnr_id = air_pnrs_2.air_pnr_id)
143. 0.252 0.252 ↑ 1.0 1 252

Index Scan using air_pricing_consolidated_air_legid_idx on air_pricing pricing_1 (cost=0.29..0.36 rows=1 width=56) (actual time=0.001..0.001 rows=1 loops=252)

  • Index Cond: (air_leg_id = air_legs_1.air_leg_id)
144. 0.290 0.290 ↑ 2.0 1 145

Index Scan using air_pnr_traveler_air_pnrid_idx on air_pnr_traveler traveler (cost=0.42..0.52 rows=2 width=8) (actual time=0.002..0.002 rows=1 loops=145)

  • Index Cond: (air_pnr_id = air_legs_1.air_pnr_id)
145.          

CTE air_full_data

146. 377.626 2,653.803 ↓ 1.0 185,584 1

Unique (cost=39,885.37..83,048.99 rows=183,675 width=1,073) (actual time=2,254.999..2,653.803 rows=185,584 loops=1)

147. 297.260 2,276.177 ↓ 1.0 185,584 1

Sort (cost=39,885.37..40,344.55 rows=183,675 width=1,073) (actual time=2,254.997..2,276.177 rows=185,584 loops=1)

  • Sort Key: asg.air_segment_id, c_8.bookingnumber, air_leg.air_pnr_id, asg.air_leg_id, asg.segment, asg.airline, asg.flight_num, asg.departure_airport_code, asg.departure_date, asg.departure_time, asg.arrival_airport_code, asg.arrival_date, asg.arrival_time, asg.gmt_timestamp, asg.fare_class, asg.service_class, asg.service_class_display_name, asg.flight_id, asg.availability_id, asg.is_bulk, asg.codeshare_airline, asg.stops, asg.equipment_code, asg.fare_basis_code, asg.booking_descriptor, asg.supports_seat_map_requests, asg.is_charter, asg.supplier_locator, asg.passive_locator, asg.change_of_gauge_airport_code, asg.change_of_gauge_arrival_timestamp, asg.change_of_gauge_departure_timestamp, asg.change_of_gauge_plane, asg.crs_status_code, asg.air_segment_type, asg.equipment_description, asg.original_departure_timestamp, asg.original_arrival_timestamp, air_leg.loyalty_fare_class, air_leg.packaged_search, air_pnr.custid, air_pnr.supplier_locators, air_pnr.quick_add, air_pnr.pseudo_city_code, air_pnr.gds, air_pnr.booking_components, air_pnr.special_id, air_pnr.recloc, air_pnr.serialized_priced_itinerary, air_pnr.roundtrip, air_pnr.segment_locator, air_pnr.fare_basis_codes, air_pnr.ticketing_deadline, air_pnr.entered_date, air_pnr.air_select_key, air_pnr.session_key, air_pnr.agent_id, air_pnr.original_air_pnr_id, air_pnr.priced_itinerary_obj, air_pnr.booking_status, air_pnr.last_checked_status_date, air_pnr.air_standalone_price_package_savings_points, air_pnr.air_standalone_price_package_savings_supplier_currency, air_pnr.air_negotiated_code, air_pnr.is_non_refundable, c_8.private_key_hash, c_8.initialization_vector, c_8.transactiondate, c_8.departuredate, c_8.enddate, c_8.cobrand, c_8.leadtime, c_8.travelduration, c_8.leadtravelerfirstname, c_8.leadtravelerlastname, c_8.activitypax, c_8.roompax, c_8.cruisepax, c_8.offlineoronlinebooking, c_8.device, c_8.departurecity, c_8.arrivalcity, c_8.payment_total_points, c_8.rewardsid, c_8.cobrandcountrycode, c_8.systemcountrycode, c_8.totaldiscountincustomercurrency, c_8.totaldiscountinsystemcurrency, c_8.totalbookingincustomercurrency, c_8.totalbookinginsystemcurrency, c_8.ismodified, c_8.customercurrency
  • Sort Method: quicksort Memory: 89249kB
148. 213.216 1,978.917 ↓ 1.0 185,584 1

Hash Left Join (cost=11,215.77..23,825.93 rows=183,675 width=1,073) (actual time=1,453.989..1,978.917 rows=185,584 loops=1)

  • Hash Cond: (air_leg.air_pnr_id = air_pnr.air_pnr_id)
149. 259.846 1,606.985 ↓ 1.0 185,584 1

Hash Left Join (cost=4,933.55..13,870.20 rows=183,675 width=382) (actual time=1,295.154..1,606.985 rows=185,584 loops=1)

  • Hash Cond: (asg.air_leg_id = air_leg.air_leg_id)
150. 52.219 52.219 ↓ 1.0 185,584 1

Seq Scan on air_segment asg (cost=0.00..5,492.75 rows=183,675 width=345) (actual time=0.015..52.219 rows=185,584 loops=1)

151. 1,260.248 1,294.920 ↓ 1.0 176,639 1

Hash (cost=2,729.91..2,729.91 rows=176,291 width=41) (actual time=1,294.920..1,294.920 rows=176,639 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 7590kB
152. 34.672 34.672 ↓ 1.0 176,639 1

Seq Scan on air_leg (cost=0.00..2,729.91 rows=176,291 width=41) (actual time=0.026..34.672 rows=176,639 loops=1)

153. 71.278 158.716 ↓ 1.0 95,452 1

Hash (cost=5,101.09..5,101.09 rows=94,491 width=695) (actual time=158.716..158.716 rows=95,452 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 17940kB
154. 49.226 87.438 ↓ 1.0 95,452 1

Hash Left Join (cost=1.40..5,101.09 rows=94,491 width=695) (actual time=0.271..87.438 rows=95,452 loops=1)

  • Hash Cond: (air_pnr.custid = c_8.bookingnumber)
155. 37.973 37.973 ↓ 1.0 95,452 1

Seq Scan on air_pnr (cost=0.00..4,744.91 rows=94,491 width=242) (actual time=0.014..37.973 rows=95,452 loops=1)

156. 0.150 0.239 ↓ 3.6 155 1

Hash (cost=0.86..0.86 rows=43 width=453) (actual time=0.239..0.239 rows=155 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 55kB
157. 0.089 0.089 ↓ 3.6 155 1

CTE Scan on preselected_customers c_8 (cost=0.00..0.86 rows=43 width=453) (actual time=0.005..0.089 rows=155 loops=1)

158.          

CTE air_with_stay

159. 709.741 4,492,786.658 ↓ 1.0 185,584 1

WindowAgg (cost=19,732.94..927,777,770.31 rows=183,675 width=12) (actual time=1,514.261..4,492,786.658 rows=185,584 loops=1)

160. 1,445.369 1,500.869 ↓ 1.0 185,584 1

Sort (cost=19,732.94..20,192.12 rows=183,675 width=12) (actual time=1,488.340..1,500.869 rows=185,584 loops=1)

  • Sort Key: afd.id
  • Sort Method: quicksort Memory: 14844kB
161. 55.500 55.500 ↓ 1.0 185,584 1

CTE Scan on air_full_data afd (cost=0.00..3,673.50 rows=183,675 width=12) (actual time=0.005..55.500 rows=185,584 loops=1)

162.          

SubPlan (forWindowAgg)

163. 4,490,576.048 4,490,576.048 ↓ 0.0 0 185,584

CTE Scan on air_full_data afd2 (cost=0.00..5,051.06 rows=5 width=4) (actual time=24.197..24.197 rows=0 loops=185,584)

  • Filter: ((id = afd.id) AND (air_segment_id = (afd.air_segment_id + 1)))
  • Rows Removed by Filter: 185584
164.          

CTE air_legs_count

165. 57.741 258.776 ↑ 1.4 138 1

GroupAggregate (cost=19,732.94..21,112.50 rows=200 width=8) (actual time=189.251..258.776 rows=138 loops=1)

  • Group Key: afd_1.id
166. 162.263 201.035 ↓ 1.0 185,584 1

Sort (cost=19,732.94..20,192.12 rows=183,675 width=8) (actual time=189.235..201.035 rows=185,584 loops=1)

  • Sort Key: afd_1.id
  • Sort Method: quicksort Memory: 14844kB
167. 38.772 38.772 ↓ 1.0 185,584 1

CTE Scan on air_full_data afd_1 (cost=0.00..3,673.50 rows=183,675 width=8) (actual time=0.002..38.772 rows=185,584 loops=1)

168.          

CTE max_stay_segment_id

169. 0.023 2,353.640 ↑ 1.7 115 1

Unique (cost=80,940.37..80,941.87 rows=200 width=8) (actual time=2,353.611..2,353.640 rows=115 loops=1)

170. 0.032 2,353.617 ↑ 1.7 115 1

Sort (cost=80,940.37..80,940.87 rows=200 width=8) (actual time=2,353.611..2,353.617 rows=115 loops=1)

  • Sort Key: aws.id, (max(aws.air_segment_id))
  • Sort Method: quicksort Memory: 30kB
171. 0.054 2,353.585 ↑ 1.7 115 1

GroupAggregate (cost=60,576.37..80,932.73 rows=200 width=8) (actual time=2,353.397..2,353.585 rows=115 loops=1)

  • Group Key: aws.id
172. 0.108 2,353.531 ↑ 3,514.2 240 1

Merge Join (cost=60,576.37..76,713.66 rows=843,413 width=8) (actual time=2,353.389..2,353.531 rows=240 loops=1)

  • Merge Cond: ((aws.id = air_with_stay.id) AND (aws.stay_length = (max(air_with_stay.stay_length) OVER (?))))
173. 36.507 66.162 ↑ 698.4 263 1

Sort (cost=19,732.94..20,192.12 rows=183,675 width=12) (actual time=66.142..66.162 rows=263 loops=1)

  • Sort Key: aws.id, aws.stay_length
  • Sort Method: quicksort Memory: 14844kB
174. 29.655 29.655 ↓ 1.0 185,584 1

CTE Scan on air_with_stay aws (cost=0.00..3,673.50 rows=183,675 width=12) (actual time=0.001..29.655 rows=185,584 loops=1)

175. 547.930 2,287.261 ↑ 698.4 263 1

Sort (cost=40,843.43..41,302.62 rows=183,675 width=8) (actual time=2,287.240..2,287.261 rows=263 loops=1)

  • Sort Key: air_with_stay.id, (max(air_with_stay.stay_length) OVER (?))
  • Sort Method: quicksort Memory: 11948kB
176. 1,672.882 1,739.331 ↓ 1.0 185,584 1

WindowAgg (cost=19,732.94..22,947.25 rows=183,675 width=8) (actual time=54.476..1,739.331 rows=185,584 loops=1)

177. 38.798 66.449 ↓ 1.0 185,584 1

Sort (cost=19,732.94..20,192.12 rows=183,675 width=8) (actual time=54.463..66.449 rows=185,584 loops=1)

  • Sort Key: air_with_stay.id
  • Sort Method: quicksort Memory: 11948kB
178. 27.651 27.651 ↓ 1.0 185,584 1

CTE Scan on air_with_stay (cost=0.00..3,673.50 rows=183,675 width=8) (actual time=0.001..27.651 rows=185,584 loops=1)

179.          

CTE air_connection_data

180. 0.083 4,503,863.369 ↑ 3,219.1 262 1

Unique (cost=3,940,303.89..3,965,606.28 rows=843,413 width=180) (actual time=4,503,863.267..4,503,863.369 rows=262 loops=1)

181. 0.198 4,503,863.286 ↑ 3,219.1 262 1

Sort (cost=3,940,303.89..3,942,412.43 rows=843,413 width=180) (actual time=4,503,863.267..4,503,863.286 rows=262 loops=1)

  • Sort Key: asg_1.id, asg_1.airline, asg_1.flight_num, asg_1.departure_airport_code, asg_1.arrival_airport_code, asg_1.service_class, asg_1.air_segment_id, aws_1.stay_length, ((asg_1.air_segment_id = (SubPlan 28))), ((asg_1.air_segment_id = aws_1.min_segment_id)), ((asg_1.air_segment_id = aws_1.max_segment_id))
  • Sort Method: quicksort Memory: 45kB
182. 0.267 4,503,863.088 ↑ 3,219.1 262 1

Merge Join (cost=39,465.87..3,857,287.26 rows=843,413 width=180) (actual time=4,503,860.031..4,503,863.088 rows=262 loops=1)

  • Merge Cond: ((asg_1.id = aws_1.id) AND (asg_1.air_segment_id = aws_1.air_segment_id))
183. 586.169 6,751.190 ↑ 698.4 263 1

Sort (cost=19,732.94..20,192.12 rows=183,675 width=168) (actual time=6,751.169..6,751.190 rows=263 loops=1)

  • Sort Key: asg_1.id, asg_1.air_segment_id
  • Sort Method: quicksort Memory: 20643kB
184. 6,165.021 6,165.021 ↓ 1.0 185,584 1

CTE Scan on air_full_data asg_1 (cost=0.00..3,673.50 rows=183,675 width=168) (actual time=2,255.008..6,165.021 rows=185,584 loops=1)

185. 1,599.686 4,494,755.203 ↑ 698.4 263 1

Sort (cost=19,732.94..20,192.12 rows=183,675 width=20) (actual time=4,494,755.176..4,494,755.203 rows=263 loops=1)

  • Sort Key: aws_1.id, aws_1.air_segment_id
  • Sort Method: quicksort Memory: 14848kB
186. 4,493,155.517 4,493,155.517 ↓ 1.0 185,584 1

CTE Scan on air_with_stay aws_1 (cost=0.00..3,673.50 rows=183,675 width=20) (actual time=1,514.264..4,493,155.517 rows=185,584 loops=1)

187.          

SubPlan (forMerge Join)

188. 2,356.428 2,356.428 ↑ 1.0 1 262

CTE Scan on max_stay_segment_id msi (cost=0.00..4.50 rows=1 width=4) (actual time=8.989..8.994 rows=1 loops=262)

  • Filter: (id = asg_1.id)
  • Rows Removed by Filter: 114
189.          

CTE redemption_summary

190. 0.595 1.630 ↓ 3.6 154 1

GroupAggregate (cost=161.13..162.52 rows=43 width=123) (actual time=1.042..1.630 rows=154 loops=1)

  • Group Key: r.custid, r.points_service
191. 0.080 1.035 ↓ 3.6 154 1

Sort (cost=161.13..161.23 rows=43 width=123) (actual time=1.024..1.035 rows=154 loops=1)

  • Sort Key: r.custid, r.points_service
  • Sort Method: quicksort Memory: 52kB
192. 0.088 0.955 ↓ 3.6 154 1

Nested Loop Left Join (cost=0.58..159.96 rows=43 width=123) (actual time=0.082..0.955 rows=154 loops=1)

193. 0.055 0.405 ↓ 3.6 154 1

Nested Loop (cost=0.29..140.73 rows=43 width=123) (actual time=0.028..0.405 rows=154 loops=1)

194. 0.040 0.040 ↓ 3.6 155 1

CTE Scan on preselected_customers customer_9 (cost=0.00..0.86 rows=43 width=4) (actual time=0.002..0.040 rows=155 loops=1)

195. 0.310 0.310 ↑ 1.0 1 155

Index Scan using redemption_bookings_custid_idx on redemption_bookings r (cost=0.29..3.24 rows=1 width=123) (actual time=0.002..0.002 rows=1 loops=155)

  • Index Cond: (custid = customer_9.bookingnumber)
196. 0.462 0.462 ↓ 0.0 0 154

Index Scan using redemption_details_redemption_bookingid_idx on redemption_details d (cost=0.29..0.44 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=154)

  • Index Cond: (redemption_bookingid = r.redemption_bookingid)
  • Filter: (discount_segment_id > 0)
  • Rows Removed by Filter: 1
197.          

CTE activity_travelers

198. 0.014 0.260 ↑ 98.0 1 1

GroupAggregate (cost=111.86..113.82 rows=98 width=12) (actual time=0.260..0.260 rows=1 loops=1)

  • Group Key: c_9.bookingnumber, ast.activity_segment_id
199. 0.014 0.246 ↑ 98.0 1 1

Sort (cost=111.86..112.11 rows=98 width=12) (actual time=0.246..0.246 rows=1 loops=1)

  • Sort Key: c_9.bookingnumber, ast.activity_segment_id
  • Sort Method: quicksort Memory: 25kB
200. 0.045 0.232 ↑ 98.0 1 1

Nested Loop (cost=0.28..108.62 rows=98 width=12) (actual time=0.151..0.232 rows=1 loops=1)

201. 0.032 0.032 ↓ 3.6 155 1

CTE Scan on preselected_customers c_9 (cost=0.00..0.86 rows=43 width=4) (actual time=0.001..0.032 rows=155 loops=1)

202. 0.155 0.155 ↓ 0.0 0 155

Index Scan using activity_segment_traveler_custid_idx on activity_segment_traveler ast (cost=0.28..2.49 rows=2 width=12) (actual time=0.001..0.001 rows=0 loops=155)

  • Index Cond: (custid = c_9.bookingnumber)
203. 0.575 4,505,129.329 ↑ 24,431,878.9 155 1

Sort (cost=576,182,404,883,885.25..576,182,414,351,238.25 rows=3,786,941,225 width=1,933) (actual time=4,505,129.314..4,505,129.329 rows=155 loops=1)

  • Sort Key: c.bookingnumber, c.transactiondate, c.departuredate, c.enddate, c.cobrand, c.leadtime, c.travelduration, c.leadtravelerfirstname, c.leadtravelerlastname, ap.airpax, c.roompax, c.cruisepax, (COALESCE((SubPlan 32), (c.activitypax)::bigint)), c.offlineoronlinebooking, c.device, ((SubPlan 33)), ((SubPlan 34)), ((SubPlan 35)), ((SubPlan 36)), ((SubPlan 37)), ((SubPlan 38)), (COALESCE((SubPlan 39), (SubPlan 40), c.departurecity)), (COALESCE((SubPlan 41), (SubPlan 42), c.arrivalcity)), ((SubPlan 43)), ap.gdsrecordlocator, (COALESCE((SubPlan 44), 1::bigint)), (COALESCE((SubPlan 45), 0::bigint)), (COALESCE((SubPlan 46), 0::bigint)), (COALESCE((SubPlan 47), 0::bigint)), rs.hotelname, rs.starrating, rs.roomtype, rs.hotellocationcountry, rs.hotellocationcity, cs.carmodel, cs.carcategory, cs.carlocationcountry, cs.carlocationcity, acs.activityname, acs.activitylocation, (COALESCE(aps.customertosystemexchangerate, cs.customertosystemexchangerate, acs.customertosystemexchangerate, rs.customertosystemexchangerate)), aps.margin, cs.margin, acs.margin, rs.margin, booking_fees.supplierbookingfeeinsystemcurrency, booking_fees.supplierbookingfeeincustomercurrency, modification_fees.amendmentfeeinsystemcurrency, modification_fees.amendmentfeeincustomercurrency, cancellation_fees.cancellationfeeinsystemcurrency, cancellation_fees.cancellationfeeincustomercurrency, ((modification_fees.amendmentfeeinsystemcurrency + cancellation_fees.cancellationfeeinsystemcurrency)), ((modification_fees.amendmentfeeincustomercurrency + cancellation_fees.cancellationfeeincustomercurrency)), redemption_summary.redemption_confirmation_code, redemption_summary.discount_points, c.payment_total_points, (GREATEST(aps.pointstocustomerexchangerate, cs.pointstocustomerexchangerate, acs.pointstocustomerexchangerate, rs.pointstocustomerexchangerate)), ((SubPlan 48)), ((SubPlan 49)), ((SubPlan 50)), ((SubPlan 51)), ((SubPlan 52)), ((SubPlan 53)), ((SubPlan 54)), c.private_key_hash, c.initialization_vector, config.accounting_code, c.rewardsid, ((redemption_summary.points + c.payment_total_points)), redemption_summary.pointsservice, ((SubPlan 55)), redemption_summary.segmentid, (COALESCE(c.cobrandcountrycode, c.systemcountrycode, 'US'::text)), c.customercurrency, aps.totalairinsystemcurrency, aps.totalairincustomercurrency, rs.totalhotelinsystemcurrency, rs.totalhotelincustomercurrency, cs.totalcarinsystemcurrency, cs.totalcarincustomercurrency, acs.totalactivityinsystemcurrency, acs.totalactivityincustomercurrency, booking_fees.totalbookingfeeinsystemcurrency, booking_fees.totalbookingfeeincustomercurrency, c.totaldiscountincustomercurrency, c.totaldiscountinsystemcurrency, c.totalbookingincustomercurrency, c.totalbookinginsystemcurrency, (CASE WHEN c.ismodified THEN 'amended'::text ELSE 'confirmed'::text END), ((ap.custid IS NOT NULL)), ((cs.bookingnumber IS NOT NULL)), ((acs.bookingnumber IS NOT NULL)), ((rs.bookingnumber IS NOT NULL)), ap.hasquickadd, cs.hasquickadd, acs.hasquickadd, rs.hasquickadd, (array_to_string((SubPlan 56), ','::text))
  • Sort Method: quicksort Memory: 180kB
204. 2.544 4,505,128.754 ↑ 24,431,878.9 155 1

Hash Left Join (cost=4,828.53..576,177,725,163,366.12 rows=3,786,941,225 width=1,933) (actual time=4,504,963.223..4,505,128.754 rows=155 loops=1)

  • Hash Cond: (c.cobrand = config.cobrand_name)
205. 0.390 696.713 ↑ 24,431,878.9 155 1

Merge Left Join (cost=4,823.17..62,495,972.14 rows=3,786,941,225 width=1,901) (actual time=695.810..696.713 rows=155 loops=1)

  • Merge Cond: (c.bookingnumber = cancellation_fees.bookingnumber)
206. 0.238 650.520 ↑ 1,044,990.5 155 1

Merge Left Join (cost=4,444.62..5,286,541.39 rows=161,973,534 width=1,861) (actual time=649.697..650.520 rows=155 loops=1)

  • Merge Cond: (c.bookingnumber = redemption_summary.bookingnumber)
207. 6.300 648.442 ↑ 1,044,990.5 155 1

Merge Left Join (cost=4,442.60..2,452,002.51 rows=161,973,534 width=1,753) (actual time=647.903..648.442 rows=155 loops=1)

  • Merge Cond: (c.bookingnumber = booking_fees.bookingnumber)
208. 2.176 386.313 ↑ 6,504.0 155 1

Merge Left Join (cost=1,394.40..16,831.29 rows=1,008,113 width=1,649) (actual time=385.914..386.313 rows=155 loops=1)

  • Merge Cond: (c.bookingnumber = rs.bookingnumber)
209. 0.262 167.656 ↑ 100.3 155 1

Merge Left Join (cost=249.15..525.19 rows=15,548 width=1,316) (actual time=167.361..167.656 rows=155 loops=1)

  • Merge Cond: (c.bookingnumber = acs.bookingnumber)
210. 0.259 141.017 ↑ 14.9 155 1

Merge Left Join (cost=151.88..188.92 rows=2,302 width=1,055) (actual time=140.792..141.017 rows=155 loops=1)

  • Merge Cond: (c.bookingnumber = modification_fees.bookingnumber)
211. 0.196 68.926 ↑ 3.2 155 1

Sort (cost=88.42..89.67 rows=502 width=1,015) (actual time=68.889..68.926 rows=155 loops=1)

  • Sort Key: c.bookingnumber
  • Sort Method: quicksort Memory: 105kB
212. 0.493 68.730 ↑ 3.2 155 1

Hash Right Join (cost=5.38..65.90 rows=502 width=1,015) (actual time=68.535..68.730 rows=155 loops=1)

  • Hash Cond: (cs.bookingnumber = c.bookingnumber)
213. 50.108 50.108 ↓ 1.0 2,379 1

CTE Scan on car_summary cs (cost=0.00..46.74 rows=2,337 width=325) (actual time=8.783..50.108 rows=2,379 loops=1)

214. 0.174 18.129 ↓ 3.6 155 1

Hash (cost=4.84..4.84 rows=43 width=690) (actual time=18.129..18.129 rows=155 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
215. 0.066 17.955 ↓ 3.6 155 1

Hash Left Join (cost=2.79..4.84 rows=43 width=690) (actual time=17.772..17.955 rows=155 loops=1)

  • Hash Cond: (c.bookingnumber = aps.custid)
216. 0.111 14.726 ↓ 3.6 155 1

Hash Left Join (cost=1.40..2.85 rows=43 width=530) (actual time=14.603..14.726 rows=155 loops=1)

  • Hash Cond: (c.bookingnumber = ap.custid)
217. 0.591 0.591 ↓ 3.6 155 1

CTE Scan on preselected_customers c (cost=0.00..0.86 rows=43 width=453) (actual time=0.565..0.591 rows=155 loops=1)

218. 0.048 14.024 ↓ 3.2 137 1

Hash (cost=0.86..0.86 rows=43 width=77) (actual time=14.024..14.024 rows=137 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
219. 13.976 13.976 ↓ 3.2 137 1

CTE Scan on air_summary ap (cost=0.00..0.86 rows=43 width=77) (actual time=13.187..13.976 rows=137 loops=1)

220. 0.043 3.163 ↓ 3.2 137 1

Hash (cost=0.86..0.86 rows=43 width=164) (actual time=3.163..3.163 rows=137 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
221. 3.120 3.120 ↓ 3.2 137 1

CTE Scan on air_pricing_summary aps (cost=0.00..0.86 rows=43 width=164) (actual time=2.981..3.120 rows=137 loops=1)

222. 0.284 71.832 ↑ 1.3 705 1

Sort (cost=63.46..65.75 rows=917 width=44) (actual time=71.787..71.832 rows=705 loops=1)

  • Sort Key: modification_fees.bookingnumber
  • Sort Method: quicksort Memory: 78kB
223. 71.548 71.548 ↑ 1.3 705 1

CTE Scan on modification_fees (cost=0.00..18.34 rows=917 width=44) (actual time=70.930..71.548 rows=705 loops=1)

224. 0.968 26.377 ↓ 1.0 1,383 1

Sort (cost=97.27..100.65 rows=1,351 width=261) (actual time=26.293..26.377 rows=1,383 loops=1)

  • Sort Key: acs.bookingnumber
  • Sort Method: quicksort Memory: 323kB
225. 25.409 25.409 ↓ 1.0 1,383 1

CTE Scan on activities_summary acs (cost=0.00..27.02 rows=1,351 width=261) (actual time=5.079..25.409 rows=1,383 loops=1)

226. 11.303 216.481 ↓ 1.0 13,114 1

Sort (cost=1,145.25..1,177.67 rows=12,968 width=333) (actual time=215.664..216.481 rows=13,114 loops=1)

  • Sort Key: rs.bookingnumber
  • Sort Method: quicksort Memory: 3844kB
227. 205.178 205.178 ↓ 1.0 13,119 1

CTE Scan on room_summary rs (cost=0.00..259.36 rows=12,968 width=333) (actual time=49.576..205.178 rows=13,119 loops=1)

228. 26.219 255.829 ↓ 1.3 42,032 1

Sort (cost=3,048.20..3,128.54 rows=32,134 width=108) (actual time=250.207..255.829 rows=42,032 loops=1)

  • Sort Key: booking_fees.bookingnumber
  • Sort Method: quicksort Memory: 4822kB
229. 229.610 229.610 ↓ 1.3 42,054 1

CTE Scan on booking_fees (cost=0.00..642.68 rows=32,134 width=108) (actual time=162.689..229.610 rows=42,054 loops=1)

230. 0.106 1.840 ↓ 3.6 154 1

Sort (cost=2.03..2.13 rows=43 width=112) (actual time=1.789..1.840 rows=154 loops=1)

  • Sort Key: redemption_summary.bookingnumber
  • Sort Method: quicksort Memory: 52kB
231. 1.734 1.734 ↓ 3.6 154 1

CTE Scan on redemption_summary (cost=0.00..0.86 rows=43 width=112) (actual time=1.044..1.734 rows=154 loops=1)

232. 1.531 45.803 ↑ 1.4 3,258 1

Sort (cost=378.55..390.24 rows=4,676 width=44) (actual time=45.557..45.803 rows=3,258 loops=1)

  • Sort Key: cancellation_fees.bookingnumber
  • Sort Method: quicksort Memory: 333kB
233. 44.272 44.272 ↑ 1.4 3,258 1

CTE Scan on cancellation_fees (cost=0.00..93.52 rows=4,676 width=44) (actual time=41.383..44.272 rows=3,258 loops=1)

234. 0.008 0.248 ↑ 11.0 15 1

Hash (cost=3.30..3.30 rows=165 width=64) (actual time=0.248..0.248 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
235. 0.240 0.240 ↑ 11.0 15 1

CTE Scan on cobrand_config config (cost=0.00..3.30 rows=165 width=64) (actual time=0.134..0.240 rows=15 loops=1)

236.          

SubPlan (forHash Left Join)

237. 0.155 0.465 ↑ 1.0 1 155

Aggregate (cost=2.21..2.22 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=155)

238. 0.310 0.310 ↓ 0.0 0 155

CTE Scan on activity_travelers at (cost=0.00..2.21 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=155)

  • Filter: (bookingnumber = c.bookingnumber)
  • Rows Removed by Filter: 1
239. 4,503,870.030 4,503,870.030 ↑ 2,109.0 1 155

CTE Scan on air_connection_data aic (cost=0.00..18,976.79 rows=2,109 width=32) (actual time=29,057.206..29,057.226 rows=1 loops=155)

  • Filter: (departure AND (bookingnumber = c.bookingnumber))
  • Rows Removed by Filter: 261
240. 5.890 5.890 ↑ 2,109.0 1 155

CTE Scan on air_connection_data aic_1 (cost=0.00..18,976.79 rows=2,109 width=32) (actual time=0.024..0.038 rows=1 loops=155)

  • Filter: (max_stay AND (bookingnumber = c.bookingnumber))
  • Rows Removed by Filter: 261
241. 6.510 6.510 ↑ 2,109.0 1 155

CTE Scan on air_connection_data aic_2 (cost=0.00..18,976.79 rows=2,109 width=32) (actual time=0.023..0.042 rows=1 loops=155)

  • Filter: (departure AND (bookingnumber = c.bookingnumber))
  • Rows Removed by Filter: 261
242. 6.510 6.510 ↑ 2,109.0 1 155

CTE Scan on air_connection_data aic_3 (cost=0.00..18,976.79 rows=2,109 width=32) (actual time=0.024..0.042 rows=1 loops=155)

  • Filter: (departure AND (bookingnumber = c.bookingnumber))
  • Rows Removed by Filter: 261
243. 5.890 5.890 ↑ 2,109.0 1 155

CTE Scan on air_connection_data aic_4 (cost=0.00..18,976.79 rows=2,109 width=32) (actual time=0.024..0.038 rows=1 loops=155)

  • Filter: (max_stay AND (bookingnumber = c.bookingnumber))
  • Rows Removed by Filter: 261
244. 6.510 6.510 ↑ 2,109.0 1 155

CTE Scan on air_connection_data aic_5 (cost=0.00..18,976.79 rows=2,109 width=32) (actual time=0.024..0.042 rows=1 loops=155)

  • Filter: (last_segment AND (bookingnumber = c.bookingnumber))
  • Rows Removed by Filter: 261
245. 6.510 6.510 ↑ 2,109.0 1 155

CTE Scan on air_connection_data aic_6 (cost=0.00..18,976.79 rows=2,109 width=32) (actual time=0.024..0.042 rows=1 loops=155)

  • Filter: (departure AND (bookingnumber = c.bookingnumber))
  • Rows Removed by Filter: 261
246. 0.000 0.054 ↑ 1.0 1 18

Aggregate (cost=3.30..3.31 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=18)

247. 0.054 0.054 ↓ 0.0 0 18

Index Scan using car_segment_custid_idx on car_segment csg (cost=0.28..3.30 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=18)

  • Index Cond: (custid = c.bookingnumber)
248. 5.890 5.890 ↑ 2,109.0 1 155

CTE Scan on air_connection_data aic_7 (cost=0.00..18,976.79 rows=2,109 width=32) (actual time=0.024..0.038 rows=1 loops=155)

  • Filter: (max_stay AND (bookingnumber = c.bookingnumber))
  • Rows Removed by Filter: 261
249. 0.000 0.080 ↑ 1.0 1 40

Aggregate (cost=3.30..3.31 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=40)

250. 0.080 0.080 ↓ 0.0 0 40

Index Scan using car_segment_custid_idx on car_segment csg_1 (cost=0.28..3.30 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=40)

  • Index Cond: (custid = c.bookingnumber)
251. 260.865 260.865 ↑ 1.0 1 155

CTE Scan on air_legs_count alc (cost=0.00..4.50 rows=1 width=8) (actual time=1.229..1.683 rows=1 loops=155)

  • Filter: (id = c.bookingnumber)
  • Rows Removed by Filter: 137
252. 144.615 144.615 ↑ 1.0 1 155

CTE Scan on traveler_type_count ttc (cost=0.00..1.07 rows=1 width=8) (actual time=0.926..0.933 rows=1 loops=155)

  • Filter: ((bookingnumber = c.bookingnumber) AND (type = 'ADT'::text))
  • Rows Removed by Filter: 162
253. 2.480 2.480 ↓ 0.0 0 155

CTE Scan on traveler_type_count ttc_1 (cost=0.00..1.07 rows=1 width=8) (actual time=0.015..0.016 rows=0 loops=155)

  • Filter: ((bookingnumber = c.bookingnumber) AND (type = 'CNN'::text))
  • Rows Removed by Filter: 163
254. 2.325 2.325 ↓ 0.0 0 155

CTE Scan on traveler_type_count ttc_2 (cost=0.00..1.07 rows=1 width=8) (actual time=0.015..0.015 rows=0 loops=155)

  • Filter: ((bookingnumber = c.bookingnumber) AND (type = 'INF'::text))
  • Rows Removed by Filter: 163
255. 2.325 2.325 ↓ 0.0 0 155

CTE Scan on traveler_type_count ttc_3 (cost=0.00..1.07 rows=1 width=8) (actual time=0.015..0.015 rows=0 loops=155)

  • Filter: ((bookingnumber = c.bookingnumber) AND (type = 'INS'::text))
  • Rows Removed by Filter: 163
256. 0.310 1.240 ↑ 1.0 1 155

Aggregate (cost=4.50..4.52 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=155)

257. 0.930 0.930 ↓ 0.0 0 155

Index Scan using accounting_custid_idx on accounting (cost=0.42..4.50 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=155)

  • Index Cond: (custid = c.bookingnumber)
  • Filter: ((actual_amount > 0::double precision) AND (pmtmethod = 'creditcard'::text))
  • Rows Removed by Filter: 1
258. 0.620 1.240 ↑ 1.0 1 155

Aggregate (cost=3.31..3.32 rows=1 width=3) (actual time=0.008..0.008 rows=1 loops=155)

259. 0.620 0.620 ↓ 0.0 0 155

Index Scan using cc_holder_custid_idx on payment_holder (cost=0.29..3.31 rows=1 width=3) (actual time=0.004..0.004 rows=0 loops=155)

  • Index Cond: (custid = c.bookingnumber)
260. 0.155 0.465 ↑ 1.0 1 155

Aggregate (cost=4.50..4.52 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=155)

261. 0.310 0.310 ↓ 0.0 0 155

Index Scan using accounting_custid_idx on accounting accounting_1 (cost=0.42..4.50 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=155)

  • Index Cond: (custid = c.bookingnumber)
  • Filter: ((actual_amount > 0::double precision) AND (pmtmethod = 'misc'::text))
  • Rows Removed by Filter: 1
262. 0.000 0.310 ↑ 1.0 1 155

Aggregate (cost=4.50..4.52 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=155)

263. 0.310 0.310 ↓ 0.0 0 155

Index Scan using accounting_custid_idx on accounting accounting_2 (cost=0.42..4.50 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=155)

  • Index Cond: (custid = c.bookingnumber)
  • Filter: ((actual_amount > 0::double precision) AND (cc_auth_no = 'transfer'::text))
  • Rows Removed by Filter: 1
264. 0.000 0.310 ↑ 1.0 1 155

Aggregate (cost=4.50..4.52 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=155)

265. 0.310 0.310 ↓ 0.0 0 155

Index Scan using accounting_custid_idx on accounting accounting_3 (cost=0.42..4.50 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=155)

  • Index Cond: (custid = c.bookingnumber)
  • Filter: ((actual_amount > 0::double precision) AND (pmtmethod = 'check'::text))
  • Rows Removed by Filter: 1
266. 0.000 0.310 ↑ 1.0 1 155

Aggregate (cost=4.50..4.52 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=155)

267. 0.310 0.310 ↓ 0.0 0 155

Index Scan using accounting_custid_idx on accounting accounting_4 (cost=0.42..4.50 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=155)

  • Index Cond: (custid = c.bookingnumber)
  • Filter: ((actual_amount > 0::double precision) AND (pmtmethod = 'acc_adj'::text))
  • Rows Removed by Filter: 1
268. 0.930 96.255 ↑ 1.0 1 155

Aggregate (cost=284.78..284.79 rows=1 width=14) (actual time=0.621..0.621 rows=1 loops=155)

269. 95.325 95.325 ↓ 0.0 0 155

Seq Scan on discount_segment d_1 (cost=0.00..284.77 rows=1 width=14) (actual time=0.615..0.615 rows=0 loops=155)

  • Filter: (custid = c.bookingnumber)
  • Rows Removed by Filter: 6241
270. 0.310 0.775 ↑ 1.0 1 155

Aggregate (cost=3.31..3.32 rows=1 width=5) (actual time=0.005..0.005 rows=1 loops=155)

271. 0.465 0.465 ↓ 0.0 0 155

Index Scan using cc_holder_custid_idx on payment_holder payment_holder_1 (cost=0.29..3.31 rows=1 width=5) (actual time=0.002..0.003 rows=0 loops=155)

  • Index Cond: (custid = c.bookingnumber)
272. 0.775 1.395 ↑ 1.0 1 155

Aggregate (cost=2.53..2.54 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=155)

273. 0.310 0.620 ↑ 100.0 1 155

HashAggregate (cost=0.77..1.28 rows=100 width=0) (actual time=0.004..0.004 rows=1 loops=155)

  • Group Key: unnest((((ap.gds || cs.gds) || acs.gds) || rs.gds))
274. 0.310 0.310 ↑ 100.0 1 155

Result (cost=0.00..0.52 rows=100 width=0) (actual time=0.002..0.002 rows=1 loops=155)

Planning time : 47.448 ms
Execution time : 4,505,163.991 ms