explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YtlY

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 17.657 ↓ 0.0 0 1

GroupAggregate (cost=1,567.06..1,567.57 rows=5 width=202) (actual time=17.657..17.657 rows=0 loops=1)

  • Group Key: task.starttime, task.priority, task.task, task.sch, task."interval", task.comloc_id, task.source_name, task.location_name, task.url, task.timeout, task.parent_id
2.          

CTE current_schedule

3. 0.256 4.863 ↓ 1.7 158 1

Hash Join (cost=56.34..81.71 rows=91 width=134) (actual time=1.176..4.863 rows=158 loops=1)

  • Hash Cond: (cl.competitor_id = com.id)
4. 0.257 4.559 ↓ 1.7 158 1

Hash Join (cost=54.56..76.70 rows=91 width=107) (actual time=1.112..4.559 rows=158 loops=1)

  • Hash Cond: (loc.id = cl.location_id)
5. 3.225 3.225 ↓ 1.0 879 1

Seq Scan on locations loc (cost=0.00..15.76 rows=876 width=26) (actual time=0.016..3.225 rows=879 loops=1)

6. 0.087 1.077 ↓ 1.7 158 1

Hash (cost=53.42..53.42 rows=91 width=89) (actual time=1.077..1.077 rows=158 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
7. 0.090 0.990 ↓ 1.7 158 1

Hash Join (cost=37.87..53.42 rows=91 width=89) (actual time=0.741..0.990 rows=158 loops=1)

  • Hash Cond: (sch.comloc_id = cl.id)
8. 0.106 0.764 ↓ 1.7 158 1

Hash Left Join (cost=30.91..46.21 rows=91 width=54) (actual time=0.588..0.764 rows=158 loops=1)

  • Hash Cond: (sch.id = sh.schedule_id)
  • Filter: (sh.enable OR (sh.enable IS NULL))
  • Rows Removed by Filter: 23
9. 0.105 0.105 ↑ 1.0 181 1

Seq Scan on schedule sch (cost=0.00..14.81 rows=181 width=54) (actual time=0.009..0.105 rows=181 loops=1)

10. 0.025 0.553 ↓ 1.1 80 1

Hash (cost=29.96..29.96 rows=76 width=9) (actual time=0.553..0.553 rows=80 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
11. 0.017 0.528 ↓ 1.1 80 1

Subquery Scan on sh (cost=27.36..29.96 rows=76 width=9) (actual time=0.404..0.528 rows=80 loops=1)

12. 0.066 0.511 ↓ 1.1 80 1

Unique (cost=27.36..29.20 rows=76 width=17) (actual time=0.403..0.511 rows=80 loops=1)

13. 0.271 0.445 ↓ 1.1 405 1

Sort (cost=27.36..28.28 rows=368 width=17) (actual time=0.402..0.445 rows=405 loops=1)

  • Sort Key: st_1.schedule_id, st_1.date DESC
  • Sort Method: quicksort Memory: 56kB
14. 0.174 0.174 ↓ 1.1 405 1

Seq Scan on schedule_status_histories st_1 (cost=0.00..11.68 rows=368 width=17) (actual time=0.012..0.174 rows=405 loops=1)

15. 0.072 0.136 ↓ 1.0 179 1

Hash (cost=4.76..4.76 rows=176 width=43) (actual time=0.136..0.136 rows=179 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
16. 0.064 0.064 ↓ 1.0 179 1

Seq Scan on comloc cl (cost=0.00..4.76 rows=176 width=43) (actual time=0.010..0.064 rows=179 loops=1)

17. 0.025 0.048 ↓ 1.1 39 1

Hash (cost=1.35..1.35 rows=35 width=38) (actual time=0.048..0.048 rows=39 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
18. 0.023 0.023 ↓ 1.1 39 1

Seq Scan on competitors com (cost=0.00..1.35 rows=35 width=38) (actual time=0.013..0.023 rows=39 loops=1)

19.          

CTE root_tasks

20. 0.087 10.850 ↓ 4.1 78 1

Hash Right Join (cost=418.38..420.89 rows=19 width=191) (actual time=10.746..10.850 rows=78 loops=1)

  • Hash Cond: ((scm.class_label_id = sch_1.class_label_id) AND (scm.comloc_id = sch_1.comloc_id))
21. 0.027 0.027 ↓ 1.7 158 1

CTE Scan on current_schedule scm (cost=0.00..1.82 rows=91 width=12) (actual time=0.000..0.027 rows=158 loops=1)

22. 0.059 10.736 ↓ 4.1 78 1

Hash (cost=418.09..418.09 rows=19 width=195) (actual time=10.736..10.736 rows=78 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
23. 0.121 10.677 ↓ 4.1 78 1

Hash Left Join (cost=415.91..418.09 rows=19 width=195) (actual time=10.549..10.677 rows=78 loops=1)

  • Hash Cond: (sch_1.id = tsk.schedule_id)
  • Filter: (((tsk.start_time IS NOT NULL) AND (tsk.complete IS NULL)) OR (tsk.id IS NULL) OR ((now() > sch_1.starttime) AND ((now() - tsk.start_time) > sch_1."interval")))
  • Rows Removed by Filter: 80
24. 1.206 1.206 ↓ 1.7 158 1

CTE Scan on current_schedule sch_1 (cost=0.00..1.82 rows=91 width=162) (actual time=1.178..1.206 rows=158 loops=1)

25. 0.062 9.350 ↓ 158.0 158 1

Hash (cost=415.90..415.90 rows=1 width=41) (actual time=9.350..9.350 rows=158 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
26. 0.032 9.288 ↓ 158.0 158 1

Subquery Scan on tsk (cost=414.85..415.90 rows=1 width=41) (actual time=8.239..9.288 rows=158 loops=1)

27. 0.132 9.256 ↓ 158.0 158 1

Subquery Scan on t (cost=414.85..415.89 rows=1 width=41) (actual time=8.239..9.256 rows=158 loops=1)

  • Filter: (t.rn = 1)
  • Rows Removed by Filter: 741
28. 0.799 9.124 ↓ 28.1 899 1

WindowAgg (cost=414.85..415.49 rows=32 width=49) (actual time=8.237..9.124 rows=899 loops=1)

29. 2.961 8.325 ↓ 28.1 899 1

Sort (cost=414.85..414.93 rows=32 width=41) (actual time=8.226..8.325 rows=899 loops=1)

  • Sort Key: tasks.schedule_id, tasks.start_time DESC
  • Sort Method: quicksort Memory: 95kB
30. 0.275 5.364 ↓ 28.1 899 1

Nested Loop (cost=2.46..414.05 rows=32 width=41) (actual time=3.962..5.364 rows=899 loops=1)

31. 0.151 3.983 ↓ 1.7 158 1

HashAggregate (cost=2.05..2.96 rows=91 width=8) (actual time=3.926..3.983 rows=158 loops=1)

  • Group Key: current_schedule.id
32. 3.832 3.832 ↓ 1.7 158 1

CTE Scan on current_schedule (cost=0.00..1.82 rows=91 width=8) (actual time=0.001..3.832 rows=158 loops=1)

33. 1.106 1.106 ↓ 6.0 6 158

Index Only Scan using schedule_idx on tasks (cost=0.41..4.51 rows=1 width=41) (actual time=0.004..0.007 rows=6 loops=158)

  • Index Cond: (schedule_id = current_schedule.id)
  • Filter: ((parent_id IS NULL) AND (start_time IS NOT NULL))
  • Rows Removed by Filter: 0
  • Heap Fetches: 393
34.          

CTE selected

35. 0.002 17.640 ↓ 0.0 0 1

Limit (cost=898.97..898.99 rows=5 width=187) (actual time=17.640..17.640 rows=0 loops=1)

36. 0.003 17.638 ↓ 0.0 0 1

Sort (cost=898.97..898.99 rows=5 width=187) (actual time=17.638..17.638 rows=0 loops=1)

  • Sort Key: root_tasks.starttime, root_tasks.priority
  • Sort Method: quicksort Memory: 25kB
37. 0.000 17.635 ↓ 0.0 0 1

Nested Loop Left Join (cost=735.47..898.91 rows=5 width=187) (actual time=17.635..17.635 rows=0 loops=1)

  • Join Filter: (root_tasks.comloc_id = started_tasks.comloc_id)
  • Filter: ((started_tasks.started_count IS NULL) OR (started_tasks.started_count < root_tasks."limit"))
38. 0.002 17.635 ↓ 0.0 0 1

Append (cost=0.00..158.18 rows=15 width=191) (actual time=17.634..17.635 rows=0 loops=1)

39. 10.939 10.939 ↓ 0.0 0 1

CTE Scan on root_tasks (cost=0.00..0.57 rows=2 width=191) (actual time=10.939..10.939 rows=0 loops=1)

  • Filter: (((start_time IS NULL) OR (start_time < starttime)) AND (((complete IS NULL) AND (finish_time < starttime)) OR ((complete IS NOT NULL) AND ((start_time - timeout) < starttime)) OR ((start_time IS NULL) AND (finish_time IS NULL))))
  • Rows Removed by Filter: 78
40. 0.001 6.694 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=0.41..157.52 rows=13 width=191) (actual time=6.694..6.694 rows=0 loops=1)

41. 0.039 6.693 ↓ 0.0 0 1

Nested Loop (cost=0.41..157.39 rows=13 width=234) (actual time=6.693..6.693 rows=0 loops=1)

42. 0.024 0.024 ↓ 4.1 78 1

CTE Scan on root_tasks rt (cost=0.00..0.38 rows=19 width=143) (actual time=0.000..0.024 rows=78 loops=1)

43. 0.078 6.630 ↓ 0.0 0 78

Subquery Scan on tsk_1 (cost=0.41..8.25 rows=1 width=99) (actual time=0.085..0.085 rows=0 loops=78)

  • Filter: (((rt.complete IS NULL) AND (rt.finish_time < rt.starttime)) OR ((rt.complete IS NOT NULL) AND ((tsk_1.start_time - rt.timeout) < rt.starttime)) OR ((tsk_1.start_time IS NULL) AND (rt.finish_time IS NULL)))
44. 0.000 6.552 ↓ 0.0 0 78

Limit (cost=0.41..8.22 rows=2 width=116) (actual time=0.084..0.084 rows=0 loops=78)

45. 6.552 6.552 ↓ 0.0 0 78

Index Only Scan using parent_idx on tasks tsk_2 (cost=0.41..62.88 rows=16 width=116) (actual time=0.084..0.084 rows=0 loops=78)

  • Index Cond: (parent_id = rt.task)
  • Filter: (start_time IS NULL)
  • Rows Removed by Filter: 129
  • Heap Fetches: 1,064
46. 0.000 0.000 ↓ 0.0 0

Materialize (cost=735.47..735.94 rows=19 width=16) (never executed)

47. 0.000 0.000 ↓ 0.0 0

Subquery Scan on started_tasks (cost=735.47..735.85 rows=19 width=16) (never executed)

48. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=735.47..735.66 rows=19 width=16) (never executed)

  • Group Key: rt_1.comloc_id
49. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=0.62..733.88 rows=318 width=8) (never executed)

  • Hash Cond: (tsk_3.parent_id = rt_1.task)
50. 0.000 0.000 ↓ 0.0 0

Seq Scan on tasks tsk_3 (cost=0.00..722.96 rows=1,899 width=8) (never executed)

  • Filter: ((start_time IS NOT NULL) AND (finish_time IS NULL))
51. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.38..0.38 rows=19 width=16) (never executed)

52. 0.000 0.000 ↓ 0.0 0

CTE Scan on root_tasks rt_1 (cost=0.00..0.38 rows=19 width=16) (never executed)

53. 0.011 17.654 ↓ 0.0 0 1

Sort (cost=165.47..165.50 rows=10 width=368) (actual time=17.654..17.654 rows=0 loops=1)

  • Sort Key: task.starttime, task.priority, task.task, task.sch, task."interval", task.comloc_id, task.source_name, task.location_name, task.url, task.timeout, task.parent_id
  • Sort Method: quicksort Memory: 25kB
54. 0.000 17.643 ↓ 0.0 0 1

Nested Loop Left Join (cost=164.11..165.31 rows=10 width=368) (actual time=17.643..17.643 rows=0 loops=1)

  • Join Filter: ((task.task = meta_map.task) OR (meta_map.comloc_id = task.comloc_id))
55. 17.643 17.643 ↓ 0.0 0 1

CTE Scan on selected task (cost=0.00..0.10 rows=5 width=170) (actual time=17.642..17.643 rows=0 loops=1)

56. 0.000 0.000 ↓ 0.0 0

Materialize (cost=164.11..164.36 rows=10 width=214) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Subquery Scan on meta_map (cost=164.11..164.31 rows=10 width=214) (never executed)

58. 0.000 0.000 ↓ 0.0 0

Unique (cost=164.11..164.21 rows=10 width=224) (never executed)

59. 0.000 0.000 ↓ 0.0 0

Sort (cost=164.11..164.13 rows=10 width=224) (never executed)

  • Sort Key: selected.task, mf.name, selected.comloc_id, all_md.priority DESC
60. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.31..163.94 rows=10 width=224) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.16..122.27 rows=10 width=28) (never executed)

  • Join Filter: ((all_md.task = selected.task) OR (all_md.comloc_id = selected.comloc_id))
62. 0.000 0.000 ↓ 0.0 0

CTE Scan on selected (cost=0.00..0.10 rows=5 width=16) (never executed)

63. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.16..120.89 rows=15 width=28) (never executed)

64. 0.000 0.000 ↓ 0.0 0

Subquery Scan on all_md (cost=0.16..120.82 rows=15 width=28) (never executed)

65. 0.000 0.000 ↓ 0.0 0

Append (cost=0.16..120.67 rows=15 width=28) (never executed)

66. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 1 (cost=0.16..111.28 rows=5 width=28) (never executed)

67. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=0.16..111.22 rows=5 width=24) (never executed)

  • Hash Cond: (data.task_id = task_1.task)
68. 0.000 0.000 ↓ 0.0 0

Seq Scan on data (cost=0.00..103.64 rows=1,964 width=16) (never executed)

69. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.10..0.10 rows=5 width=8) (never executed)

70. 0.000 0.000 ↓ 0.0 0

CTE Scan on selected task_1 (cost=0.00..0.10 rows=5 width=8) (never executed)

71. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2 (cost=8.99..9.31 rows=10 width=28) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Subquery Scan on comloc_md (cost=8.99..9.19 rows=10 width=24) (never executed)

73. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=8.99..9.09 rows=10 width=20) (never executed)

  • Group Key: metadata.metadata_field_id, task_2.comloc_id
74. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=0.16..8.94 rows=10 width=16) (never executed)

  • Hash Cond: (metadata.comloc_id = task_2.comloc_id)
75. 0.000 0.000 ↓ 0.0 0

Seq Scan on metadata (cost=0.00..7.40 rows=340 width=16) (never executed)

76. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.10..0.10 rows=5 width=8) (never executed)

77. 0.000 0.000 ↓ 0.0 0

CTE Scan on selected task_2 (cost=0.00..0.10 rows=5 width=8) (never executed)

78. 0.000 0.000 ↓ 0.0 0

Index Scan using metadata_fields_pkey on metadata_fields mf (cost=0.14..4.16 rows=1 width=206) (never executed)

  • Index Cond: (id = all_md.metadata_field_id)
79.          

SubPlan (for GroupAggregate)

80. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=32) (never executed)

Planning time : 4.801 ms
Execution time : 18.074 ms