explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NInH

Settings
# exclusive inclusive rows x rows loops node
1. 10.373 719.809 ↑ 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=666.486..719.809 rows=8,897 loops=1)

  • Hash Cond: (job_result.job_id = job.job_id)
2. 13.668 703.111 ↑ 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=660.123..703.111 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. 29.417 674.285 ↑ 9.6 8,897 1

Hash Right Join (cost=12,172,049.47..12,180,471.70 rows=85,299 width=680) (actual time=644.922..674.285 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.011 ↓ 0.0 0 1

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

  • Hash Cond: (ce_resource.compliance_exclusion_id = ces.compliance_exclusion_id)
5. 0.010 0.010 ↓ 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.010 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. 28.611 644.857 ↑ 9.6 8,897 1

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

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

Hash Left Join (cost=11,158,134.55..12,163,432.61 rows=85,299 width=672) (actual time=456.090..616.246 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. 5.837 598.852 ↑ 9.6 8,897 1

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

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

Hash Join (cost=11,157,733.97..12,161,954.34 rows=85,299 width=616) (actual time=450.941..592.981 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. 18.418 412.146 ↑ 2.4 16,810 1

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

19. 5.488 343.298 ↑ 2.4 16,810 1

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

20. 8.453 337.810 ↑ 2,494.0 16,810 1

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

21. 19.449 329.357 ↑ 1,730.1 24,232 1

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

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

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

23. 11.103 305.841 ↑ 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=244.729..305.841 rows=24,232 loops=1)

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

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

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

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

26. 146.315 249.726 ↑ 171.9 115,186 1

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

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

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

28. 39.168 76.311 ↑ 232.1 85,193 1

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

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

30. 10.115 22.177 ↑ 8.1 86,679 1

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

31. 2.098 12.062 ↑ 227.3 3,104 1

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

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

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

33. 1.251 9.419 ↑ 227.3 3,104 1

Hash Join (cost=32,604.84..50,432.29 rows=705,667 width=212) (actual time=1.291..9.419 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.179 4.321 ↑ 227.3 3,104 1

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

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

37. 0.999 1.818 ↑ 220.2 320 6

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

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

39. 0.265 0.525 ↑ 1.0 1,187 1

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

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

41. 1.250 7.523 ↑ 227.3 3,104 1

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

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

43. 0.296 0.606 ↓ 1.0 1,678 1

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

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

45. 0.315 0.645 ↓ 1.0 1,678 1

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

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

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

48. 1.051 2.225 ↓ 1.0 5,496 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 279kB
49. 1.174 1.174 ↓ 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.174 rows=5,496 loops=1)

50. 0.332 2.476 ↓ 1.0 1,193 1

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

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

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

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

53. 0.170 1.343 ↑ 1.1 526 1

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

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

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

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

56. 0.146 0.303 ↓ 1.0 564 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
57. 0.157 0.157 ↓ 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.157 rows=564 loops=1)

58. 0.176 2.221 ↓ 0.0 0 1

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

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

60. 0.884 1.880 ↓ 1.0 5,521 1

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

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

62. 50.430 50.430 ↑ 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.003..0.003 rows=1 loops=16,810)

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

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

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

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

  • Hash Cond: ((applicable_rules_test.resource_type_uuid)::text = (rt.uuid)::text)
65. 14.712 14.712 ↑ 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.031..14.712 rows=41,370 loops=1)

66. 0.029 0.063 ↑ 1.0 97 1

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

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

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

68. 0.011 0.034 ↑ 14.8 33 1

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

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

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

70. 1.828 5.074 ↓ 1.0 4,943 1

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

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

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

72. 5.355 15.158 ↓ 1.0 4,958 1

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

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

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

74. 1.981 6.325 ↓ 1.0 4,935 1

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

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

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