explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o7Nr

Settings
# exclusive inclusive rows x rows loops node
1. 0.022 7,614.209 ↑ 1.0 100 1

Limit (cost=115,699.09..115,727.84 rows=100 width=128) (actual time=7,613.387..7,614.209 rows=100 loops=1)

2.          

CTE event_seating_lookup_aggregate_report

3. 0.259 5.017 ↓ 4.1 815 1

Unique (cost=404.97..411.25 rows=200 width=12) (actual time=4.689..5.017 rows=815 loops=1)

4. 0.317 4.758 ↑ 1.0 815 1

Sort (cost=404.97..407.06 rows=838 width=12) (actual time=4.688..4.758 rows=815 loops=1)

  • Sort Key: "*SELECT* 1".cinema_id, ((0)::bigint) DESC
  • Sort Method: quicksort Memory: 63kB
5. 0.395 4.441 ↑ 1.0 815 1

HashAggregate (cost=347.52..355.90 rows=838 width=12) (actual time=4.299..4.441 rows=815 loops=1)

  • Group Key: "*SELECT* 1".cinema_id, ((0)::bigint)
6. 0.093 4.046 ↑ 1.0 815 1

Append (cost=0.28..343.33 rows=838 width=12) (actual time=0.038..4.046 rows=815 loops=1)

7. 0.154 1.387 ↑ 1.0 815 1

Subquery Scan on *SELECT* 1 (cost=0.28..66.69 rows=815 width=12) (actual time=0.037..1.387 rows=815 loops=1)

8. 1.233 1.233 ↑ 1.0 815 1

Index Only Scan using cinema_pkey on cinema c1_1 (cost=0.28..56.50 rows=815 width=8) (actual time=0.036..1.233 rows=815 loops=1)

  • Heap Fetches: 345
9. 0.002 2.566 ↓ 0.0 0 1

GroupAggregate (cost=271.82..272.22 rows=23 width=12) (actual time=2.566..2.566 rows=0 loops=1)

  • Group Key: c1_2.id
10. 0.019 2.564 ↓ 0.0 0 1

Sort (cost=271.82..271.88 rows=23 width=4) (actual time=2.564..2.564 rows=0 loops=1)

  • Sort Key: c1_2.id
  • Sort Method: quicksort Memory: 25kB
11. 0.000 2.545 ↓ 0.0 0 1

Nested Loop (cost=1.40..271.30 rows=23 width=4) (actual time=2.545..2.545 rows=0 loops=1)

12. 0.001 2.545 ↓ 0.0 0 1

Nested Loop (cost=1.13..264.26 rows=23 width=4) (actual time=2.545..2.545 rows=0 loops=1)

13. 0.001 2.544 ↓ 0.0 0 1

Nested Loop (cost=0.85..257.40 rows=23 width=4) (actual time=2.544..2.544 rows=0 loops=1)

14. 2.543 2.543 ↓ 0.0 0 1

Index Scan using event_seating_lookup_ended_at_idx on event_seating_lookup esl1 (cost=0.42..15.10 rows=99 width=4) (actual time=2.543..2.543 rows=0 loops=1)

  • Index Cond: (ended_at IS NULL)
  • Filter: (started_at IS NOT NULL)
15. 0.000 0.000 ↓ 0.0 0

Index Scan using event_pkey on event e1_1 (cost=0.42..2.45 rows=1 width=8) (never executed)

  • Index Cond: (id = esl1.event_id)
  • Filter: ((event_seating_asked_at IS NOT NULL) AND (start_time > now()))
16. 0.000 0.000 ↓ 0.0 0

Index Scan using venue_pkey on venue v1_1 (cost=0.28..0.30 rows=1 width=8) (never executed)

  • Index Cond: (id = e1_1.venue_id)
17. 0.000 0.000 ↓ 0.0 0

Index Only Scan using cinema_pkey on cinema c1_2 (cost=0.28..0.31 rows=1 width=4) (never executed)

  • Index Cond: (id = v1_1.cinema_id)
  • Heap Fetches: 0
18. 0.345 7,614.187 ↑ 25.2 100 1

LockRows (cost=115,287.84..116,012.05 rows=2,519 width=128) (actual time=7,613.385..7,614.187 rows=100 loops=1)

19. 0.516 7,613.842 ↑ 25.2 100 1

Result (cost=115,287.84..115,986.86 rows=2,519 width=128) (actual time=7,613.338..7,613.842 rows=100 loops=1)

20. 137.575 7,613.326 ↑ 25.2 100 1

Sort (cost=115,287.84..115,294.13 rows=2,519 width=100) (actual time=7,613.302..7,613.326 rows=100 loops=1)

  • Sort Key: (CASE WHEN (e1.event_seating_lookup_count = 0) THEN 2 WHEN (e1.start_time < now()) THEN 3 WHEN (e1.start_time > (now() + '01:00:00'::interval)) THEN 4 ELSE 1 END), e1.start_time, e1.event_seating_asked_at
  • Sort Method: quicksort Memory: 16508kB
21. 292.124 7,475.751 ↓ 37.9 95,540 1

Nested Loop (cost=1.67..115,191.56 rows=2,519 width=100) (actual time=16.242..7,475.751 rows=95,540 loops=1)

  • Join Filter: (((to_char(timezone((t1.name)::text, now()), 'HH24'::text))::smallint < 0) OR ((to_char(timezone((t1.name)::text, now()), 'HH24'::text))::smallint > 7) OR (e1.created_at > (now() - '24:00:00'::interval)) OR (e1.start_time < (now() + '24:00:00'::interval)))
22. 43.637 6,992.547 ↓ 33.6 95,540 1

Merge Join (cost=1.52..114,527.86 rows=2,845 width=106) (actual time=15.873..6,992.547 rows=95,540 loops=1)

  • Merge Cond: (v1.google_place_id = gp1.id)
23. 935.754 6,926.198 ↓ 33.6 95,540 1

Nested Loop Anti Join (cost=1.23..129,483.46 rows=2,845 width=100) (actual time=15.837..6,926.198 rows=95,540 loops=1)

  • Join Filter: (event_seating_lookup_queue.event_id = e1.id)
  • Rows Removed by Join Filter: 8220095
24. 40.906 5,416.688 ↓ 33.6 95,626 1

Nested Loop (cost=0.98..129,439.51 rows=2,845 width=94) (actual time=14.559..5,416.688 rows=95,626 loops=1)

  • Join Filter: (e1.consecutive_event_seating_lookup_is_unsuccessful_count < c1.maximum_consecutive_unsuccessful_event_seating_lookup_count)
25. 175.615 306.888 ↓ 8.9 1,483 1

Nested Loop (cost=0.56..2,592.28 rows=167 width=60) (actual time=8.039..306.888 rows=1,483 loops=1)

  • Join Filter: (c1.id = v1.cinema_id)
  • Rows Removed by Join Filter: 1665773
26. 6.557 6.557 ↑ 1.0 3,282 1

Index Scan using public_venue_google_place_id1_idx on venue v1 (cost=0.28..275.51 rows=3,282 width=18) (actual time=0.009..6.557 rows=3,282 loops=1)

27. 116.946 124.716 ↓ 12.1 508 3,282

Materialize (cost=0.28..249.21 rows=42 width=54) (actual time=0.002..0.038 rows=508 loops=3,282)

28. 0.720 7.770 ↓ 12.1 508 1

Nested Loop (cost=0.28..249.00 rows=42 width=54) (actual time=4.718..7.770 rows=508 loops=1)

29. 5.420 5.420 ↓ 4.1 815 1

CTE Scan on event_seating_lookup_aggregate_report eslar1 (cost=0.00..4.00 rows=200 width=48) (actual time=4.702..5.420 rows=815 loops=1)

30. 1.630 1.630 ↑ 1.0 1 815

Index Scan using cinema_pkey on cinema c1 (cost=0.28..1.21 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=815)

  • Index Cond: (id = eslar1.cinema_id)
  • Filter: ((data_aggregation_disabled_at IS NULL) AND (eslar1.active_event_seating_lookup_count < maximum_event_seating_lookup_concurrency))
  • Rows Removed by Filter: 0
31. 5,068.894 5,068.894 ↑ 2.3 64 1,483

Index Scan using event_venue_id_fuid_idx on event e1 (cost=0.42..757.73 rows=147 width=46) (actual time=0.519..3.418 rows=64 loops=1,483)

  • Index Cond: (venue_id = v1.id)
  • Filter: ((unlisted_at IS NULL) AND (event_seating_asked_at IS NOT NULL) AND (start_time > (now() - '00:15:00'::interval)) AND (event_seating_lookup_time_frame(start_time, event_seating_asked_at, consecutive_event_seating_lookup_is_unsuccessful_count) IS NOT NULL) AND ((consecutive_event_seating_lookup_is_unsuccessful_count = 0) OR ((start_time >= now()) AND (start_time <= (now() + '00:05:00'::interval)) AND ((event_seating_asked_at < (start_time - '00:05:00'::interval)) OR (event_seating_asked_at > start_time))) OR (event_seating_asked_at < (now() - ('00:00:01'::interval * (bound_power((consecutive_event_seating_lookup_is_unsuccessful_count + 1), 4, 1, 1440))::double precision)))))
  • Rows Removed by Filter: 583
32. 572.519 573.756 ↓ 86.0 86 95,626

Materialize (cost=0.25..1.28 rows=1 width=10) (actual time=0.000..0.006 rows=86 loops=95,626)

33. 1.237 1.237 ↓ 86.0 86 1

Index Scan using event_seating_lookup_queue_id_idx on event_seating_lookup_queue (cost=0.25..1.27 rows=1 width=10) (actual time=1.201..1.237 rows=86 loops=1)

  • Filter: (attempted_at IS NULL)
34. 22.712 22.712 ↑ 1.0 25,747 1

Index Scan using google_place_pkey on google_place gp1 (cost=0.29..1,239.34 rows=26,937 width=14) (actual time=0.014..22.712 rows=25,747 loops=1)

35. 191.080 191.080 ↑ 1.0 1 95,540

Index Scan using timezone_pkey on timezone t1 (cost=0.15..0.17 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=95,540)

  • Index Cond: (id = gp1.timezone_id)