explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OQSmq

Settings
# exclusive inclusive rows x rows loops node
1. 1.439 8,816.796 ↓ 1.5 60 1

Nested Loop Left Join (cost=4,542.07..4,468,573.48 rows=40 width=196) (actual time=163.110..8,816.796 rows=60 loops=1)

2.          

CTE customer_ids

3. 0.011 14.793 ↓ 1.5 60 1

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

4. 0.024 14.782 ↓ 1.5 60 1

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

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

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

6. 0.256 14.440 ↓ 1.5 60 1

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

7. 0.491 12.324 ↓ 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=11.861..12.324 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. 11.833 11.833 ↑ 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=11.833..11.833 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.860 1.860 ↓ 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.003..0.003 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.263 16.417 ↓ 1.5 60 1

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

14. 0.318 15.974 ↓ 1.5 60 1

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

15. 0.147 14.985 ↓ 1.5 61 1

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

  • Group Key: customer_ids.customer_id
16. 0.007 14.838 ↓ 1.5 61 1

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

17. 0.025 14.830 ↓ 1.5 60 1

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

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

CTE Scan on customer_ids (cost=0.00..0.78 rows=39 width=8) (actual time=14.782..14.805 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.671 0.671 ↑ 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.011..0.011 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. 1.560 1.560 ↓ 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.026..0.026 rows=0 loops=60)

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

SubPlan (for Nested Loop Left Join)

24. 4.080 4.080 ↓ 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.068..0.068 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.180 8,793.300 ↑ 1.0 1 60

Aggregate (cost=111,589.00..111,589.02 rows=1 width=32) (actual time=146.554..146.555 rows=1 loops=60)

27. 0.600 8,790.120 ↓ 1.5 3 60

Subquery Scan on tt (cost=3.53..111,588.97 rows=2 width=112) (actual time=50.828..146.502 rows=3 loops=60)

28. 7.250 8,789.520 ↓ 1.5 3 60

Nested Loop (cost=3.53..111,588.95 rows=2 width=280) (actual time=50.823..146.492 rows=3 loops=60)

29. 0.742 56.520 ↓ 1.5 3 60

Nested Loop (cost=3.11..43.38 rows=2 width=84) (actual time=0.310..0.942 rows=3 loops=60)

  • Join Filter: (chkn_1.booking_id = bknggg.booking_id)
30. 0.532 50.100 ↑ 1.0 3 60

Nested Loop (cost=2.82..42.00 rows=3 width=68) (actual time=0.297..0.835 rows=3 loops=60)

31. 0.575 45.120 ↑ 1.0 2 60

Hash Join (cost=2.40..29.10 rows=2 width=52) (actual time=0.286..0.752 rows=2 loops=60)

  • Hash Cond: (chkn_1.location_id = loc.location_id)
32. 0.681 44.520 ↑ 1.0 2 60

Nested Loop (cost=0.70..27.40 rows=2 width=44) (actual time=0.282..0.742 rows=2 loops=60)

33. 15.900 15.900 ↑ 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.257..0.265 rows=2 loops=60)

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

Index Scan using unique_checkin_customer_per_timerange on booking_checkin_to_station bcts_1 (cost=0.28..8.30 rows=1 width=36) (actual time=0.199..0.201 rows=1 loops=139)

  • Index Cond: (checkin_id = chkn_1.checkin_id)
  • Filter: (NOT deleted)
35. 0.006 0.025 ↑ 1.0 31 1

Hash (cost=1.31..1.31 rows=31 width=12) (actual time=0.025..0.025 rows=31 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
36. 0.019 0.019 ↑ 1.0 31 1

Seq Scan on location loc (cost=0.00..1.31 rows=31 width=12) (actual time=0.012..0.019 rows=31 loops=1)

37. 4.448 4.448 ↑ 1.0 1 139

Index Only Scan using btt_bookingid_timeslotid_index on booking_to_timeslot btt_2 (cost=0.42..6.44 rows=1 width=16) (actual time=0.029..0.032 rows=1 loops=139)

  • Index Cond: (booking_id = chkn_1.booking_id)
  • Heap Fetches: 0
38. 5.678 5.678 ↑ 1.0 1 167

Index Scan using booking_pkey on booking bknggg (cost=0.29..0.45 rows=1 width=32) (actual time=0.034..0.034 rows=1 loops=167)

  • Index Cond: (booking_id = btt_2.booking_id)
39. 1.670 1.670 ↑ 1.0 1 167

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=167)

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

SubPlan (for Nested Loop)

41. 1.336 13.026 ↑ 1.0 1 167

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

42. 0.668 8.517 ↑ 1.0 1 167

Aggregate (cost=21.50..21.51 rows=1 width=8) (actual time=0.051..0.051 rows=1 loops=167)

43. 0.666 7.849 ↑ 1.0 1 167

Nested Loop (cost=1.40..21.50 rows=1 width=4) (actual time=0.044..0.047 rows=1 loops=167)

44. 0.830 5.845 ↑ 1.0 1 167

Nested Loop (cost=1.13..21.21 rows=1 width=12) (actual time=0.033..0.035 rows=1 loops=167)

45. 0.334 1.670 ↑ 1.0 1 167

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

46. 0.501 0.501 ↑ 1.0 1 167

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

  • Index Cond: (booking_id = chkn_1.booking_id)
  • Heap Fetches: 17
47. 0.835 0.835 ↑ 1.0 1 167

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=167)

  • Index Cond: (booking_id = chkn_1.booking_id)
48. 3.345 3.345 ↑ 1.0 1 223

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.015..0.015 rows=1 loops=223)

  • Index Cond: (booking_to_timeslot_id = btt.booking_to_timeslot_id)
49. 1.338 1.338 ↑ 1.0 1 223

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=223)

  • Index Cond: (item_id = bti.item_id)
  • Filter: (item_category_id = 1)
  • Rows Removed by Filter: 0
50. 0.334 3.173 ↑ 1.0 1 167

Aggregate (cost=21.25..21.26 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=167)

51. 0.486 2.839 ↓ 0.0 0 167

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

52. 0.444 2.338 ↓ 0.0 0 167

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

53. 0.334 1.002 ↑ 1.0 1 167

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

54. 0.334 0.334 ↑ 1.0 1 167

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=167)

  • Index Cond: (booking_id = chkn_1.booking_id)
  • Heap Fetches: 17
55. 0.334 0.334 ↑ 1.0 1 167

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=167)

  • Index Cond: (booking_id = chkn_1.booking_id)
56. 0.892 0.892 ↓ 0.0 0 223

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=223)

  • Index Cond: (booking_to_timeslot_id = btt_1.booking_to_timeslot_id)
57. 0.015 0.015 ↑ 1.0 1 1

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

  • Index Cond: (addon_id = bta.addon_id)
  • Filter: (lower(title) = ANY ('{timesplit,"time split"}'::text[]))
58. 5.344 8,711.054 ↑ 1.0 1 167

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

59. 2.505 8,705.710 ↑ 1.8 9 167

Subquery Scan on t (cost=0.86..55,728.13 rows=16 width=50) (actual time=50.958..52.130 rows=9 loops=167)

60. 119.651 8,703.205 ↑ 1.8 9 167

Nested Loop (cost=0.86..55,727.97 rows=16 width=154) (actual time=50.947..52.115 rows=9 loops=167)

  • Join Filter: (gt.game_identifier = g.game_identifier)
  • Rows Removed by Join Filter: 6,767
61. 40.080 40.080 ↓ 1.0 775 167

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

  • Filter: (NOT exempt)
  • Rows Removed by Filter: 24
62. 101.035 8,542.050 ↑ 1.8 9 129,425

Materialize (cost=0.86..55,502.87 rows=16 width=49) (actual time=0.049..0.066 rows=9 loops=129,425)

63. 3.355 8,441.015 ↑ 1.8 9 167

Nested Loop (cost=0.86..55,502.79 rows=16 width=49) (actual time=37.968..50.545 rows=9 loops=167)

64. 8,420.140 8,420.140 ↑ 1.8 9 167

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

  • Index Cond: (checkin_to_station_id = bcts_1.checkin_to_station_id)
65. 17.520 17.520 ↑ 1.0 1 1,460

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

  • Index Cond: (gametracking_id = gtb.gametracking_id)
66.          

SubPlan (for Nested Loop)

67. 1.424 1.424 ↑ 1.0 1 1,424

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