explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DOTO

Settings
# exclusive inclusive rows x rows loops node
1. 5.800 703.842 ↑ 9.6 8,897 1

Hash Left Join (cost=12,179,015.28..12,187,333.32 rows=85,297 width=972) (actual time=674.683..703.842 rows=8,897 loops=1)

  • Hash Cond: (job_result.job_id = job.job_id)
2. 7.518 693.913 ↑ 9.6 8,897 1

Hash Left Join (cost=12,177,280.36..12,185,374.30 rows=85,297 width=957) (actual time=670.480..693.913 rows=8,897 loops=1)

  • Hash Cond: ((compliance_status.rule_id = job_result.rule_id) AND (compliance_status.resource_id = job_result.resource_id) AND (compliance_status.job_id = job_result.job_id))
3. 16.068 673.835 ↑ 9.6 8,897 1

Hash Right Join (cost=12,172,138.15..12,179,560.38 rows=85,297 width=582) (actual time=657.838..673.835 rows=8,897 loops=1)

  • Hash Cond: ((ce_resource.resource_id = resource.resource_id) AND (ces_rule.rule_checksum = applicable_rules.rule_checksum))
4. 0.001 0.012 ↓ 0.0 0 1

Hash Join (cost=89.37..222.68 rows=18,993 width=16) (actual time=0.012..0.012 rows=0 loops=1)

  • Hash Cond: (ce_resource.compliance_exclusion_id = ces.compliance_exclusion_id)
5. 0.011 0.011 ↓ 0.0 0 1

Seq Scan on compliance_exclusion_resource ce_resource (cost=0.00..28.50 rows=1,850 width=16) (actual time=0.010..0.011 rows=0 loops=1)

6. 0.000 0.000 ↓ 0.0 0

Hash (cost=85.52..85.52 rows=308 width=24) (never executed)

7. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=47.01..85.52 rows=308 width=24) (never executed)

  • Hash Cond: (ces_rule.compliance_exclusion_scope_id = ces.compliance_exclusion_scope_id)
8. 0.000 0.000 ↓ 0.0 0

Seq Scan on compliance_exclusion_scope_rule ces_rule (cost=0.00..28.50 rows=1,850 width=16) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Hash (cost=43.16..43.16 rows=308 width=24) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.02..43.16 rows=308 width=24) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Seq Scan on compliance_exclusion ce (cost=0.00..10.45 rows=5 width=8) (never executed)

  • Filter: ((NOT deleted) AND (expiration_dt > now()))
12. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on compliance_exclusion_scope ces (cost=1.02..6.45 rows=9 width=16) (never executed)

  • Recheck Cond: (compliance_exclusion_id = ce.compliance_exclusion_id)
13. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on compliance_exclusion_scope_idx1 (cost=0.00..1.02 rows=9 width=0) (never executed)

  • Index Cond: (compliance_exclusion_id = ce.compliance_exclusion_id)
14. 15.794 657.755 ↑ 9.6 8,897 1

Hash (cost=12,164,521.32..12,164,521.32 rows=85,297 width=574) (actual time=657.755..657.755 rows=8,897 loops=1)

  • Buckets: 32,768 Batches: 4 Memory Usage: 1,531kB
15. 9.023 641.961 ↑ 9.6 8,897 1

Hash Left Join (cost=11,160,007.50..12,164,521.32 rows=85,297 width=574) (actual time=551.268..641.961 rows=8,897 loops=1)

  • Hash Cond: ((resource.resource_id = compliance_status.resource_id) AND (applicable_rules.rule_id = compliance_status.rule_id))
16. 27.668 628.975 ↑ 9.6 8,897 1

Hash Join (cost=11,159,627.95..12,163,288.29 rows=85,297 width=526) (actual time=547.262..628.975 rows=8,897 loops=1)

  • Hash Cond: ((resource.resource_type_id = rt.resource_type_id) AND (p.policy_plan_id = applicable_rules.policy_plan_id))
17. 6.761 384.962 ↑ 2.4 16,810 1

Nested Loop (cost=11,150,478.21..12,106,886.58 rows=40,000 width=82) (actual time=330.849..384.962 rows=16,810 loops=1)

18. 2.766 344.581 ↑ 2.4 16,810 1

Unique (cost=11,150,477.92..12,093,786.82 rows=40,000 width=16) (actual time=330.806..344.581 rows=16,810 loops=1)

19. 5.712 341.815 ↑ 2,494.0 16,810 1

Unique (cost=11,150,477.92..11,464,914.22 rows=41,924,840 width=16) (actual time=330.804..341.815 rows=16,810 loops=1)

20. 18.634 336.103 ↑ 1,730.1 24,232 1

Sort (cost=11,150,477.92..11,255,290.02 rows=41,924,840 width=16) (actual time=330.800..336.103 rows=24,232 loops=1)

  • Sort Key: p.policy_plan_id, rgr.resource_id
  • Sort Method: quicksort Memory: 1,904kB
21. 2.532 317.469 ↑ 1,730.1 24,232 1

Append (cost=3,376,352.44..4,409,536.26 rows=41,924,840 width=16) (actual time=226.215..317.469 rows=24,232 loops=1)

22. 13.427 311.825 ↑ 1,730.1 24,232 1

Hash Join (cost=3,376,352.44..3,990,059.35 rows=41,923,648 width=16) (actual time=226.213..311.825 rows=24,232 loops=1)

  • Hash Cond: (prg.parent_resource_group_id = rmrg.resource_group_id)
23. 27.095 295.108 ↑ 251.3 78,798 1

Hash Join (cost=3,376,195.37..3,774,734.32 rows=19,801,186 width=16) (actual time=216.818..295.108 rows=78,798 loops=1)

  • Hash Cond: (rgr.resource_id = rmr.resource_id)
24. 26.169 262.936 ↑ 251.3 78,798 1

Unique (cost=3,375,917.73..3,524,426.63 rows=19,801,186 width=16) (actual time=211.681..262.936 rows=78,798 loops=1)

25. 130.067 236.767 ↑ 171.9 115,186 1

Sort (cost=3,375,917.73..3,425,420.70 rows=19,801,186 width=16) (actual time=211.675..236.767 rows=115,186 loops=1)

  • Sort Key: prg.parent_resource_group_id, rgr.resource_id
  • Sort Method: quicksort Memory: 8,472kB
26. 15.569 106.700 ↑ 171.9 115,186 1

Append (cost=138,100.96..637,703.73 rows=19,801,186 width=16) (actual time=20.448..106.700 rows=115,186 loops=1)

27. 36.796 83.080 ↑ 232.1 85,193 1

Merge Join (cost=138,100.96..438,992.08 rows=19,771,207 width=16) (actual time=20.446..83.080 rows=85,193 loops=1)

  • Merge Cond: (rgr.resource_group_id = prg.child_resource_group_id)
28. 15.082 15.082 ↑ 1.0 29,978 1

Index Scan using rm_resource_group_resource_resource_group_id_idx on rm_resource_group_resource rgr (cost=0.29..2,484.18 rows=29,979 width=16) (actual time=0.046..15.082 rows=29,978 loops=1)

29. 9.970 31.202 ↑ 8.1 86,679 1

Materialize (cost=138,100.67..141,629.01 rows=705,667 width=16) (actual time=20.360..31.202 rows=86,679 loops=1)

30. 3.352 21.232 ↑ 227.3 3,104 1

Sort (cost=138,100.67..139,864.84 rows=705,667 width=16) (actual time=20.352..21.232 rows=3,104 loops=1)

  • Sort Key: prg.child_resource_group_id
  • Sort Method: quicksort Memory: 242kB
31. 0.922 17.880 ↑ 227.3 3,104 1

Subquery Scan on prg (cost=32,604.84..57,488.96 rows=705,667 width=16) (actual time=2.322..17.880 rows=3,104 loops=1)

32. 2.301 16.958 ↑ 227.3 3,104 1

Hash Join (cost=32,604.84..50,432.29 rows=705,667 width=212) (actual time=2.320..16.958 rows=3,104 loops=1)

  • Hash Cond: (frr.child_resource_group_id = child_rg.resource_group_id)
33.          

CTE flat_resource_group_relations

34. 4.112 7.841 ↑ 227.3 3,104 1

Recursive Union (cost=0.00..32,433.46 rows=705,667 width=16) (actual time=0.030..7.841 rows=3,104 loops=1)

35. 0.591 0.591 ↑ 1.0 1,187 1

Seq Scan on rm_parent_resource_group (cost=0.00..26.87 rows=1,187 width=16) (actual time=0.023..0.591 rows=1,187 loops=1)

36. 1.811 3.138 ↑ 220.2 320 6

Hash Join (cost=41.71..1,829.33 rows=70,448 width=16) (actual time=0.155..0.523 rows=320 loops=6)

  • Hash Cond: (parent.child_resource_group_id = child.parent_resource_group_id)
37. 0.624 0.624 ↑ 23.0 517 6

WorkTable Scan on flat_resource_group_relations parent (cost=0.00..237.40 rows=11,870 width=16) (actual time=0.001..0.104 rows=517 loops=6)

38. 0.329 0.703 ↑ 1.0 1,187 1

Hash (cost=26.87..26.87 rows=1,187 width=16) (actual time=0.703..0.703 rows=1,187 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 72kB
39. 0.374 0.374 ↑ 1.0 1,187 1

Seq Scan on rm_parent_resource_group child (cost=0.00..26.87 rows=1,187 width=16) (actual time=0.026..0.374 rows=1,187 loops=1)

40. 2.123 13.424 ↑ 227.3 3,104 1

Hash Join (cost=85.69..16,056.09 rows=705,667 width=16) (actual time=1.064..13.424 rows=3,104 loops=1)

  • Hash Cond: (frr.parent_resource_group_id = parent_rg.resource_group_id)
41. 10.302 10.302 ↑ 227.3 3,104 1

CTE Scan on flat_resource_group_relations frr (cost=0.00..14,113.34 rows=705,667 width=16) (actual time=0.033..10.302 rows=3,104 loops=1)

42. 0.494 0.999 ↓ 1.0 1,678 1

Hash (cost=64.75..64.75 rows=1,675 width=8) (actual time=0.999..0.999 rows=1,678 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
43. 0.505 0.505 ↓ 1.0 1,678 1

Seq Scan on rm_resource_group parent_rg (cost=0.00..64.75 rows=1,675 width=8) (actual time=0.013..0.505 rows=1,678 loops=1)

44. 0.613 1.233 ↓ 1.0 1,678 1

Hash (cost=64.75..64.75 rows=1,675 width=8) (actual time=1.233..1.233 rows=1,678 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
45. 0.620 0.620 ↓ 1.0 1,678 1

Seq Scan on rm_resource_group child_rg (cost=0.00..64.75 rows=1,675 width=8) (actual time=0.026..0.620 rows=1,678 loops=1)

46. 8.051 8.051 ↓ 1.0 29,993 1

Seq Scan on rm_resource_group_resource rmrgr (cost=0.00..699.79 rows=29,979 width=16) (actual time=0.019..8.051 rows=29,993 loops=1)

47. 2.339 5.077 ↓ 1.0 5,496 1

Hash (cost=208.95..208.95 rows=5,495 width=8) (actual time=5.077..5.077 rows=5,496 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 279kB
48. 2.738 2.738 ↓ 1.0 5,496 1

Seq Scan on rm_resource rmr (cost=0.00..208.95 rows=5,495 width=8) (actual time=0.023..2.738 rows=5,496 loops=1)

49. 0.445 3.290 ↓ 1.0 1,193 1

Hash (cost=142.17..142.17 rows=1,192 width=24) (actual time=3.290..3.290 rows=1,193 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
50. 0.736 2.845 ↓ 1.0 1,193 1

Hash Join (cost=94.86..142.17 rows=1,192 width=24) (actual time=1.804..2.845 rows=1,193 loops=1)

  • Hash Cond: (p.target_id = t.target_id)
51. 0.348 0.348 ↓ 1.0 1,193 1

Seq Scan on policy_plan_subscription p (cost=0.00..30.92 rows=1,192 width=16) (actual time=0.027..0.348 rows=1,193 loops=1)

52. 0.221 1.761 ↑ 1.1 526 1

Hash (cost=87.83..87.83 rows=563 width=24) (actual time=1.761..1.761 rows=526 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 37kB
53. 0.578 1.540 ↑ 1.1 526 1

Hash Join (cost=18.67..87.83 rows=563 width=24) (actual time=0.483..1.540 rows=526 loops=1)

  • Hash Cond: (rmrg.resource_group_id = t.resource_group_id)
54. 0.513 0.513 ↓ 1.0 1,678 1

Seq Scan on rm_resource_group rmrg (cost=0.00..64.75 rows=1,675 width=8) (actual time=0.014..0.513 rows=1,678 loops=1)

55. 0.225 0.449 ↓ 1.0 564 1

Hash (cost=11.63..11.63 rows=563 width=16) (actual time=0.449..0.449 rows=564 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
56. 0.224 0.224 ↓ 1.0 564 1

Seq Scan on target_resource_group t (cost=0.00..11.63 rows=563 width=16) (actual time=0.018..0.224 rows=564 loops=1)

57. 0.184 3.112 ↓ 0.0 0 1

Hash Join (cost=181.20..228.51 rows=1,192 width=16) (actual time=3.112..3.112 rows=0 loops=1)

  • Hash Cond: (p_1.target_id = t_1.target_id)
58. 0.199 0.199 ↓ 1.0 1,193 1

Seq Scan on policy_plan_subscription p_1 (cost=0.00..30.92 rows=1,192 width=16) (actual time=0.031..0.199 rows=1,193 loops=1)

59. 1.232 2.729 ↓ 1.0 5,521 1

Hash (cost=112.20..112.20 rows=5,520 width=16) (actual time=2.729..2.729 rows=5,521 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 323kB
60. 1.497 1.497 ↓ 1.0 5,521 1

Seq Scan on target_resource t_1 (cost=0.00..112.20 rows=5,520 width=16) (actual time=0.026..1.497 rows=5,521 loops=1)

61. 33.620 33.620 ↑ 1.0 1 16,810

Index Scan using rm_resource_pkey on rm_resource resource (cost=0.28..0.32 rows=1 width=74) (actual time=0.002..0.002 rows=1 loops=16,810)

  • Index Cond: (resource_id = rgr.resource_id)
62. 80.366 216.345 ↓ 1.0 41,370 1

Hash (cost=6,024.21..6,024.21 rows=41,369 width=468) (actual time=216.344..216.345 rows=41,370 loops=1)

  • Buckets: 32,768 Batches: 2 Memory Usage: 10,904kB
63. 107.802 135.979 ↓ 1.0 41,370 1

Hash Join (cost=5.18..6,024.21 rows=41,369 width=468) (actual time=0.099..135.979 rows=41,370 loops=1)

  • Hash Cond: ((applicable_rules.resource_type_uuid)::text = (rt.uuid)::text)
64. 28.121 28.121 ↓ 1.0 41,370 1

Seq Scan on applicable_rules (cost=0.00..5,905.69 rows=41,369 width=497) (actual time=0.029..28.121 rows=41,370 loops=1)

65. 0.023 0.056 ↑ 1.0 97 1

Hash (cost=3.97..3.97 rows=97 width=45) (actual time=0.056..0.056 rows=97 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
66. 0.033 0.033 ↑ 1.0 97 1

Seq Scan on rm_resource_type rt (cost=0.00..3.97 rows=97 width=45) (actual time=0.011..0.033 rows=97 loops=1)

67. 1.468 3.963 ↓ 1.0 4,943 1

Hash (cost=305.42..305.42 rows=4,942 width=48) (actual time=3.963..3.963 rows=4,943 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 433kB
68. 2.495 2.495 ↓ 1.0 4,943 1

Seq Scan on compliance_status (cost=0.00..305.42 rows=4,942 width=48) (actual time=0.021..2.495 rows=4,943 loops=1)

69. 4.892 12.560 ↓ 1.0 4,958 1

Hash (cost=5,055.53..5,055.53 rows=4,953 width=415) (actual time=12.560..12.560 rows=4,958 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 2,217kB
70. 7.668 7.668 ↓ 1.0 4,958 1

Seq Scan on job_result (cost=0.00..5,055.53 rows=4,953 width=415) (actual time=0.020..7.668 rows=4,958 loops=1)

71. 1.191 4.129 ↓ 1.0 4,935 1

Hash (cost=1,673.30..1,673.30 rows=4,930 width=30) (actual time=4.129..4.129 rows=4,935 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 374kB
72. 2.938 2.938 ↓ 1.0 4,935 1

Seq Scan on job (cost=0.00..1,673.30 rows=4,930 width=30) (actual time=0.018..2.938 rows=4,935 loops=1)