explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HhsP

Settings
# exclusive inclusive rows x rows loops node
1. 0.063 1.400 ↑ 2.9 70 1

Sort (cost=256.50..257.00 rows=200 width=172) (actual time=1.394..1.400 rows=70 loops=1)

  • Sort Key: (CASE WHEN ((b.step)::text = 'LOADING_LINESTRINGS'::text) THEN 1 WHEN ((b.step)::text = 'CONVERTING_LINESTRINGS'::text) THEN 2 WHEN ((b.step)::text = 'DOWNLOADING_STREETVIEW_IMAGES'::text) THEN 3 WHEN ((b.step)::text = 'RUNNING_INFERENCE'::text) THEN 4 WHEN ((b.step)::text = 'AUTOROUTING'::text) THEN 5 WHEN ((b.step)::text = 'COST_CALCULATION_CLUSTERS'::text) THEN 6 WHEN ((b.step)::text = 'COST_CALCULATION_OPPORTUNITY'::text) THEN 7 ELSE 8 END), b.bucket_min, b.bucket_max
  • Sort Method: quicksort Memory: 37kB
2.          

CTE cfg

3. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

4.          

CTE jss_global_range_per_step

5. 0.113 0.450 ↓ 1.4 7 1

GroupAggregate (cost=106.79..106.99 rows=5 width=70) (actual time=0.396..0.450 rows=7 loops=1)

  • Group Key: jss.step
6. 0.037 0.337 ↓ 12.4 62 1

Sort (cost=106.79..106.80 rows=5 width=46) (actual time=0.335..0.337 rows=62 loops=1)

  • Sort Key: jss.step
  • Sort Method: quicksort Memory: 31kB
7. 0.004 0.300 ↓ 12.4 62 1

Nested Loop (cost=103.04..106.73 rows=5 width=46) (actual time=0.268..0.300 rows=62 loops=1)

8. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on cfg c (cost=0.00..0.02 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

9. 0.030 0.295 ↓ 12.4 62 1

Hash Join (cost=103.04..106.66 rows=5 width=46) (actual time=0.266..0.295 rows=62 loops=1)

  • Hash Cond: ((jss.job_id)::text = (jd.job_id)::text)
10. 0.012 0.012 ↑ 1.5 73 1

Seq Scan on job_step_stats jss (cost=0.00..3.16 rows=110 width=73) (actual time=0.001..0.012 rows=73 loops=1)

  • Filter: (("timestamp" IS NOT NULL) AND (end_timestamp IS NOT NULL))
  • Rows Removed by Filter: 2
11. 0.009 0.253 ↓ 1.5 38 1

Hash (cost=102.72..102.72 rows=25 width=43) (actual time=0.253..0.253 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
12. 0.244 0.244 ↓ 1.5 38 1

Seq Scan on job_details jd (cost=0.00..102.72 rows=25 width=43) (actual time=0.018..0.244 rows=38 loops=1)

  • Filter: (("timestamp" IS NOT NULL) AND (end_timestamp IS NOT NULL) AND ((status)::text <> ALL ('{FAILED,CANCELLED}'::text[])))
  • Rows Removed by Filter: 501
13.          

CTE jss_data

14. 0.016 0.742 ↓ 28.0 28 1

Nested Loop (cost=0.44..16.96 rows=1 width=81) (actual time=0.503..0.742 rows=28 loops=1)

15. 0.033 0.726 ↓ 28.0 28 1

Nested Loop (cost=0.44..16.93 rows=1 width=81) (actual time=0.500..0.726 rows=28 loops=1)

  • Join Filter: (((jd_1.end_timestamp - jd_1."timestamp") >= r.min_duration) AND ((jd_1.end_timestamp - jd_1."timestamp") <= r.max_duration))
  • Rows Removed by Join Filter: 23
16. 0.041 0.522 ↓ 14.2 57 1

Hash Join (cost=0.16..4.32 rows=4 width=113) (actual time=0.479..0.522 rows=57 loops=1)

  • Hash Cond: ((jss_1.step)::text = (r.step)::text)
  • Join Filter: ((jss_1.size >= r.min_size) AND (jss_1.size <= r.max_size))
  • Rows Removed by Join Filter: 16
17. 0.020 0.020 ↑ 1.5 73 1

Seq Scan on job_step_stats jss_1 (cost=0.00..3.16 rows=110 width=81) (actual time=0.005..0.020 rows=73 loops=1)

  • Filter: (("timestamp" IS NOT NULL) AND (end_timestamp IS NOT NULL))
  • Rows Removed by Filter: 2
18. 0.004 0.461 ↓ 1.4 7 1

Hash (cost=0.10..0.10 rows=5 width=80) (actual time=0.461..0.461 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.457 0.457 ↓ 1.4 7 1

CTE Scan on jss_global_range_per_step r (cost=0.00..0.10 rows=5 width=80) (actual time=0.397..0.457 rows=7 loops=1)

20. 0.171 0.171 ↑ 1.0 1 57

Index Scan using job_details_pk on job_details jd_1 (cost=0.28..3.13 rows=1 width=51) (actual time=0.003..0.003 rows=1 loops=57)

  • Index Cond: ((job_id)::text = (jss_1.job_id)::text)
  • Filter: (("timestamp" IS NOT NULL) AND (end_timestamp IS NOT NULL) AND ((status)::text <> ALL ('{FAILED,CANCELLED}'::text[])))
  • Rows Removed by Filter: 0
21. 0.000 0.000 ↑ 1.0 1 28

CTE Scan on cfg c_1 (cost=0.00..0.02 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=28)

22.          

CTE jss_range_per_step

23. 0.009 0.808 ↓ 7.0 7 1

Nested Loop (cost=0.07..0.14 rows=1 width=80) (actual time=0.801..0.808 rows=7 loops=1)

24. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on cfg c_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

25. 0.034 0.798 ↓ 7.0 7 1

HashAggregate (cost=0.07..0.08 rows=1 width=84) (actual time=0.794..0.798 rows=7 loops=1)

  • Group Key: d_1.step, c_3.num_buckets
26. 0.012 0.764 ↓ 28.0 28 1

Nested Loop (cost=0.00..0.05 rows=1 width=52) (actual time=0.505..0.764 rows=28 loops=1)

27. 0.752 0.752 ↓ 28.0 28 1

CTE Scan on jss_data d_1 (cost=0.00..0.02 rows=1 width=48) (actual time=0.503..0.752 rows=28 loops=1)

28. 0.000 0.000 ↑ 1.0 1 28

CTE Scan on cfg c_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=28)

29.          

CTE jss_buckets_per_step

30. 0.049 0.860 ↑ 14.3 70 1

Nested Loop (cost=0.00..5.07 rows=1,000 width=64) (actual time=0.814..0.860 rows=70 loops=1)

31. 0.811 0.811 ↓ 7.0 7 1

CTE Scan on jss_range_per_step r_1 (cost=0.00..0.02 rows=1 width=64) (actual time=0.802..0.811 rows=7 loops=1)

32. 0.000 0.000 ↑ 1.0 1 7

CTE Scan on cfg c_4 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=7)

33. 0.079 1.337 ↑ 2.9 70 1

WindowAgg (cost=73.69..119.69 rows=200 width=172) (actual time=1.106..1.337 rows=70 loops=1)

34. 0.225 1.258 ↑ 2.9 70 1

GroupAggregate (cost=73.69..110.19 rows=200 width=136) (actual time=1.056..1.258 rows=70 loops=1)

  • Group Key: b.step, b.bucket_min, b.bucket_max
35. 0.070 1.033 ↑ 13.3 75 1

Sort (cost=73.69..76.19 rows=1,000 width=120) (actual time=1.024..1.033 rows=75 loops=1)

  • Sort Key: b.step, b.bucket_min, b.bucket_max
  • Sort Method: quicksort Memory: 38kB
36. 0.072 0.963 ↑ 13.3 75 1

Hash Left Join (cost=0.03..23.86 rows=1,000 width=120) (actual time=0.844..0.963 rows=75 loops=1)

  • Hash Cond: ((b.step)::text = (d.step)::text)
  • Join Filter: ((d.duration >= b.bucket_min) AND (d.duration <= b.bucket_max))
  • Rows Removed by Join Filter: 252
37. 0.879 0.879 ↑ 14.3 70 1

CTE Scan on jss_buckets_per_step b (cost=0.00..20.00 rows=1,000 width=64) (actual time=0.816..0.879 rows=70 loops=1)

38. 0.007 0.012 ↓ 28.0 28 1

Hash (cost=0.02..0.02 rows=1 width=88) (actual time=0.012..0.012 rows=28 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
39. 0.005 0.005 ↓ 28.0 28 1

CTE Scan on jss_data d (cost=0.00..0.02 rows=1 width=88) (actual time=0.000..0.005 rows=28 loops=1)

Planning time : 1.847 ms