explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1DKu

Settings
# exclusive inclusive rows x rows loops node
1. 0.047 14.155 ↓ 44.0 44 1

Sort (cost=371.51..371.52 rows=1 width=161) (actual time=14.149..14.155 rows=44 loops=1)

  • Sort Key: (sum(1)) DESC
  • Sort Method: quicksort Memory: 35kB
2.          

CTE ttt

3. 0.053 13.478 ↓ 44.0 44 1

Sort (cost=371.42..371.42 rows=1 width=157) (actual time=13.472..13.478 rows=44 loops=1)

  • Sort Key: tasks.start_time DESC
  • Sort Method: quicksort Memory: 36kB
4. 0.480 13.425 ↓ 44.0 44 1

GroupAggregate (cost=371.34..371.41 rows=1 width=157) (actual time=12.887..13.425 rows=44 loops=1)

  • Group Key: competitors.name, locations.name, tasks.id
5. 1.139 12.945 ↓ 664.0 664 1

Sort (cost=371.34..371.34 rows=1 width=85) (actual time=12.856..12.945 rows=664 loops=1)

  • Sort Key: competitors.name, locations.name, tasks.id
  • Sort Method: quicksort Memory: 118kB
6. 0.300 11.806 ↓ 664.0 664 1

Nested Loop (cost=323.10..371.33 rows=1 width=85) (actual time=9.557..11.806 rows=664 loops=1)

7. 0.064 10.106 ↓ 50.0 50 1

Hash Join (cost=322.82..345.08 rows=1 width=61) (actual time=9.499..10.106 rows=50 loops=1)

  • Hash Cond: (tasks.schedule_id = schedule.id)
8. 0.579 0.974 ↓ 8.3 50 1

Bitmap Heap Scan on tasks (cost=66.53..88.76 rows=6 width=33) (actual time=0.420..0.974 rows=50 loops=1)

  • Recheck Cond: ((start_time >= '2020-02-12 00:00:00+10'::timestamp with time zone) AND (start_time <= '2020-02-12 23:59:59+10'::timestamp with time zone) AND (parent_id IS NULL))
  • Heap Blocks: exact=33
9. 0.013 0.395 ↓ 0.0 0 1

BitmapAnd (cost=66.53..66.53 rows=6 width=0) (actual time=0.394..0.395 rows=0 loops=1)

10. 0.170 0.170 ↓ 3.8 339 1

Bitmap Index Scan on st_idx (cost=0.00..21.31 rows=90 width=0) (actual time=0.170..0.170 rows=339 loops=1)

  • Index Cond: ((start_time >= '2020-02-12 00:00:00+10'::timestamp with time zone) AND (start_time <= '2020-02-12 23:59:59+10'::timestamp with time zone))
11. 0.212 0.212 ↑ 1.1 952 1

Bitmap Index Scan on url_id (cost=0.00..44.96 rows=1,055 width=0) (actual time=0.212..0.212 rows=952 loops=1)

12. 0.051 9.068 ↑ 1.0 181 1

Hash (cost=254.03..254.03 rows=181 width=44) (actual time=9.067..9.068 rows=181 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
13. 0.261 9.017 ↑ 1.0 181 1

Sort (cost=251.76..252.22 rows=181 width=44) (actual time=8.990..9.017 rows=181 loops=1)

  • Sort Key: competitors.name
  • Sort Method: quicksort Memory: 42kB
14. 0.118 8.756 ↑ 1.0 181 1

Hash Join (cost=48.98..244.98 rows=181 width=44) (actual time=4.863..8.756 rows=181 loops=1)

  • Hash Cond: (comloc.location_id = locations.id)
15. 0.105 7.934 ↑ 1.0 181 1

Hash Join (cost=22.27..217.79 rows=181 width=26) (actual time=4.150..7.934 rows=181 loops=1)

  • Hash Cond: (comloc.competitor_id = competitors.id)
16. 0.123 7.795 ↑ 1.0 181 1

Hash Join (cost=20.48..215.47 rows=181 width=16) (actual time=4.103..7.795 rows=181 loops=1)

  • Hash Cond: (schedule.comloc_id = comloc.id)
17. 1.810 7.511 ↑ 1.0 181 1

Nested Loop Left Join (cost=13.52..208.03 rows=181 width=16) (actual time=3.931..7.511 rows=181 loops=1)

  • Join Filter: (schedule.id = his.schedule_id)
  • Rows Removed by Join Filter: 10260
18. 0.271 0.271 ↑ 1.0 181 1

Seq Scan on schedule (cost=0.00..14.81 rows=181 width=16) (actual time=0.017..0.271 rows=181 loops=1)

19. 1.561 5.430 ↓ 57.0 57 181

Materialize (cost=13.52..190.50 rows=1 width=8) (actual time=0.006..0.030 rows=57 loops=181)

20. 1.862 3.869 ↓ 57.0 57 1

Nested Loop (cost=13.52..190.50 rows=1 width=8) (actual time=0.976..3.869 rows=57 loops=1)

  • Join Filter: (((his.schedule_id = schedule_status_histories.schedule_id) AND (his.date = (max(schedule_status_histories.date))) AND his.enable) OR (his.enable IS NULL))
  • Rows Removed by Join Filter: 9943
21. 0.132 0.132 ↓ 1.0 125 1

Seq Scan on schedule_status_histories his (cost=0.00..11.68 rows=123 width=17) (actual time=0.023..0.132 rows=125 loops=1)

  • Filter: (enable OR (enable IS NULL))
  • Rows Removed by Filter: 280
22. 1.381 1.875 ↓ 1.1 80 125

Materialize (cost=13.52..15.42 rows=76 width=16) (actual time=0.004..0.015 rows=80 loops=125)

23. 0.234 0.494 ↓ 1.1 80 1

HashAggregate (cost=13.52..14.28 rows=76 width=16) (actual time=0.468..0.494 rows=80 loops=1)

  • Group Key: schedule_status_histories.schedule_id
24. 0.260 0.260 ↓ 1.1 405 1

Seq Scan on schedule_status_histories (cost=0.00..11.68 rows=368 width=16) (actual time=0.006..0.260 rows=405 loops=1)

25. 0.067 0.161 ↓ 1.0 179 1

Hash (cost=4.76..4.76 rows=176 width=16) (actual time=0.161..0.161 rows=179 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
26. 0.094 0.094 ↓ 1.0 179 1

Seq Scan on comloc (cost=0.00..4.76 rows=176 width=16) (actual time=0.011..0.094 rows=179 loops=1)

27. 0.015 0.034 ↓ 1.1 39 1

Hash (cost=1.35..1.35 rows=35 width=18) (actual time=0.034..0.034 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
28. 0.019 0.019 ↓ 1.1 39 1

Seq Scan on competitors (cost=0.00..1.35 rows=35 width=18) (actual time=0.009..0.019 rows=39 loops=1)

29. 0.347 0.704 ↓ 1.0 879 1

Hash (cost=15.76..15.76 rows=876 width=26) (actual time=0.703..0.704 rows=879 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 60kB
30. 0.357 0.357 ↓ 1.0 879 1

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

31. 1.400 1.400 ↓ 1.2 13 50

Index Scan using task_id_class_label on metrics (cost=0.29..26.13 rows=11 width=32) (actual time=0.005..0.028 rows=13 loops=50)

  • Index Cond: (task_id = tasks.id)
32. 0.227 14.108 ↓ 44.0 44 1

Nested Loop Left Join (cost=0.03..0.08 rows=1 width=161) (actual time=13.566..14.108 rows=44 loops=1)

  • Join Filter: (((ttt.competitor)::text = (ttt_1.competitor)::text) AND ((ttt.city)::text = (ttt_1.city)::text))
  • Rows Removed by Join Filter: 946
33. 13.485 13.485 ↓ 44.0 44 1

CTE Scan on ttt (cost=0.00..0.02 rows=1 width=153) (actual time=13.475..13.485 rows=44 loops=1)

34. 0.365 0.396 ↓ 22.0 22 44

HashAggregate (cost=0.03..0.04 rows=1 width=72) (actual time=0.002..0.009 rows=22 loops=44)

  • Group Key: ttt_1.competitor, ttt_1.city
35. 0.031 0.031 ↓ 44.0 44 1

CTE Scan on ttt ttt_1 (cost=0.00..0.02 rows=1 width=64) (actual time=0.001..0.031 rows=44 loops=1)

Planning time : 3.733 ms
Execution time : 14.453 ms