explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ljtr

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 3,059.850 ↑ 1.0 100 1

Limit (cost=50,635.33..50,664.08 rows=100 width=122) (actual time=3,059.103..3,059.850 rows=100 loops=1)

2.          

CTE event_seating_lookup_aggregate_report

3. 0.271 2.815 ↓ 4.1 815 1

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

4. 0.277 2.544 ↑ 1.0 815 1

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

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

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

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

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

7. 0.215 0.864 ↑ 1.0 815 1

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

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

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

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

  • Group Key: c1_2.id
10. 0.008 0.884 ↓ 0.0 0 1

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

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

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

12. 0.001 0.875 ↓ 0.0 0 1

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

13. 0.002 0.874 ↓ 0.0 0 1

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

14. 0.872 0.872 ↓ 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.872..0.872 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.324 3,059.824 ↑ 100.3 100 1

LockRows (cost=50,224.08..53,106.84 rows=10,027 width=122) (actual time=3,059.102..3,059.824 rows=100 loops=1)

19. 0.504 3,059.500 ↑ 100.3 100 1

Result (cost=50,224.08..53,006.57 rows=10,027 width=122) (actual time=3,059.065..3,059.500 rows=100 loops=1)

20. 281.797 3,058.996 ↑ 100.3 100 1

Sort (cost=50,224.08..50,249.14 rows=10,027 width=94) (actual time=3,058.965..3,058.996 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: 37195kB
21. 142.164 2,777.199 ↓ 22.0 220,803 1

Hash Join (cost=12.10..49,840.85 rows=10,027 width=94) (actual time=7.257..2,777.199 rows=220,803 loops=1)

  • Hash Cond: (gp1.timezone_id = t1.id)
22. 73.914 2,634.905 ↓ 22.0 220,803 1

Merge Join (cost=1.27..49,652.86 rows=10,027 width=92) (actual time=7.111..2,634.905 rows=220,803 loops=1)

  • Merge Cond: (v1.google_place_id = gp1.id)
23. 63.106 2,534.905 ↓ 22.0 220,803 1

Nested Loop (cost=0.98..55,187.97 rows=10,027 width=86) (actual time=7.086..2,534.905 rows=220,803 loops=1)

24. 169.550 290.306 ↓ 8.9 1,483 1

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

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

26. 109.666 114.870 ↓ 12.1 508 3,282

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

27. 0.367 5.204 ↓ 12.1 508 1

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

28. 3.207 3.207 ↓ 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.483..3.207 rows=815 loops=1)

29. 1.630 1.630 ↑ 1.0 1 815

Index Scan using cinema_pkey on cinema c1 (cost=0.28..1.21 rows=1 width=14) (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
30. 2,181.493 2,181.493 ↑ 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.207..1.471 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. 26.086 26.086 ↑ 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..26.086 rows=25,747 loops=1)

32. 0.049 0.130 ↑ 1.0 259 1

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

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

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

Planning time : 3.081 ms
Execution time : 3,064.289 ms