explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eflI

Settings
# exclusive inclusive rows x rows loops node
1. 1.470 9,660.138 ↓ 1.5 60 1

Nested Loop Left Join (cost=4,542.07..2,237,442.67 rows=40 width=196) (actual time=216.895..9,660.138 rows=60 loops=1)

2.          

CTE customer_ids

3. 0.010 2.098 ↓ 1.5 60 1

Unique (cost=4,538.92..4,539.11 rows=39 width=8) (actual time=2.086..2.098 rows=60 loops=1)

4. 0.023 2.088 ↓ 1.5 60 1

Sort (cost=4,538.92..4,539.01 rows=39 width=8) (actual time=2.085..2.088 rows=60 loops=1)

  • Sort Key: cst_1.customer_id
  • Sort Method: quicksort Memory: 27kB
5. 0.005 2.065 ↓ 1.5 60 1

Nested Loop (cost=65.73..4,537.89 rows=39 width=8) (actual time=0.330..2.065 rows=60 loops=1)

6. 0.000 1.760 ↓ 1.5 60 1

Nested Loop (cost=65.30..4,509.38 rows=39 width=8) (actual time=0.317..1.760 rows=60 loops=1)

7. 0.475 0.610 ↓ 2.2 620 1

Bitmap Heap Scan on booking_checkin_to_station bcts (cost=64.88..2,340.42 rows=284 width=8) (actual time=0.154..0.610 rows=620 loops=1)

  • Recheck Cond: ((check_in_time >= '2020-03-01 05:00:00+00'::timestamp with time zone) AND (check_in_time <= '2020-03-03 05:00:00+00'::timestamp with time zone))
  • Filter: ((NOT deleted) AND ((SubPlan 1) > '10'::double precision))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=36
8. 0.135 0.135 ↑ 1.4 623 1

Bitmap Index Scan on booking_checkin_to_station_check_in_time_check_out_time_idx (cost=0.00..64.81 rows=853 width=0) (actual time=0.135..0.135 rows=623 loops=1)

  • Index Cond: ((check_in_time >= '2020-03-01 05:00:00+00'::timestamp with time zone) AND (check_in_time <= '2020-03-03 05:00:00+00'::timestamp with time zone))
9.          

SubPlan (for Bitmap Heap Scan)

10. 0.000 0.000 ↑ 1.0 1 623

Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=623)

11. 1.240 1.240 ↓ 0.0 0 620

Index Scan using booking_to_checkin_pk on booking_checkin chkn (cost=0.42..7.64 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=620)

  • Index Cond: (checkin_id = bcts.checkin_id)
  • Filter: (location_id = 1)
  • Rows Removed by Filter: 1
12. 0.300 0.300 ↑ 1.0 1 60

Index Only Scan using customer_customer_id_is_deleted_idx on customer cst_1 (cost=0.42..0.73 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=60)

  • Index Cond: (customer_id = chkn.customer_id)
  • Heap Fetches: 2
13. 0.244 3.768 ↓ 1.5 60 1

Nested Loop (cost=2.54..346.55 rows=40 width=76) (actual time=2.181..3.768 rows=60 loops=1)

14. 0.338 3.344 ↓ 1.5 60 1

Nested Loop (cost=2.40..340.38 rows=40 width=64) (actual time=2.173..3.344 rows=60 loops=1)

15. 0.130 2.274 ↓ 1.5 61 1

HashAggregate (cost=1.98..2.38 rows=40 width=8) (actual time=2.158..2.274 rows=61 loops=1)

  • Group Key: customer_ids.customer_id
16. 0.006 2.144 ↓ 1.5 61 1

Append (cost=0.88..1.88 rows=40 width=8) (actual time=2.131..2.144 rows=61 loops=1)

17. 0.028 2.137 ↓ 1.5 60 1

HashAggregate (cost=0.88..1.27 rows=39 width=8) (actual time=2.131..2.137 rows=60 loops=1)

  • Group Key: customer_ids.customer_id
18. 2.109 2.109 ↓ 1.5 60 1

CTE Scan on customer_ids (cost=0.00..0.78 rows=39 width=8) (actual time=2.088..2.109 rows=60 loops=1)

19. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

20. 0.732 0.732 ↑ 1.0 1 61

Index Scan using customer_id_total_check_ins_gender_birth_date_idx on customer cst (cost=0.42..8.44 rows=1 width=64) (actual time=0.012..0.012 rows=1 loops=61)

  • Index Cond: (customer_id = customer_ids.customer_id)
21. 0.180 0.180 ↑ 1.0 1 60

Index Only Scan using facility_details_location_id_time_zone_idx on facility_details fd (cost=0.14..0.16 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=60)

  • Index Cond: (location_id = cst.location_id)
  • Heap Fetches: 0
22. 0.600 0.600 ↓ 0.0 0 60

Index Scan using customer_adult_details_pkey on customer_adult_details cad (cost=0.42..0.49 rows=1 width=33) (actual time=0.010..0.010 rows=0 loops=60)

  • Index Cond: (cst.customer_id = customer_adult_details_id)
23.          

SubPlan (for Nested Loop Left Join)

24. 0.540 0.540 ↓ 0.0 0 60

Index Only Scan using customer_adult_to_child_customeradultdetailsid_index on customer_adult_to_child catd (cost=0.42..4.45 rows=2 width=0) (actual time=0.009..0.009 rows=0 loops=60)

  • Index Cond: (customer_adult_details_id = cst.customer_id)
  • Heap Fetches: 0
25. 0.000 0.000 ↓ 0.0 0

Seq Scan on customer_adult_to_child catd_1 (cost=0.00..2,150.26 rows=120,126 width=4) (never executed)

26. 3.120 9,653.760 ↑ 1.0 1 60

Aggregate (cost=55,810.73..55,810.75 rows=1 width=32) (actual time=160.896..160.896 rows=1 loops=60)

27. 0.600 9,650.640 ↓ 3.0 3 60

Subquery Scan on tt (cost=1.83..55,810.71 rows=1 width=112) (actual time=58.352..160.844 rows=3 loops=60)

28. 10.152 9,650.040 ↓ 3.0 3 60

Nested Loop Left Join (cost=1.83..55,810.70 rows=1 width=280) (actual time=58.347..160.834 rows=3 loops=60)

29. 0.484 21.780 ↓ 3.0 3 60

Nested Loop Left Join (cost=1.41..37.92 rows=1 width=84) (actual time=0.081..0.363 rows=3 loops=60)

30. 0.572 19.800 ↓ 2.0 2 60

Nested Loop Left Join (cost=0.99..37.45 rows=1 width=84) (actual time=0.071..0.330 rows=2 loops=60)

31. 1.716 17.460 ↓ 2.0 2 60

Nested Loop (cost=0.70..29.14 rows=1 width=52) (actual time=0.059..0.291 rows=2 loops=60)

  • Join Filter: (chkn_1.location_id = loc.location_id)
  • Rows Removed by Join Filter: 48
32. 0.774 14.520 ↓ 2.0 2 60

Nested Loop (cost=0.70..27.44 rows=1 width=44) (actual time=0.037..0.242 rows=2 loops=60)

33. 6.240 6.240 ↑ 1.0 2 60

Index Scan using customer_checkin_customer_id_idx on booking_checkin chkn_1 (cost=0.42..10.78 rows=2 width=24) (actual time=0.011..0.104 rows=2 loops=60)

  • Index Cond: (customer_id = cst.customer_id)
34. 7.367 7.506 ↑ 1.0 1 139

Index Scan using unique_checkin_customer_per_timerange on booking_checkin_to_station bcts_1 (cost=0.28..8.32 rows=1 width=36) (actual time=0.052..0.054 rows=1 loops=139)

  • Index Cond: (checkin_id = chkn_1.checkin_id)
  • Filter: ((NOT deleted) AND ((SubPlan 8) > '10'::double precision))
  • Rows Removed by Filter: 0
35.          

SubPlan (for Index Scan)

36. 0.139 0.139 ↑ 1.0 1 139

Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=139)

37. 1.224 1.224 ↑ 1.4 22 136

Seq Scan on location loc (cost=0.00..1.31 rows=31 width=12) (actual time=0.005..0.009 rows=22 loops=136)

38. 1.768 1.768 ↑ 1.0 1 136

Index Scan using booking_pkey on booking bknggg (cost=0.29..8.31 rows=1 width=32) (actual time=0.013..0.013 rows=1 loops=136)

  • Index Cond: (chkn_1.booking_id = booking_id)
39. 1.496 1.496 ↑ 1.0 1 136

Index Only Scan using btt_bookingid_timeslotid_index on booking_to_timeslot btt_2 (cost=0.42..0.46 rows=1 width=16) (actual time=0.008..0.011 rows=1 loops=136)

  • Index Cond: (booking_id = bknggg.booking_id)
  • Heap Fetches: 0
40. 1.640 1.640 ↑ 1.0 1 164

Index Scan using timeslot_pkey on timeslot ts (cost=0.42..0.53 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=164)

  • Index Cond: (btt_2.timeslot_id = timeslot_id)
41.          

SubPlan (for Nested Loop Left Join)

42. 1.476 11.316 ↑ 1.0 1 164

Nested Loop (cost=42.75..42.81 rows=1 width=32) (actual time=0.069..0.069 rows=1 loops=164)

43. 0.492 6.888 ↑ 1.0 1 164

Aggregate (cost=21.50..21.51 rows=1 width=8) (actual time=0.042..0.042 rows=1 loops=164)

44. 0.648 6.396 ↑ 1.0 1 164

Nested Loop (cost=1.40..21.50 rows=1 width=4) (actual time=0.036..0.039 rows=1 loops=164)

45. 0.644 4.428 ↑ 1.0 1 164

Nested Loop (cost=1.13..21.21 rows=1 width=12) (actual time=0.025..0.027 rows=1 loops=164)

46. 0.492 1.804 ↑ 1.0 1 164

Nested Loop (cost=0.71..12.76 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=164)

47. 0.492 0.492 ↑ 1.0 1 164

Index Only Scan using booking_pkey on booking bkng (cost=0.29..4.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=164)

  • Index Cond: (booking_id = chkn_1.booking_id)
  • Heap Fetches: 17
48. 0.820 0.820 ↑ 1.0 1 164

Index Scan using btt_bookingid_timeslotid_index on booking_to_timeslot btt (cost=0.42..8.44 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=164)

  • Index Cond: (booking_id = chkn_1.booking_id)
49. 1.980 1.980 ↑ 1.0 1 220

Index Scan using booking_to_item_bookingtotimeslotid_index on booking_to_item bti (cost=0.42..8.44 rows=1 width=20) (actual time=0.008..0.009 rows=1 loops=220)

  • Index Cond: (booking_to_timeslot_id = btt.booking_to_timeslot_id)
50. 1.320 1.320 ↑ 1.0 1 220

Index Scan using item_pkey on item itm (cost=0.27..0.29 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=220)

  • Index Cond: (item_id = bti.item_id)
  • Filter: (item_category_id = 1)
  • Rows Removed by Filter: 0
51. 0.328 2.952 ↑ 1.0 1 164

Aggregate (cost=21.25..21.26 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=164)

52. 0.314 2.624 ↓ 0.0 0 164

Nested Loop (cost=1.13..21.25 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=164)

53. 0.432 2.296 ↓ 0.0 0 164

Nested Loop (cost=0.99..21.07 rows=1 width=12) (actual time=0.014..0.014 rows=0 loops=164)

54. 0.328 0.984 ↑ 1.0 1 164

Nested Loop (cost=0.71..12.76 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=164)

55. 0.328 0.328 ↑ 1.0 1 164

Index Only Scan using booking_pkey on booking bkng_1 (cost=0.29..4.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=164)

  • Index Cond: (booking_id = chkn_1.booking_id)
  • Heap Fetches: 17
56. 0.328 0.328 ↑ 1.0 1 164

Index Scan using btt_bookingid_timeslotid_index on booking_to_timeslot btt_1 (cost=0.42..8.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=164)

  • Index Cond: (booking_id = chkn_1.booking_id)
57. 0.880 0.880 ↓ 0.0 0 220

Index Scan using booking_to_addon_bookingtotimeslotid_index on booking_to_addon bta (cost=0.28..8.30 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=220)

  • Index Cond: (booking_to_timeslot_id = btt_1.booking_to_timeslot_id)
58. 0.014 0.014 ↑ 1.0 1 1

Index Scan using addon_pkey on addon adn (cost=0.14..0.16 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: (addon_id = bta.addon_id)
  • Filter: (lower(title) = ANY ('{timesplit,"time split"}'::text[]))
59. 5.412 9,605.152 ↑ 1.0 1 164

Aggregate (cost=55,728.17..55,728.18 rows=1 width=32) (actual time=58.568..58.568 rows=1 loops=164)

60. 1.804 9,599.740 ↑ 1.8 9 164

Subquery Scan on t (cost=0.86..55,728.13 rows=16 width=50) (actual time=57.219..58.535 rows=9 loops=164)

61. 152.848 9,597.936 ↑ 1.8 9 164

Nested Loop (cost=0.86..55,727.97 rows=16 width=154) (actual time=57.212..58.524 rows=9 loops=164)

  • Join Filter: (gt.game_identifier = g.game_identifier)
  • Rows Removed by Join Filter: 6,877
  • -> Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1,421)"Planning Time: 5.934 ms
62. 39.688 39.688 ↓ 1.0 775 164

Seq Scan on game g (cost=0.00..30.98 rows=774 width=25) (actual time=0.003..0.242 rows=775 loops=164)

  • Filter: (NOT exempt)
  • Rows Removed by Filter: 24
63. 83.968 9,405.400 ↑ 1.8 9 127,100

Materialize (cost=0.86..55,502.87 rows=16 width=49) (actual time=0.055..0.074 rows=9 loops=127,100)

64. 2.574 9,321.432 ↑ 1.8 9 164

Nested Loop (cost=0.86..55,502.79 rows=16 width=49) (actual time=42.925..56.838 rows=9 loops=164)

65. 9,310.116 9,310.116 ↑ 1.8 9 164

Index Scan using gametracking_breakdown_unique_station_checkin on gametracking_breakdown gtb (cost=0.43..55,367.59 rows=16 width=40) (actual time=42.900..56.769 rows=9 loops=164)

  • Index Cond: (checkin_to_station_id = bcts_1.checkin_to_station_id)
66. 8.742 8.742 ↑ 1.0 1 1,457

Index Scan using gametracking_gametracking_id_time_finalized_idx on gametracking gt (cost=0.43..8.45 rows=1 width=17) (actual time=0.006..0.006 rows=1 loops=1,457)

  • Index Cond: (gametracking_id = gtb.gametracking_id)