explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LECt

Settings
# exclusive inclusive rows x rows loops node
1. 31,372.583 151,523.195 ↓ 258.8 64,598,514 1

Hash Join (cost=326,633.76..342,661.84 rows=249,606 width=685) (actual time=83,483.177..151,523.195 rows=64,598,514 loops=1)

  • Hash Cond: (fb.corona_region_id = dc_c.corona_region_id)
2. 0.000 120,150.405 ↓ 209.2 2,576,400 1

Gather (cost=326,622.57..337,414.50 rows=12,315 width=676) (actual time=83,482.963..120,150.405 rows=2,576,400 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
3. 1,519.168 137,768.003 ↓ 177.8 1,288,200 2 / 2

Hash Join (cost=325,622.57..335,183.00 rows=7,244 width=676) (actual time=83,503.008..137,768.003 rows=1,288,200 loops=2)

  • Hash Cond: (fb.data_acquisition_source_dwid = ddas.data_acquisition_source_dwid)
4. 1,402.635 136,248.502 ↓ 177.8 1,288,200 2 / 2

Hash Join (cost=325,604.33..335,145.60 rows=7,244 width=673) (actual time=83,502.633..136,248.502 rows=1,288,200 loops=2)

  • Hash Cond: (fb.device_platform_dwid = ddp.device_platform_dwid)
5. 1,417.420 134,845.847 ↓ 177.8 1,288,200 2 / 2

Hash Join (cost=325,587.21..335,109.31 rows=7,244 width=645) (actual time=83,502.604..134,845.847 rows=1,288,200 loops=2)

  • Hash Cond: (fb.issuer_country_dwid = dc_i.country_dwid)
6. 1,399.551 133,428.263 ↓ 177.8 1,288,200 2 / 2

Hash Join (cost=325,573.56..335,076.32 rows=7,244 width=645) (actual time=83,502.431..133,428.263 rows=1,288,200 loops=2)

  • Hash Cond: (fb.service_package_dwid = dsp.service_package_dwid)
7. 1,416.025 132,028.373 ↓ 177.8 1,288,200 2 / 2

Hash Join (cost=325,554.02..335,037.61 rows=7,244 width=643) (actual time=83,502.084..132,028.373 rows=1,288,200 loops=2)

  • Hash Cond: (fb.fare_type_dwid = dft.fare_type_dwid)
8. 1,862.824 130,612.268 ↓ 177.8 1,288,200 2 / 2

Nested Loop (cost=325,549.50..335,013.35 rows=7,244 width=641) (actual time=83,501.995..130,612.268 rows=1,288,200 loops=2)

9. 1,448.619 126,173.044 ↓ 177.8 1,288,200 2 / 2

Hash Join (cost=325,549.07..331,824.10 rows=7,244 width=634) (actual time=83,501.986..126,173.044 rows=1,288,200 loops=2)

  • Hash Cond: (fb.customer_nationality_dwid = dc_cn.country_dwid)
10. 1,415.601 124,724.263 ↓ 177.8 1,288,200 2 / 2

Hash Join (cost=325,535.43..331,791.12 rows=7,244 width=635) (actual time=83,501.815..124,724.263 rows=1,288,200 loops=2)

  • Hash Cond: (fb.beeline_dwid = db.beeline_dwid)
11. 2,750.666 123,308.647 ↓ 177.8 1,288,200 2 / 2

Nested Loop (cost=325,520.78..331,757.28 rows=7,244 width=607) (actual time=83,501.790..123,308.647 rows=1,288,200 loops=2)

12. 2,051.342 116,511.421 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=325,520.36..326,702.65 rows=11,481 width=600) (actual time=83,500.295..116,511.421 rows=2,023,280 loops=2)

  • Hash Cond: (fb.price_category_per_passenger_dwid = dpc_pp.price_category_dwid)
13. 2,099.753 114,460.062 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=325,505.71..326,657.57 rows=11,481 width=572) (actual time=83,500.267..114,460.062 rows=2,023,280 loops=2)

  • Hash Cond: (fb.purchase_source_dwid = da_ps.application_dwid)
14. 2,189.083 112,342.743 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=324,485.05..325,606.78 rows=11,481 width=558) (actual time=83,482.619..112,342.743 rows=2,023,280 loops=2)

  • Hash Cond: (fb.return_window_dwid = dtw_r.time_window_dwid)
15. 2,180.086 110,152.981 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=324,446.53..325,538.00 rows=11,481 width=558) (actual time=83,481.929..110,152.981 rows=2,023,280 loops=2)

  • Hash Cond: (fb.booking_window_dwid = dtw_b.time_window_dwid)
16. 2,081.533 107,972.131 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=324,408.01..325,469.22 rows=11,481 width=530) (actual time=83,481.155..107,972.131 rows=2,023,280 loops=2)

  • Hash Cond: (fb.dst_transport_hub_dwid = dth_d.transport_hub_dwid)
17. 2,074.514 105,876.987 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=323,630.95..324,662.02 rows=11,481 width=525) (actual time=83,467.463..105,876.987 rows=2,023,280 loops=2)

  • Hash Cond: (fb.src_transport_hub_dwid = dth_s.transport_hub_dwid)
18. 2,031.484 103,789.830 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=322,853.90..323,854.81 rows=11,481 width=520) (actual time=83,454.740..103,789.830 rows=2,023,280 loops=2)

  • Hash Cond: (fb.device_type_dwid = ddt.device_type_dwid)
19. 2,123.770 101,758.327 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=322,832.92..323,803.51 rows=11,481 width=492) (actual time=83,454.711..101,758.327 rows=2,023,280 loops=2)

  • Hash Cond: (fb.price_category_dwid = dpc.price_category_dwid)
20. 2,028.579 99,634.544 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=322,818.28..323,758.44 rows=11,481 width=464) (actual time=83,454.689..99,634.544 rows=2,023,280 loops=2)

  • Hash Cond: (fb.currency_dwid = dcur.currency_dwid)
21. 1,969.891 97,605.841 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=322,812.16..323,721.48 rows=11,481 width=464) (actual time=83,454.555..97,605.841 rows=2,023,280 loops=2)

  • Hash Cond: (fb.market_dwid = dc_m.country_dwid)
22. 2,042.256 95,635.779 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=322,798.51..323,677.18 rows=11,481 width=465) (actual time=83,454.374..95,635.779 rows=2,023,280 loops=2)

  • Hash Cond: (fb.trip_type_dwid = tt.trip_type_dwid)
23. 2,119.249 93,593.505 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=322,777.54..323,625.88 rows=11,481 width=437) (actual time=83,454.347..93,593.505 rows=2,023,280 loops=2)

  • Hash Cond: (fb.content_type_dwid = ct.content_type_dwid)
24. 4,436.673 91,474.238 ↓ 176.2 2,023,280 2 / 2

Merge Join (cost=322,756.56..323,574.58 rows=11,481 width=409) (actual time=83,454.319..91,474.238 rows=2,023,280 loops=2)

  • Merge Cond: (da.application_dwid = fb.application_dwid)
25. 7.154 7.154 ↑ 1.8 8,014 2 / 2

Parallel Index Scan using dim_application_pkey on dim_application da (cost=0.29..613.65 rows=14,407 width=22) (actual time=0.006..7.154 rows=8,014 loops=2)

26. 18,274.400 87,030.411 ↓ 207.3 4,045,942 2 / 2

Sort (cost=322,756.27..322,805.07 rows=19,518 width=395) (actual time=83,444.835..87,030.411 rows=4,045,942 loops=2)

  • Sort Key: fb.application_dwid
  • Sort Method: external sort Disk: 1,615,320kB
27. 5,744.252 68,756.011 ↓ 207.3 4,046,560 2 / 2

Nested Loop (cost=0.58..321,365.37 rows=19,518 width=395) (actual time=7.090..68,756.011 rows=4,046,560 loops=2)

28. 0.020 0.020 ↑ 1.0 1 2 / 2

Index Scan using dim_booking_status_code_key on dim_booking_status bs (cost=0.15..0.57 rows=1 width=36) (actual time=0.019..0.020 rows=1 loops=2)

  • Index Cond: (code = 'unpaid'::text)
29. 63,011.739 63,011.739 ↓ 2.2 4,046,560 2 / 2

Index Scan using idx_view_booking_current_booking_status_dwid on view_booking_current fb (cost=0.43..302,962.17 rows=1,840,263 width=367) (actual time=7.063..63,011.739 rows=4,046,560 loops=2)

  • Index Cond: (booking_status_dwid = bs.booking_status_dwid)
30. 0.004 0.018 ↑ 223.3 3 2 / 2

Hash (cost=12.60..12.60 rows=670 width=36) (actual time=0.018..0.018 rows=3 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
31. 0.014 0.014 ↑ 223.3 3 2 / 2

Index Scan using dim_content_type_pkey on dim_content_type ct (cost=0.15..12.60 rows=670 width=36) (actual time=0.012..0.014 rows=3 loops=2)

32. 0.005 0.018 ↑ 134.0 5 2 / 2

Hash (cost=12.60..12.60 rows=670 width=36) (actual time=0.017..0.018 rows=5 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 0.013 0.013 ↑ 134.0 5 2 / 2

Index Scan using dim_trip_type_pkey on dim_trip_type tt (cost=0.15..12.60 rows=670 width=36) (actual time=0.011..0.013 rows=5 loops=2)

34. 0.086 0.171 ↑ 1.0 251 2 / 2

Hash (cost=10.51..10.51 rows=251 width=7) (actual time=0.171..0.171 rows=251 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
35. 0.085 0.085 ↑ 1.0 251 2 / 2

Seq Scan on dim_country dc_m (cost=0.00..10.51 rows=251 width=7) (actual time=0.003..0.085 rows=251 loops=2)

36. 0.063 0.124 ↑ 1.0 183 2 / 2

Hash (cost=3.83..3.83 rows=183 width=8) (actual time=0.124..0.124 rows=183 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
37. 0.061 0.061 ↑ 1.0 183 2 / 2

Seq Scan on dim_currency dcur (cost=0.00..3.83 rows=183 width=8) (actual time=0.006..0.061 rows=183 loops=2)

38. 0.005 0.013 ↑ 62.9 7 2 / 2

Hash (cost=9.15..9.15 rows=440 width=36) (actual time=0.013..0.013 rows=7 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
39. 0.008 0.008 ↑ 62.9 7 2 / 2

Index Scan using dim_price_category_pkey on dim_price_category dpc (cost=0.15..9.15 rows=440 width=36) (actual time=0.005..0.008 rows=7 loops=2)

40. 0.004 0.019 ↑ 134.0 5 2 / 2

Hash (cost=12.60..12.60 rows=670 width=36) (actual time=0.019..0.019 rows=5 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 0.015 0.015 ↑ 134.0 5 2 / 2

Index Scan using dim_device_type_pkey on dim_device_type ddt (cost=0.15..12.60 rows=670 width=36) (actual time=0.013..0.015 rows=5 loops=2)

42. 6.688 12.643 ↓ 1.0 18,776 2 / 2

Hash (cost=542.58..542.58 rows=18,758 width=13) (actual time=12.642..12.643 rows=18,776 loops=2)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,128kB
43. 5.955 5.955 ↓ 1.0 18,776 2 / 2

Seq Scan on dim_transport_hub dth_s (cost=0.00..542.58 rows=18,758 width=13) (actual time=0.003..5.955 rows=18,776 loops=2)

44. 7.043 13.611 ↓ 1.0 18,776 2 / 2

Hash (cost=542.58..542.58 rows=18,758 width=13) (actual time=13.611..13.611 rows=18,776 loops=2)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,128kB
45. 6.568 6.568 ↓ 1.0 18,776 2 / 2

Seq Scan on dim_transport_hub dth_d (cost=0.00..542.58 rows=18,758 width=13) (actual time=0.006..6.568 rows=18,776 loops=2)

46. 0.401 0.764 ↑ 1.0 1,001 2 / 2

Hash (cost=26.01..26.01 rows=1,001 width=36) (actual time=0.764..0.764 rows=1,001 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 76kB
47. 0.363 0.363 ↑ 1.0 1,001 2 / 2

Seq Scan on dim_time_window dtw_b (cost=0.00..26.01 rows=1,001 width=36) (actual time=0.002..0.363 rows=1,001 loops=2)

48. 0.341 0.679 ↑ 1.0 1,001 2 / 2

Hash (cost=26.01..26.01 rows=1,001 width=8) (actual time=0.679..0.679 rows=1,001 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 48kB
49. 0.338 0.338 ↑ 1.0 1,001 2 / 2

Seq Scan on dim_time_window dtw_r (cost=0.00..26.01 rows=1,001 width=8) (actual time=0.007..0.338 rows=1,001 loops=2)

50. 6.888 17.566 ↑ 1.5 16,066 2 / 2

Hash (cost=714.50..714.50 rows=24,492 width=22) (actual time=17.566..17.566 rows=16,066 loops=2)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,146kB
51. 10.678 10.678 ↑ 1.5 16,066 2 / 2

Index Scan using dim_application_pkey on dim_application da_ps (cost=0.29..714.50 rows=24,492 width=22) (actual time=0.011..10.678 rows=16,066 loops=2)

52. 0.004 0.017 ↑ 62.9 7 2 / 2

Hash (cost=9.15..9.15 rows=440 width=36) (actual time=0.017..0.017 rows=7 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
53. 0.013 0.013 ↑ 62.9 7 2 / 2

Index Scan using dim_price_category_pkey on dim_price_category dpc_pp (cost=0.15..9.15 rows=440 width=36) (actual time=0.009..0.013 rows=7 loops=2)

54. 4,046.560 4,046.560 ↑ 1.0 1 4,046,560 / 2

Index Scan using dim_carrier_combo_pkey on dim_carrier_combo dcc_o (cost=0.42..0.44 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=4,046,560)

  • Index Cond: (carrier_combo_dwid = fb.original_carrier_combo_dwid)
55. 0.004 0.015 ↑ 110.0 4 2 / 2

Hash (cost=9.15..9.15 rows=440 width=36) (actual time=0.015..0.015 rows=4 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
56. 0.011 0.011 ↑ 110.0 4 2 / 2

Index Scan using dim_beeline_pkey on dim_beeline db (cost=0.15..9.15 rows=440 width=36) (actual time=0.010..0.011 rows=4 loops=2)

57. 0.083 0.162 ↑ 1.0 251 2 / 2

Hash (cost=10.51..10.51 rows=251 width=7) (actual time=0.162..0.162 rows=251 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
58. 0.079 0.079 ↑ 1.0 251 2 / 2

Seq Scan on dim_country dc_cn (cost=0.00..10.51 rows=251 width=7) (actual time=0.002..0.079 rows=251 loops=2)

59. 2,576.400 2,576.400 ↑ 1.0 1 2,576,400 / 2

Index Scan using dim_carrier_combo_pkey on dim_carrier_combo dcc (cost=0.42..0.44 rows=1 width=15) (actual time=0.002..0.002 rows=1 loops=2,576,400)

  • Index Cond: (carrier_combo_dwid = fb.carrier_combo_dwid)
60. 0.040 0.080 ↑ 1.0 112 2 / 2

Hash (cost=3.12..3.12 rows=112 width=10) (actual time=0.079..0.080 rows=112 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
61. 0.040 0.040 ↑ 1.0 112 2 / 2

Seq Scan on dim_fare_type dft (cost=0.00..3.12 rows=112 width=10) (actual time=0.005..0.040 rows=112 loops=2)

62. 0.170 0.339 ↑ 1.0 513 2 / 2

Hash (cost=13.13..13.13 rows=513 width=10) (actual time=0.339..0.339 rows=513 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
63. 0.169 0.169 ↑ 1.0 513 2 / 2

Seq Scan on dim_service_package dsp (cost=0.00..13.13 rows=513 width=10) (actual time=0.005..0.169 rows=513 loops=2)

64. 0.082 0.164 ↑ 1.0 251 2 / 2

Hash (cost=10.51..10.51 rows=251 width=8) (actual time=0.164..0.164 rows=251 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
65. 0.082 0.082 ↑ 1.0 251 2 / 2

Seq Scan on dim_country dc_i (cost=0.00..10.51 rows=251 width=8) (actual time=0.003..0.082 rows=251 loops=2)

66. 0.004 0.020 ↑ 106.0 5 2 / 2

Hash (cost=10.50..10.50 rows=530 width=36) (actual time=0.020..0.020 rows=5 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
67. 0.016 0.016 ↑ 106.0 5 2 / 2

Index Scan using dim_device_platform_pkey on dim_device_platform ddp (cost=0.15..10.50 rows=530 width=36) (actual time=0.013..0.016 rows=5 loops=2)

68. 0.169 0.333 ↑ 1.1 516 2 / 2

Hash (cost=11.44..11.44 rows=544 width=11) (actual time=0.332..0.333 rows=516 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
69. 0.164 0.164 ↑ 1.1 516 2 / 2

Seq Scan on dim_data_acquisition_source ddas (cost=0.00..11.44 rows=544 width=11) (actual time=0.011..0.164 rows=516 loops=2)

70. 0.082 0.207 ↑ 1.0 251 1

Hash (cost=8.05..8.05 rows=251 width=17) (actual time=0.206..0.207 rows=251 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
71. 0.125 0.125 ↑ 1.0 251 1

Index Scan using idx_dim_country_corona_region_id on dim_country dc_c (cost=0.14..8.05 rows=251 width=17) (actual time=0.008..0.125 rows=251 loops=1)

Planning time : 3,585.148 ms
Execution time : 166,272.015 ms