explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Jh7

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=1,060,120.75..1,060,797.50 rows=270,700 width=1,552) (actual rows= loops=)

  • Sort Key: jaa.job_id, (COALESCE(to_char(jaa_step2.created_time, 'YYYY-MM-DD HH24:MI:SS.MS'::text), to_char(jaa.created_time, 'YYYY-MM-DD HH24:MI:SS.MS'::text), ''::text))
2. 0.000 0.000 ↓ 0.0

Unique (cost=646,102.28..668,435.03 rows=270,700 width=1,552) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Sort (cost=646,102.28..646,779.03 rows=270,700 width=1,552) (actual rows= loops=)

  • Sort Key: jaa.job_id, jaa.step, jaa_step2.step, jaa_step2.node_score, jaa_step3.step, jaa_step3.status, jaan.node_id, jaa_step2.node_id, jaa_step3.node_id, (COALESCE(to_char(jaa_step2.created_time, 'YYYY-MM-DD HH24:MI:SS.MS'::text), to_char(jaa.created_time, 'YYYY-MM-DD HH24:MI:SS.MS'::text), ''::text)), (COALESCE(to_char(jaa_step3.created_time, 'YYYY-MM-DD HH24:MI:SS.MS'::text), ''::text)), (1), (CASE WHEN (((jaan.node_id)::text = (jaa_step2.node_id)::text) AND ((jaa_step3.node_id)::text = (jaa_step2.node_id)::text)) THEN 1 ELSE 0 END), njr.name, njr.instance_count, njr.component_grade, njr.job_type, njr.run_type, njr.port, njr.network_quality, njr.data_cap, njr.bandwidth_cap, njr.cores, njr.mem_cap, njr.storage_cap, njr.city, njr.country, jaa_step3.cores_available, jaa_step3.ram_available, jaa_step3.storage_available, jaa_step3.bandwidth_available, jaa_step3.data_available
4. 0.000 0.000 ↓ 0.0

Append (cost=209,789.15..257,123.57 rows=270,700 width=1,552) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Unique (cost=209,789.15..229,003.81 rows=247,931 width=434) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Sort (cost=209,789.15..210,408.98 rows=247,931 width=434) (actual rows= loops=)

  • Sort Key: jaa.job_id, (COALESCE(to_char(jaa_step2.created_time, 'YYYY-MM-DD HH24:MI:SS.MS'::text), to_char(jaa.created_time, 'YYYY-MM-DD HH24:MI:SS.MS'::text), ''::text)), jaa_step2.step, jaa_step2.node_score, jaa_step3.step, jaa_step3.status, jaan.node_id, jaa_step2.node_id, jaa_step3.node_id, (COALESCE(to_char(jaa_step3.created_time, 'YYYY-MM-DD HH24:MI:SS.MS'::text), ''::text)), (CASE WHEN (((jaan.node_id)::text = (jaa_step2.node_id)::text) AND ((jaa_step3.node_id)::text = (jaa_step2.node_id)::text)) THEN 1 ELSE 0 END), njr.name, njr.instance_count, njr.component_grade, njr.job_type, njr.run_type, njr.port, njr.network_quality, njr.data_cap, njr.bandwidth_cap, njr.cores, njr.mem_cap, njr.storage_cap, njr.city, njr.country, jaa_step3.cores_available, jaa_step3.ram_available, jaa_step3.storage_available, jaa_step3.bandwidth_available, jaa_step3.data_available
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=18,509.97..89,274.06 rows=247,931 width=434) (actual rows= loops=)

  • Hash Cond: ((jaa.job_id)::text = (jaa_step3.job_id)::text)
  • Join Filter: ((date_part('epoch'::text, (jaa_step3.created_time - jaa_step2.created_time)) < '300'::double precision) AND (date_part('epoch'::text, (jaa_step3.created_time - jaa_step2.created_time)) > '0'::double precision))
8. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=13,629.09..18,035.91 rows=4,660 width=260) (actual rows= loops=)

  • Merge Cond: (((jaa.job_id)::text = (jaa_step2.job_id)::text) AND ((jaan.node_id)::text = (jaa_step2.node_id)::text))
  • Join Filter: ((jaa.created_time < jaa_step2.created_time) AND (date_part('epoch'::text, (jaa_step2.created_time - jaa.created_time)) < '300'::double precision) AND (date_part('epoch'::text, (jaa_step2.created_time - jaa.created_time)) > '0'::double precision))
  • Hash Cond: ((jaa_3.job_id)::text = (jaa_4.job_id)::text)
  • Join Filter: ((date_part('epoch'::text, (jaa_3.created_time - jaa_4.created_time)) < '300'::double precision) AND (date_part('epoch'::text, (jaa_3.created_time - jaa_4.created_time)) > '0'::double precision))
9. 0.000 0.000 ↓ 0.0

Sort (cost=4,173.86..4,175.18 rows=528 width=183) (actual rows= loops=)

  • Sort Key: jaa.job_id, jaan.node_id
10. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,134.92..4,149.99 rows=528 width=183) (actual rows= loops=)

  • Hash Cond: ((jaa.job_id)::text = (njr.job_id)::text)
11. 0.000 0.000 ↓ 0.0

Sort (cost=4,017.27..4,018.58 rows=522 width=355) (actual rows= loops=)

  • Sort Key: jaa.job_id, jaa.created_time, ((jaan.node_priority)::integer)
12. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,428.57..3,993.71 rows=522 width=355) (actual rows= loops=)

  • Hash Cond: (jaan.job_allocation_audit = jaa.id)
13. 0.000 0.000 ↓ 0.0

Seq Scan on job_allocation_audit_node jaan (cost=0.00..1,368.25 rows=50,625 width=55) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash (cost=2,422.04..2,422.04 rows=522 width=84) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on job_allocation_audit jaa (cost=0.00..2,422.04 rows=522 width=84) (actual rows= loops=)

  • Filter: ((created_time > '2019-01-16 05:00:00.001988'::timestamp without time zone) AND (created_time < '2019-01-17 05:00:00.002013'::timestamp without time zone) AND ((step)::text = 'Resource Filtered'::text))
16. 0.000 0.000 ↓ 0.0

Hash (cost=104.51..104.51 rows=1,051 width=115) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on new_job_requests njr (cost=0.00..104.51 rows=1,051 width=115) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Sort (cost=9,455.23..9,497.31 rows=16,831 width=113) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=8,770.93..14,848.47 rows=22,769 width=456) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Sort (cost=4,626.13..4,651.61 rows=10,190 width=355) (actual rows= loops=)

  • Sort Key: jaa_3.job_id, jaa_3.created_time, ((jaan_3.node_priority)::integer)
21. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,251.97..3,947.74 rows=10,190 width=355) (actual rows= loops=)

  • Hash Cond: (jaan_3.job_allocation_audit = jaa_3.id)
22. 0.000 0.000 ↓ 0.0

Seq Scan on job_allocation_audit_node jaan_3 (cost=0.00..1,368.25 rows=50,625 width=75) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=2,124.60..2,124.60 rows=10,190 width=84) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on job_allocation_audit jaa_3 (cost=0.00..2,124.60 rows=10,190 width=84) (actual rows= loops=)

  • Filter: ((step)::text = 'Nomad allocated node'::text)
25. 0.000 0.000 ↓ 0.0

Hash (cost=4,139.45..4,139.45 rows=428 width=183) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash Join (cost=4,127.24..4,139.45 rows=428 width=183) (actual rows= loops=)

  • Hash Cond: ((jaa_4.job_id)::text = (njr_1.job_id)::text)
27. 0.000 0.000 ↓ 0.0

Sort (cost=4,009.59..4,010.65 rows=423 width=255) (actual rows= loops=)

  • Sort Key: jaa_4.job_id, jaa_4.created_time, ((jaan_4.node_priority)::integer)
28. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,427.33..3,991.14 rows=423 width=255) (actual rows= loops=)

  • Hash Cond: (jaan_4.job_allocation_audit = jaa_4.id)
29. 0.000 0.000 ↓ 0.0

Seq Scan on job_allocation_audit_node jaan_4 (cost=0.00..1,368.25 rows=50,625 width=18) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash (cost=2,422.04..2,422.04 rows=423 width=121) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Seq Scan on job_allocation_audit jaa_4 (cost=0.00..2,422.04 rows=423 width=121) (actual rows= loops=)

  • Filter: ((created_time > '2019-01-16 05:00:00.001988'::timestamp without time zone) AND (created_time < '2019-01-17 05:00:00.002013'::timestamp without time zone) AND ((step)::text = 'No nodes selected. Fallback Called'::text))
32. 0.000 0.000 ↓ 0.0

Hash (cost=104.51..104.51 rows=1,051 width=115) (actual rows= loops=)

  • -> Seq Scan on new_job_requests njr_1 (cost=0.00..104.51 rows=1051 width=115)Sort Key: jaa_step2.job_id, jaa_step2.node_id
33. 0.000 0.000 ↓ 0.0

Subquery Scan on jaa_step2 (cost=8,063.40..8,273.79 rows=16,831 width=113) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Sort (cost=8,063.40..8,105.48 rows=16,831 width=355) (actual rows= loops=)

  • Sort Key: jaa_1.job_id, jaa_1.created_time, ((jaan_1.node_priority)::integer)
35. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,334.99..4,120.47 rows=16,831 width=355) (actual rows= loops=)

  • Hash Cond: (jaan_1.job_allocation_audit = jaa_1.id)
36. 0.000 0.000 ↓ 0.0

Seq Scan on job_allocation_audit_node jaan_1 (cost=0.00..1,368.25 rows=50,625 width=63) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash (cost=2,124.60..2,124.60 rows=16,831 width=84) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on job_allocation_audit jaa_1 (cost=0.00..2,124.60 rows=16,831 width=84) (actual rows= loops=)

  • Filter: ((step)::text = 'Filtered By Node Score'::text)
39. 0.000 0.000 ↓ 0.0

Hash (cost=4,753.51..4,753.51 rows=10,190 width=162) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Subquery Scan on jaa_step3 (cost=4,626.13..4,753.51 rows=10,190 width=162) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Sort (cost=4,626.13..4,651.61 rows=10,190 width=174) (actual rows= loops=)

  • Sort Key: jaa_2.job_id, jaa_2.created_time, ((jaan_2.node_priority)::integer)
42. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,251.97..3,947.74 rows=10,190 width=174) (actual rows= loops=)

  • Hash Cond: (jaan_2.job_allocation_audit = jaa_2.id)
43. 0.000 0.000 ↓ 0.0

Seq Scan on job_allocation_audit_node jaan_2 (cost=0.00..1,368.25 rows=50,625 width=75) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash (cost=2,124.60..2,124.60 rows=10,190 width=121) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on job_allocation_audit jaa_2 (cost=0.00..2,124.60 rows=10,190 width=121) (actual rows= loops=)

  • Filter: ((step)::text = 'Nomad allocated node'::text)
46. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=21,168.86..23,161.14 rows=22,769 width=432) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Unique (cost=21,168.86..22,648.84 rows=22,769 width=456) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Sort (cost=21,168.86..21,225.78 rows=22,769 width=456) (actual rows= loops=)

  • Sort Key: jaa_4.job_id, (COALESCE(to_char(jaa_4.created_time, 'YYYY-MM-DD HH24:MI:SS.MS'::text), ''::text)), jaa_3.step, jaa_4.status, jaan_3.node_id, (COALESCE(to_char(jaa_3.created_time, 'YYYY-MM-DD HH24:MI:SS.MS'::text), ''::text)), njr_1.name, njr_1.instance_count, njr_1.component_grade, njr_1.job_type, njr_1.run_type, njr_1.port, njr_1.network_quality, njr_1.data_cap, njr_1.bandwidth_cap, njr_1.cores, njr_1.mem_cap, njr_1.storage_cap, njr_1.city, njr_1.country, jaan_3.cores_available, jaan_3.ram_available, jaan_3.storage_available, jaan_3.bandwidth_available, jaan_3.data_available