explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CmP4

Settings
# exclusive inclusive rows x rows loops node
1. 3.392 508.823 ↑ 9.6 8,897 1

Hash Left Join (cost=12,178,926.60..12,188,244.66 rows=85,299 width=1,070) (actual time=488.901..508.823 rows=8,897 loops=1)

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

Hash Left Join (cost=12,177,191.68..12,186,285.63 rows=85,299 width=1,055) (actual time=485.414..501.972 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. 11.756 488.913 ↑ 9.6 8,897 1

Hash Right Join (cost=12,172,049.47..12,180,471.70 rows=85,299 width=680) (actual time=477.243..488.913 rows=8,897 loops=1)

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

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

  • Hash Cond: (ce_resource.compliance_exclusion_id = ces.compliance_exclusion_id)
5. 0.008 0.008 ↓ 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.008..0.008 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. 14.096 477.148 ↑ 9.6 8,897 1

Hash (cost=12,163,432.61..12,163,432.61 rows=85,299 width=672) (actual time=477.148..477.148 rows=8,897 loops=1)

  • Buckets: 32,768 Batches: 4 Memory Usage: 1,746kB
15. 6.644 463.052 ↑ 9.6 8,897 1

Hash Left Join (cost=11,158,134.55..12,163,432.61 rows=85,299 width=672) (actual time=383.425..463.052 rows=8,897 loops=1)

  • Hash Cond: ((resource.resource_id = compliance_status.resource_id) AND (applicable_rules_test.rule_id = compliance_status.rule_id))
16. 2.753 453.744 ↑ 9.6 8,897 1

Hash Left Join (cost=11,157,755.00..12,162,199.45 rows=85,299 width=624) (actual time=380.736..453.744 rows=8,897 loops=1)

  • Hash Cond: (resource.resource_id = rr.child_resource_id)
17. 27.415 450.964 ↑ 9.6 8,897 1

Hash Join (cost=11,157,733.97..12,161,954.34 rows=85,299 width=616) (actual time=380.680..450.964 rows=8,897 loops=1)

  • Hash Cond: ((resource.resource_type_id = rt.resource_type_id) AND (p.policy_plan_id = applicable_rules_test.policy_plan_id))
18. 14.578 241.831 ↑ 2.4 16,810 1

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

19. 2.408 210.443 ↑ 2.4 16,810 1

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

20. 4.613 208.035 ↑ 2,494.0 16,810 1

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

21. 16.165 203.422 ↑ 1,730.1 24,232 1

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

  • Sort Key: p.policy_plan_id, rgr.resource_id
  • Sort Method: quicksort Memory: 1,904kB
22. 1.622 187.257 ↑ 1,730.1 24,232 1

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

23. 10.653 183.741 ↑ 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=115.558..183.741 rows=24,232 loops=1)

  • Hash Cond: (prg.parent_resource_group_id = rmrg.resource_group_id)
24. 19.915 171.311 ↑ 251.3 78,798 1

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

  • Hash Cond: (rgr.resource_id = rmr.resource_id)
25. 21.427 149.416 ↑ 251.3 78,798 1

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

26. 80.431 127.989 ↑ 171.9 115,186 1

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

  • Sort Key: prg.parent_resource_group_id, rgr.resource_id
  • Sort Method: quicksort Memory: 8,472kB
27. 7.531 47.558 ↑ 171.9 115,186 1

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

28. 16.682 35.845 ↑ 232.1 85,193 1

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

  • Merge Cond: (rgr.resource_group_id = prg.child_resource_group_id)
29. 5.944 5.944 ↑ 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.016..5.944 rows=29,978 loops=1)

30. 4.142 13.219 ↑ 8.1 86,679 1

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

31. 1.393 9.077 ↑ 227.3 3,104 1

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

  • Sort Key: prg.child_resource_group_id
  • Sort Method: quicksort Memory: 242kB
32. 0.481 7.684 ↑ 227.3 3,104 1

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

33. 0.818 7.203 ↑ 227.3 3,104 1

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

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

CTE flat_resource_group_relations

35. 2.178 3.644 ↑ 227.3 3,104 1

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

36. 0.272 0.272 ↑ 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.012..0.272 rows=1,187 loops=1)

37. 0.617 1.194 ↑ 220.2 320 6

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

  • Hash Cond: (parent.child_resource_group_id = child.parent_resource_group_id)
38. 0.186 0.186 ↑ 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.031 rows=517 loops=6)

39. 0.188 0.391 ↑ 1.0 1,187 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 72kB
40. 0.203 0.203 ↑ 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.008..0.203 rows=1,187 loops=1)

41. 0.778 5.962 ↑ 227.3 3,104 1

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

  • Hash Cond: (frr.parent_resource_group_id = parent_rg.resource_group_id)
42. 4.722 4.722 ↑ 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.019..4.722 rows=3,104 loops=1)

43. 0.228 0.462 ↓ 1.0 1,678 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
44. 0.234 0.234 ↓ 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.005..0.234 rows=1,678 loops=1)

45. 0.222 0.423 ↓ 1.0 1,678 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
46. 0.201 0.201 ↓ 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.007..0.201 rows=1,678 loops=1)

47. 4.182 4.182 ↓ 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.013..4.182 rows=29,993 loops=1)

48. 0.782 1.980 ↓ 1.0 5,496 1

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

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

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

50. 0.310 1.777 ↓ 1.0 1,193 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
51. 0.482 1.467 ↓ 1.0 1,193 1

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

  • Hash Cond: (p.target_id = t.target_id)
52. 0.217 0.217 ↓ 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.010..0.217 rows=1,193 loops=1)

53. 0.096 0.768 ↑ 1.1 526 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 37kB
54. 0.269 0.672 ↑ 1.1 526 1

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

  • Hash Cond: (rmrg.resource_group_id = t.resource_group_id)
55. 0.235 0.235 ↓ 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.007..0.235 rows=1,678 loops=1)

56. 0.074 0.168 ↓ 1.0 564 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
57. 0.094 0.094 ↓ 1.0 564 1

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

58. 0.167 1.894 ↓ 0.0 0 1

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

  • Hash Cond: (p_1.target_id = t_1.target_id)
59. 0.176 0.176 ↓ 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.023..0.176 rows=1,193 loops=1)

60. 0.752 1.551 ↓ 1.0 5,521 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 323kB
61. 0.799 0.799 ↓ 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.012..0.799 rows=5,521 loops=1)

62. 16.810 16.810 ↑ 1.0 1 16,810

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

  • Index Cond: (resource_id = rgr.resource_id)
63. 94.213 181.718 ↑ 1.0 41,370 1

Hash (cost=3,806.22..3,806.22 rows=41,370 width=530) (actual time=181.718..181.718 rows=41,370 loops=1)

  • Buckets: 32,768 Batches: 2 Memory Usage: 12,155kB
64. 66.053 87.505 ↑ 1.0 41,370 1

Hash Join (cost=5.18..3,806.22 rows=41,370 width=530) (actual time=0.205..87.505 rows=41,370 loops=1)

  • Hash Cond: ((applicable_rules_test.resource_type_uuid)::text = (rt.uuid)::text)
65. 21.292 21.292 ↑ 1.0 41,370 1

Seq Scan on applicable_rules_test (cost=0.00..3,687.70 rows=41,370 width=522) (actual time=0.022..21.292 rows=41,370 loops=1)

66. 0.082 0.160 ↑ 1.0 97 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
67. 0.078 0.078 ↑ 1.0 97 1

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

68. 0.008 0.027 ↑ 14.8 33 1

Hash (cost=14.90..14.90 rows=490 width=16) (actual time=0.027..0.027 rows=33 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
69. 0.019 0.019 ↑ 14.8 33 1

Seq Scan on rm_parent_resource rr (cost=0.00..14.90 rows=490 width=16) (actual time=0.013..0.019 rows=33 loops=1)

70. 0.995 2.664 ↓ 1.0 4,943 1

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

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

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

72. 2.213 8.136 ↓ 1.0 4,958 1

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

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

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

74. 0.864 3.459 ↓ 1.0 4,935 1

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

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

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