explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F44n

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 2,914.113 ↑ 1.0 100 1

Limit (cost=50,636.09..50,664.84 rows=100 width=122) (actual time=2,912.700..2,914.113 rows=100 loops=1)

2.          

CTE event_seating_lookup_aggregate_report

3. 0.363 3.293 ↓ 4.1 815 1

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

4. 0.421 2.930 ↑ 1.0 815 1

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

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

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

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

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

7. 0.194 0.865 ↑ 1.0 815 1

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

8. 0.671 0.671 ↑ 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.009..0.671 rows=815 loops=1)

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

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

  • Group Key: c1_2.id
10. 0.006 0.872 ↓ 0.0 0 1

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

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

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

12. 0.001 0.866 ↓ 0.0 0 1

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

13. 0.001 0.865 ↓ 0.0 0 1

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

14. 0.864 0.864 ↓ 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=0.864..0.864 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.681 2,914.089 ↑ 100.3 100 1

LockRows (cost=50,224.84..53,107.89 rows=10,028 width=122) (actual time=2,912.699..2,914.089 rows=100 loops=1)

19. 0.769 2,913.408 ↑ 100.3 100 1

Result (cost=50,224.84..53,007.61 rows=10,028 width=122) (actual time=2,912.671..2,913.408 rows=100 loops=1)

20. 343.417 2,912.639 ↑ 100.3 100 1

Sort (cost=50,224.84..50,249.91 rows=10,028 width=94) (actual time=2,912.608..2,912.639 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: external merge Disk: 23784kB
21. 145.384 2,569.222 ↓ 22.0 220,853 1

Hash Join (cost=12.10..49,841.57 rows=10,028 width=94) (actual time=8.971..2,569.222 rows=220,853 loops=1)

  • Hash Cond: (gp1.timezone_id = t1.id)
22. 75.911 2,423.669 ↓ 22.0 220,853 1

Merge Join (cost=1.27..49,653.57 rows=10,028 width=92) (actual time=8.780..2,423.669 rows=220,853 loops=1)

  • Merge Cond: (v1.google_place_id = gp1.id)
23. 67.093 2,326.054 ↓ 22.0 220,853 1

Nested Loop (cost=0.98..55,187.97 rows=10,028 width=86) (actual time=8.759..2,326.054 rows=220,853 loops=1)

24. 170.395 293.986 ↓ 8.9 1,483 1

Nested Loop (cost=0.56..2,592.28 rows=167 width=56) (actual time=7.022..293.986 rows=1,483 loops=1)

  • Join Filter: (c1.id = v1.cinema_id)
  • Rows Removed by Join Filter: 1665773
25. 5.439 5.439 ↑ 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.013..5.439 rows=3,282 loops=1)

26. 111.458 118.152 ↓ 12.1 508 3,282

Materialize (cost=0.28..249.21 rows=42 width=50) (actual time=0.001..0.036 rows=508 loops=3,282)

27. 0.424 6.694 ↓ 12.1 508 1

Nested Loop (cost=0.28..249.00 rows=42 width=50) (actual time=2.843..6.694 rows=508 loops=1)

28. 3.825 3.825 ↓ 4.1 815 1

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

29. 2.445 2.445 ↑ 1.0 1 815

Index Scan using cinema_pkey on cinema c1 (cost=0.28..1.21 rows=1 width=14) (actual time=0.003..0.003 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
30. 1,964.975 1,964.975 ↑ 1.2 149 1,483

Index Scan using event_venue_id_fuid_idx on event e1 (cost=0.42..313.21 rows=173 width=38) (actual time=0.178..1.325 rows=149 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)))
  • Rows Removed by Filter: 499
31. 21.704 21.704 ↑ 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.011..21.704 rows=25,747 loops=1)

32. 0.073 0.169 ↑ 1.0 259 1

Hash (cost=7.59..7.59 rows=259 width=10) (actual time=0.169..0.169 rows=259 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
33. 0.096 0.096 ↑ 1.0 259 1

Seq Scan on timezone t1 (cost=0.00..7.59 rows=259 width=10) (actual time=0.013..0.096 rows=259 loops=1)

Planning time : 3.964 ms
Execution time : 2,921.197 ms