explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uIun

Settings
# exclusive inclusive rows x rows loops node
1. 0.107 1,055.492 ↑ 19,096.3 7 1

Hash Join (cost=220,515.46..224,066.34 rows=133,674 width=4) (actual time=1,054.982..1,055.492 rows=7 loops=1)

  • Hash Cond: (ot1.cinema_id = c1.id)
  • Join Filter: ((ot1.venue_id IS NULL) OR (((tr1.queued_task_count + tr1.running_task_count) + ot1.task_number) < c1.maximum_cinema_data_task_concurrency) OR ot1.cinema_data_task_is_failing)
  • Rows Removed by Join Filter: 194
2.          

CTE outstanding_cinema_data_task

3. 0.111 1,051.220 ↑ 43.0 201 1

Hash Anti Join (cost=130,147.58..217,783.49 rows=8,651 width=21) (actual time=857.983..1,051.220 rows=201 loops=1)

  • Hash Cond: (cdt1.id = cdtq1.cinema_data_task_id)
4. 0.178 1,050.870 ↑ 43.0 201 1

Nested Loop Anti Join (cost=130,132.21..217,658.90 rows=8,651 width=21) (actual time=857.719..1,050.870 rows=201 loops=1)

5. 0.172 1,050.011 ↑ 38.2 227 1

Nested Loop Left Join (cost=130,131.78..199,002.92 rows=8,675 width=25) (actual time=857.705..1,050.011 rows=227 loops=1)

  • Filter: ((cdte2.id IS NULL) OR cdte2.execution_is_successful)
6. 111.927 1,049.158 ↑ 42.0 227 1

Hash Left Join (cost=130,131.36..179,305.50 rows=9,528 width=29) (actual time=857.684..1,049.158 rows=227 loops=1)

  • Hash Cond: (cdt1.last_cinema_data_task_execution_id = cdte1.id)
  • Filter: ((cdt1.venue_id IS NULL) OR (SubPlan 1) OR (NOT cdte1.execution_is_successful))
  • Rows Removed by Filter: 383
7. 79.986 79.986 ↑ 28.6 610 1

Index Scan using cinema_data_task_last_successful_cinema_data_task_execution_id_ on cinema_data_task cdt1 (cost=0.42..44,848.83 rows=17,423 width=32) (actual time=0.058..79.986 rows=610 loops=1)

  • Index Cond: (last_successful_cinema_data_task_execution_id IS NULL)
  • Filter: ((terminated_reason IS NULL) AND (attempt_count <= maximum_execution_attempt_count) AND (last_attempted_at < (now() - '00:01:00'::interval)))
  • Rows Removed by Filter: 54567
8. 271.869 855.418 ↑ 1.0 1,030,579 1

Hash (cost=113,054.97..113,054.97 rows=1,034,097 width=5) (actual time=855.418..855.418 rows=1,030,579 loops=1)

  • Buckets: 524288 Batches: 4 Memory Usage: 14167kB
9. 583.549 583.549 ↑ 1.0 1,030,579 1

Seq Scan on cinema_data_task_execution cdte1 (cost=0.00..113,054.97 rows=1,034,097 width=5) (actual time=0.010..583.549 rows=1,030,579 loops=1)

10.          

SubPlan (forHash Left Join)

11. 0.329 1.827 ↓ 2.0 4 609

Materialize (cost=109.75..207.43 rows=2 width=4) (actual time=0.001..0.003 rows=4 loops=609)

12. 0.564 1.498 ↓ 2.5 5 1

Hash Join (cost=109.75..207.42 rows=2 width=4) (actual time=0.441..1.498 rows=5 loops=1)

  • Hash Cond: (tr1_1.venue_id = v1.id)
  • Join Filter: (c1_1.maximum_cinema_data_task_concurrency < ((tr1_1.queued_task_count + tr1_1.running_task_count) + tr1_1.failing_task_count))
  • Rows Removed by Join Filter: 6
13. 0.515 0.515 ↑ 1.0 4,624 1

Seq Scan on task_report tr1_1 (cost=0.00..80.24 rows=4,624 width=28) (actual time=0.010..0.515 rows=4,624 loops=1)

14. 0.005 0.419 ↓ 2.2 11 1

Hash (cost=109.69..109.69 rows=5 width=8) (actual time=0.419..0.419 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.006 0.414 ↓ 2.2 11 1

Nested Loop (cost=0.28..109.69 rows=5 width=8) (actual time=0.138..0.414 rows=11 loops=1)

16. 0.380 0.380 ↓ 2.0 2 1

Seq Scan on cinema c1_1 (cost=0.00..103.52 rows=1 width=8) (actual time=0.122..0.380 rows=2 loops=1)

  • Filter: (new_cinema_data_task_execution_disabled_until_at > now())
  • Rows Removed by Filter: 966
17. 0.028 0.028 ↓ 1.2 6 2

Index Scan using public_venue_cinema_id0_idx on venue v1 (cost=0.28..6.12 rows=5 width=8) (actual time=0.009..0.014 rows=6 loops=2)

  • Index Cond: (cinema_id = c1_1.id)
18. 0.681 0.681 ↑ 1.0 1 227

Index Scan using cinema_data_task_execution_pkey on cinema_data_task_execution cdte2 (cost=0.42..2.06 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=227)

  • Index Cond: (id = cdt1.parent_cinema_data_task_execution_id)
19. 0.681 0.681 ↓ 0.0 0 227

Index Scan using cinema_data_task_execution_cinema_data_task_id_idx on cinema_data_task_execution cdte1_1 (cost=0.42..2.14 rows=1 width=13) (actual time=0.003..0.003 rows=0 loops=227)

  • Index Cond: (cinema_data_task_id = cdt1.id)
  • Filter: (((NOT execution_is_successful) OR (ended_at IS NULL)) AND (((NOT execution_is_successful) AND (ended_at > ((now() - '00:01:00'::interval) - ('00:00:01'::interval * (bound_power((cdt1.attempt_count + 1), 4, 1, 1440))::double precision)))) OR (ended_at IS NULL)))
20. 0.001 0.239 ↓ 0.0 0 1

Hash (cost=15.36..15.36 rows=1 width=4) (actual time=0.239..0.239 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
21. 0.238 0.238 ↓ 0.0 0 1

Index Only Scan using cinema_data_task_queue_cinema_data_task_id_idx on cinema_data_task_queue cdtq1 (cost=0.25..15.36 rows=1 width=4) (actual time=0.238..0.238 rows=0 loops=1)

  • Heap Fetches: 653
22.          

CTE ordered_task

23. 0.200 1,051.788 ↑ 43.0 201 1

Sort (cost=1,499.10..1,520.73 rows=8,651 width=33) (actual time=1,051.773..1,051.788 rows=201 loops=1)

  • Sort Key: ocdt1.target_data, (random())
  • Sort Method: quicksort Memory: 40kB
24. 0.161 1,051.588 ↑ 43.0 201 1

WindowAgg (cost=738.74..933.38 rows=8,651 width=33) (actual time=1,051.425..1,051.588 rows=201 loops=1)

25. 0.075 1,051.427 ↑ 43.0 201 1

Sort (cost=738.74..760.36 rows=8,651 width=17) (actual time=1,051.415..1,051.427 rows=201 loops=1)

  • Sort Key: ocdt1.venue_id, ocdt1.target_data
  • Sort Method: quicksort Memory: 34kB
26. 1,051.352 1,051.352 ↑ 43.0 201 1

CTE Scan on outstanding_cinema_data_task ocdt1 (cost=0.00..173.02 rows=8,651 width=17) (actual time=857.985..1,051.352 rows=201 loops=1)

27. 0.409 1,054.757 ↑ 995.1 201 1

Merge Right Join (cost=1,100.46..4,123.75 rows=200,011 width=37) (actual time=1,054.346..1,054.757 rows=201 loops=1)

  • Merge Cond: (tr1.venue_id = ot1.venue_id)
28. 1.588 2.439 ↑ 1.5 3,001 1

Sort (cost=361.72..373.28 rows=4,624 width=20) (actual time=1.978..2.439 rows=3,001 loops=1)

  • Sort Key: tr1.venue_id
  • Sort Method: quicksort Memory: 554kB
29. 0.851 0.851 ↑ 1.0 4,624 1

Seq Scan on task_report tr1 (cost=0.00..80.24 rows=4,624 width=20) (actual time=0.009..0.851 rows=4,624 loops=1)

30. 0.054 1,051.909 ↑ 43.0 201 1

Sort (cost=738.74..760.36 rows=8,651 width=21) (actual time=1,051.892..1,051.909 rows=201 loops=1)

  • Sort Key: ot1.venue_id
  • Sort Method: quicksort Memory: 40kB
31. 1,051.855 1,051.855 ↑ 43.0 201 1

CTE Scan on ordered_task ot1 (cost=0.00..173.02 rows=8,651 width=21) (actual time=1,051.776..1,051.855 rows=201 loops=1)

32. 0.147 0.628 ↑ 1.0 968 1

Hash (cost=98.68..98.68 rows=968 width=8) (actual time=0.628..0.628 rows=968 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
33. 0.481 0.481 ↑ 1.0 968 1

Seq Scan on cinema c1 (cost=0.00..98.68 rows=968 width=8) (actual time=0.008..0.481 rows=968 loops=1)

Planning time : 2.169 ms
Execution time : 1,055.740 ms