explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CmMb

Settings
# exclusive inclusive rows x rows loops node
1. 3,984.644 128,524.903 ↓ 208.3 2,576,400 1

Gather (cost=326,465.60..337,330.67 rows=12,371 width=672) (actual time=62,669.448..128,524.903 rows=2,576,400 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
2. 1,562.214 124,540.259 ↓ 177.0 1,288,200 2 / 2

Hash Join (cost=325,465.60..335,093.57 rows=7,277 width=672) (actual time=62,684.592..124,540.259 rows=1,288,200 loops=2)

  • Hash Cond: (fb.backend_ab_test_dwid = dabt.ab_test_dwid)
3. 1,397.867 122,977.099 ↓ 177.0 1,288,200 2 / 2

Hash Join (cost=325,427.29..335,036.04 rows=7,277 width=676) (actual time=62,683.594..122,977.099 rows=1,288,200 loops=2)

  • Hash Cond: (fb.data_acquisition_source_dwid = ddas.data_acquisition_source_dwid)
4. 1,410.295 121,578.775 ↓ 177.0 1,288,200 2 / 2

Hash Join (cost=325,409.05..334,998.56 rows=7,277 width=673) (actual time=62,683.126..121,578.775 rows=1,288,200 loops=2)

  • Hash Cond: (fb.device_platform_dwid = ddp.device_platform_dwid)
5. 1,428.651 120,168.286 ↓ 177.0 1,288,200 2 / 2

Hash Join (cost=325,391.92..334,962.17 rows=7,277 width=645) (actual time=62,682.919..120,168.286 rows=1,288,200 loops=2)

  • Hash Cond: (fb.issuer_country_dwid = dc_i.country_dwid)
6. 1,399.289 118,738.997 ↓ 177.0 1,288,200 2 / 2

Hash Join (cost=325,378.27..334,929.10 rows=7,277 width=645) (actual time=62,682.272..118,738.997 rows=1,288,200 loops=2)

  • Hash Cond: (fb.service_package_dwid = dsp.service_package_dwid)
7. 1,406.688 117,339.226 ↓ 177.0 1,288,200 2 / 2

Hash Join (cost=325,358.73..334,890.30 rows=7,277 width=643) (actual time=62,681.778..117,339.226 rows=1,288,200 loops=2)

  • Hash Cond: (fb.fare_type_dwid = dft.fare_type_dwid)
8. 1,892.552 115,932.454 ↓ 177.0 1,288,200 2 / 2

Nested Loop (cost=325,354.21..334,865.95 rows=7,277 width=641) (actual time=62,681.685..115,932.454 rows=1,288,200 loops=2)

9. 1,429.515 111,463.502 ↓ 177.0 1,288,200 2 / 2

Hash Join (cost=325,353.79..331,662.18 rows=7,277 width=634) (actual time=62,681.675..111,463.502 rows=1,288,200 loops=2)

  • Hash Cond: (fb.customer_nationality_dwid = dc_cn.country_dwid)
10. 1,477.589 110,033.825 ↓ 177.0 1,288,200 2 / 2

Hash Join (cost=325,340.14..331,629.11 rows=7,277 width=635) (actual time=62,681.503..110,033.825 rows=1,288,200 loops=2)

  • Hash Cond: (fb.beeline_dwid = db.beeline_dwid)
11. 2,294.049 108,556.024 ↓ 177.0 1,288,200 2 / 2

Nested Loop (cost=325,325.49..331,595.17 rows=7,277 width=607) (actual time=62,681.280..108,556.024 rows=1,288,200 loops=2)

12. 2,064.350 96,145.575 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=325,325.07..326,540.54 rows=11,481 width=600) (actual time=62,673.887..96,145.575 rows=2,023,280 loops=2)

  • Hash Cond: (fb.price_category_per_passenger_dwid = dpc_pp.price_category_dwid)
13. 2,193.754 94,080.836 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=325,310.42..326,495.47 rows=11,481 width=572) (actual time=62,673.490..94,080.836 rows=2,023,280 loops=2)

  • Hash Cond: (fb.purchase_source_dwid = da_ps.application_dwid)
14. 2,141.884 91,858.354 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=324,289.77..325,444.67 rows=11,481 width=558) (actual time=62,644.678..91,858.354 rows=2,023,280 loops=2)

  • Hash Cond: (fb.return_window_dwid = dtw_r.time_window_dwid)
15. 2,194.932 89,715.316 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=324,251.25..325,375.89 rows=11,481 width=558) (actual time=62,643.511..89,715.316 rows=2,023,280 loops=2)

  • Hash Cond: (fb.booking_window_dwid = dtw_b.time_window_dwid)
16. 2,165.207 87,519.565 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=324,212.73..325,307.11 rows=11,481 width=530) (actual time=62,642.678..87,519.565 rows=2,023,280 loops=2)

  • Hash Cond: (fb.dst_transport_hub_dwid = dth_d.transport_hub_dwid)
17. 2,142.997 85,334.200 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=323,435.67..324,499.91 rows=11,481 width=525) (actual time=62,622.429..85,334.200 rows=2,023,280 loops=2)

  • Hash Cond: (fb.src_transport_hub_dwid = dth_s.transport_hub_dwid)
18. 2,079.728 83,177.109 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=322,658.62..323,692.70 rows=11,481 width=520) (actual time=62,608.246..83,177.109 rows=2,023,280 loops=2)

  • Hash Cond: (fb.device_type_dwid = ddt.device_type_dwid)
19. 2,112.078 81,097.110 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=322,637.64..323,641.40 rows=11,481 width=492) (actual time=62,607.963..81,097.110 rows=2,023,280 loops=2)

  • Hash Cond: (fb.price_category_dwid = dpc.price_category_dwid)
20. 2,079.287 78,985.017 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=322,622.99..323,596.33 rows=11,481 width=464) (actual time=62,607.938..78,985.017 rows=2,023,280 loops=2)

  • Hash Cond: (fb.currency_dwid = dcur.currency_dwid)
21. 2,040.327 76,905.608 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=322,616.88..323,559.37 rows=11,481 width=464) (actual time=62,607.804..76,905.608 rows=2,023,280 loops=2)

  • Hash Cond: (fb.market_dwid = dc_m.country_dwid)
22. 2,092.996 74,865.114 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=322,603.23..323,515.07 rows=11,481 width=465) (actual time=62,607.628..74,865.114 rows=2,023,280 loops=2)

  • Hash Cond: (fb.trip_type_dwid = tt.trip_type_dwid)
23. 2,111.984 72,771.871 ↓ 176.2 2,023,280 2 / 2

Hash Join (cost=322,582.25..323,463.77 rows=11,481 width=437) (actual time=62,607.373..72,771.871 rows=2,023,280 loops=2)

  • Hash Cond: (fb.content_type_dwid = ct.content_type_dwid)
24. 4,410.178 70,659.869 ↓ 176.2 2,023,280 2 / 2

Merge Join (cost=322,561.28..323,412.47 rows=11,481 width=409) (actual time=62,607.343..70,659.869 rows=2,023,280 loops=2)

  • Merge Cond: (da.application_dwid = fb.application_dwid)
25. 8.273 8.273 ↑ 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..8.273 rows=8,014 loops=2)

26. 19,166.499 66,241.418 ↓ 207.0 4,040,638 2 / 2

Sort (cost=322,560.99..322,609.79 rows=19,518 width=395) (actual time=62,597.881..66,241.418 rows=4,040,638 loops=2)

  • Sort Key: fb.application_dwid
  • Sort Method: external sort Disk: 1,615,320kB
27. 6,158.466 47,074.919 ↓ 207.3 4,046,560 2 / 2

Nested Loop (cost=0.58..321,170.09 rows=19,518 width=395) (actual time=1.103..47,074.919 rows=4,046,560 loops=2)

28. 0.729 0.729 ↑ 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.728..0.729 rows=1 loops=2)

  • Index Cond: (code = 'unpaid'::text)
29. 40,915.724 40,915.724 ↓ 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,766.66 rows=1,840,286 width=367) (actual time=0.368..40,915.724 rows=4,046,560 loops=2)

  • Index Cond: (booking_status_dwid = bs.booking_status_dwid)
30. 0.003 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.015 0.015 ↑ 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.013..0.015 rows=3 loops=2)

32. 0.005 0.247 ↑ 134.0 5 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 0.242 0.242 ↑ 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.239..0.242 rows=5 loops=2)

34. 0.083 0.167 ↑ 1.0 251 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
35. 0.084 0.084 ↑ 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.084 rows=251 loops=2)

36. 0.058 0.122 ↑ 1.0 183 2 / 2

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

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

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

38. 0.005 0.015 ↑ 62.9 7 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
39. 0.010 0.010 ↑ 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.006..0.010 rows=7 loops=2)

40. 0.005 0.271 ↑ 134.0 5 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 0.266 0.266 ↑ 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.264..0.266 rows=5 loops=2)

42. 7.443 14.094 ↓ 1.0 18,776 2 / 2

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,128kB
43. 6.651 6.651 ↓ 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.004..6.651 rows=18,776 loops=2)

44. 7.363 20.158 ↓ 1.0 18,776 2 / 2

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,128kB
45. 12.795 12.795 ↓ 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.009..12.795 rows=18,776 loops=2)

46. 0.430 0.819 ↑ 1.0 1,001 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 76kB
47. 0.389 0.389 ↑ 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.004..0.389 rows=1,001 loops=2)

48. 0.352 1.154 ↑ 1.0 1,001 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 48kB
49. 0.802 0.802 ↑ 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.009..0.802 rows=1,001 loops=2)

50. 7.130 28.728 ↑ 1.5 16,066 2 / 2

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,146kB
51. 21.598 21.598 ↑ 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.010..21.598 rows=16,066 loops=2)

52. 0.005 0.389 ↑ 62.9 7 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
53. 0.384 0.384 ↑ 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.381..0.384 rows=7 loops=2)

54. 10,116.400 10,116.400 ↑ 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.005..0.005 rows=1 loops=4,046,560)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
56. 0.209 0.209 ↑ 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.207..0.209 rows=4 loops=2)

57. 0.084 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.078 0.078 ↑ 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.078 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.046 0.084 ↑ 1.0 112 2 / 2

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

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

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

62. 0.169 0.482 ↑ 1.0 513 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
63. 0.313 0.313 ↑ 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.313 rows=513 loops=2)

64. 0.091 0.638 ↑ 1.0 251 2 / 2

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

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

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

66. 0.004 0.194 ↑ 106.0 5 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
67. 0.190 0.190 ↑ 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.187..0.190 rows=5 loops=2)

68. 0.177 0.457 ↑ 1.1 516 2 / 2

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
69. 0.280 0.280 ↑ 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.006..0.280 rows=516 loops=2)

70. 0.224 0.946 ↑ 1.0 700 2 / 2

Hash (cost=29.25..29.25 rows=725 width=4) (actual time=0.946..0.946 rows=700 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
71. 0.722 0.722 ↑ 1.0 700 2 / 2

Seq Scan on dim_ab_test dabt (cost=0.00..29.25 rows=725 width=4) (actual time=0.008..0.722 rows=700 loops=2)

Planning time : 121.918 ms
Execution time : 129,501.754 ms