explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PsNZ

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

Nested Loop (cost=241,378.50..2,370,984.52 rows=17 width=230) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Nested Loop (cost=241,378.07..265,213.52 rows=5 width=148) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=241,377.93..265,212.64 rows=5 width=152) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Hash Join (cost=241,377.50..265,209.97 rows=5 width=152) (actual rows= loops=)

  • Hash Cond: (uca_1.cloud_type = cloud_types.cloud_type)
5. 0.000 0.000 ↓ 0.0

Hash Join (cost=241,376.32..265,208.11 rows=167 width=116) (actual rows= loops=)

  • Hash Cond: (ct.group_id = cloud_groups.id)
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=241,375.21..265,180.34 rows=6,663 width=52) (actual rows= loops=)

  • Hash Cond: (uca_1.vendor_id = v.vendor_id)
7. 0.000 0.000 ↓ 0.0

Unique (cost=241,374.12..260,531.51 rows=333,172 width=340) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Sort (cost=241,374.12..242,207.05 rows=333,172 width=340) (actual rows= loops=)

  • Sort Key: dj.job_id, uca_1.vendor_id, (CASE WHEN CASE WHEN (bj.benchmark_id IS NULL) THEN false ELSE true END THEN br.name WHEN CASE WHEN (ad.deployment_id IS NULL) THEN false ELSE true END THEN ad.name ELSE js.job_name END), (CASE WHEN (ad.deployment_id IS NOT NULL) THEN d_at.template_id WHEN (bj.benchmark_id IS NOT NULL) THEN b_at.template_id ELSE at.template_id END), (CASE WHEN (ad.deployment_id IS NOT NULL) THEN d_at.name WHEN (bj.benchmark_id IS NOT NULL) THEN b_at.name ELSE at.name END), dj.version, js.status, js.start_time, js.end_time, uca_1.cloud_type, ct.name, ca.display_name, js.user_id, (NULL::character varying), dj.phase_id, dj.memory_size, dj.num_of_cpus, dj.local_storage_size, ct.group_id, uca.cloud_account_id, dj.parent_id, dj.service_id
9. 0.000 0.000 ↓ 0.0

Append (cost=11,327.31..106,043.40 rows=333,172 width=340) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Hash Join (cost=11,327.31..102,583.37 rows=333,068 width=275) (actual rows= loops=)

  • Hash Cond: (uca.cloud_account_id = ca.cloud_account_id)
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=11,326.22..101,273.69 rows=333,068 width=290) (actual rows= loops=)

  • Hash Cond: (dj.job_id = dej.job_id)
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,987.66..70,807.99 rows=333,068 width=230) (actual rows= loops=)

  • Hash Cond: (dj.user_cloud_account_id = uca.account_id)
13. 0.000 0.000 ↓ 0.0

Hash Join (cost=3,880.97..66,121.62 rows=333,068 width=242) (actual rows= loops=)

  • Hash Cond: (dj.source_template_id = at.template_id)
14. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,859.54..59,520.51 rows=333,068 width=231) (actual rows= loops=)

  • Hash Cond: (dj.user_cloud_account_id = uca_1.account_id)
15. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1,707.80..54,789.08 rows=333,068 width=166) (actual rows= loops=)

  • Merge Cond: (dj.job_id = bj.job_id)
16. 0.000 0.000 ↓ 0.0

Merge Join (cost=1.01..52,224.81 rows=333,068 width=107) (actual rows= loops=)

  • Merge Cond: (dj.job_id = js.job_id)
17. 0.000 0.000 ↓ 0.0

Index Scan using distributed_jobs_pkey on distributed_jobs dj (cost=0.42..21,216.34 rows=347,191 width=63) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Index Scan using job_status_pkey on job_status js (cost=0.42..27,079.30 rows=333,068 width=52) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Sort (cost=1,706.79..1,711.04 rows=1,700 width=67) (actual rows= loops=)

  • Sort Key: bj.job_id
20. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,565.20..1,615.58 rows=1,700 width=67) (actual rows= loops=)

  • Hash Cond: (bj.benchmark_id = br.benchmark_id)
21. 0.000 0.000 ↓ 0.0

Seq Scan on benchmark_jobs bj (cost=0.00..27.00 rows=1,700 width=16) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=1,561.20..1,561.20 rows=320 width=59) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.29..1,561.20 rows=320 width=59) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Seq Scan on benchmark_runs br (cost=0.00..13.20 rows=320 width=48) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Index Scan using all_templates_pkey on all_templates b_at (cost=0.29..4.83 rows=1 width=19) (actual rows= loops=)

  • Index Cond: (template_id = br.source_template_id)
26. 0.000 0.000 ↓ 0.0

Hash (cost=111.92..111.92 rows=3,186 width=65) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.25..111.92 rows=3,186 width=65) (actual rows= loops=)

  • Hash Cond: (uca_1.cloud_type = ct.cloud_type)
28. 0.000 0.000 ↓ 0.0

Seq Scan on user_cloud_accounts uca_1 (cost=0.00..66.86 rows=3,186 width=29) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=1.11..1.11 rows=11 width=68) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on cloud_types ct (cost=0.00..1.11 rows=11 width=68) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=1,911.19..1,911.19 rows=8,819 width=19) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Seq Scan on all_templates at (cost=0.00..1,911.19 rows=8,819 width=19) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash (cost=66.86..66.86 rows=3,186 width=12) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Seq Scan on user_cloud_accounts uca (cost=0.00..66.86 rows=3,186 width=12) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Hash (cost=6,133.86..6,133.86 rows=49,736 width=68) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=3,457.49..6,133.86 rows=49,736 width=68) (actual rows= loops=)

  • Hash Cond: (ad.deployment_id = dej.deployment_id)
37. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,021.43..4,099.27 rows=43,530 width=60) (actual rows= loops=)

  • Hash Cond: (ad.app_id = d_at.template_id)
38. 0.000 0.000 ↓ 0.0

Seq Scan on app_deployments ad (cost=0.00..1,479.30 rows=43,530 width=49) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=1,911.19..1,911.19 rows=8,819 width=19) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on all_templates d_at (cost=0.00..1,911.19 rows=8,819 width=19) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Hash (cost=814.36..814.36 rows=49,736 width=16) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Seq Scan on deployment_jobs dej (cost=0.00..814.36 rows=49,736 width=16) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Hash (cost=1.04..1.04 rows=4 width=36) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on cloud_accounts ca (cost=0.00..1.04 rows=4 width=36) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=14.01..129.35 rows=104 width=271) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Hash Join (cost=14.01..128.31 rows=104 width=271) (actual rows= loops=)

  • Hash Cond: (uca_2.cloud_account_id = ca_1.cloud_account_id)
47. 0.000 0.000 ↓ 0.0

Nested Loop (cost=12.92..126.81 rows=104 width=223) (actual rows= loops=)

  • Join Filter: (rj.user_id = uca_2.user_id)
48. 0.000 0.000 ↓ 0.0

Nested Loop (cost=12.64..115.24 rows=10 width=223) (actual rows= loops=)

  • Join Filter: (rj.job_id = js_1.job_id)
49. 0.000 0.000 ↓ 0.0

Nested Loop (cost=12.22..109.78 rows=10 width=204) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Hash Join (cost=11.79..25.28 rows=10 width=152) (actual rows= loops=)

  • Hash Cond: (ua.user_id = rj.user_id)
51. 0.000 0.000 ↓ 0.0

Seq Scan on user_accounts ua (cost=0.00..12.19 rows=319 width=8) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Hash (cost=11.67..11.67 rows=10 width=144) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.25..11.67 rows=10 width=144) (actual rows= loops=)

  • Hash Cond: (rj.cloud_type = ct_1.cloud_type)
54. 0.000 0.000 ↓ 0.0

Seq Scan on remote_job_info rj (cost=0.00..10.25 rows=19 width=140) (actual rows= loops=)

  • Filter: (remote_job_type <> 'ORIGIN'::text)
55. 0.000 0.000 ↓ 0.0

Hash (cost=1.11..1.11 rows=11 width=36) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Seq Scan on cloud_types ct_1 (cost=0.00..1.11 rows=11 width=36) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Index Scan using cliqr_distributed_jobs_job_id7_idx on distributed_jobs dj_1 (cost=0.42..8.44 rows=1 width=52) (actual rows= loops=)

  • Index Cond: (job_id = rj.job_id)
58. 0.000 0.000 ↓ 0.0

Index Scan using job_status_pkey on job_status js_1 (cost=0.42..0.53 rows=1 width=35) (actual rows= loops=)

  • Index Cond: (job_id = dj_1.job_id)
59. 0.000 0.000 ↓ 0.0

Index Scan using cliqr_user_cloud_accounts_user_id3_idx on user_cloud_accounts uca_2 (cost=0.28..1.03 rows=10 width=8) (actual rows= loops=)

  • Index Cond: (user_id = ua.user_id)
60. 0.000 0.000 ↓ 0.0

Hash (cost=1.04..1.04 rows=4 width=36) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on cloud_accounts ca_1 (cost=0.00..1.04 rows=4 width=36) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Hash (cost=1.04..1.04 rows=4 width=4) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Seq Scan on vendor v (cost=0.00..1.04 rows=4 width=4) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=68) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Seq Scan on cloud_groups (cost=0.00..1.05 rows=5 width=68) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Hash (cost=1.11..1.11 rows=6 width=68) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Seq Scan on cloud_types (cost=0.00..1.11 rows=6 width=68) (actual rows= loops=)

  • Filter: (NOT deleted)
68. 0.000 0.000 ↓ 0.0

Index Scan using billing_cost_entity_key on job_cost jc (cost=0.42..0.52 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((entity_type = 'JOB'::text) AND (entity_id = dj.job_id))
69. 0.000 0.000 ↓ 0.0

Index Only Scan using user_accounts_pkey on user_accounts (cost=0.15..0.17 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (user_id = js.user_id)
70. 0.000 0.000 ↓ 0.0

Index Scan using cliqr_node_cost_job_id1_idx on node_cost nc (cost=0.43..421,153.73 rows=45 width=66) (actual rows= loops=)

  • Index Cond: (billing_entity_cost_id = jc.id)
  • Filter: (NOT (SubPlan 1))
71.          

SubPlan (for Index Scan)

72. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..9,191.77 rows=108,918 width=37) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Seq Scan on node_status (cost=0.00..7,796.18 rows=108,918 width=37) (actual rows= loops=)