explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w9FP

Settings
# exclusive inclusive rows x rows loops node
1. 71.220 989.915 ↑ 14.0 10,265 1

Merge Left Join (cost=12,731,112.30..12,736,790.98 rows=143,472 width=1,045) (actual time=888.955..989.915 rows=10,265 loops=1)

  • Merge Cond: (resource.resource_id = compliance_status.resource_id)
  • Join Filter: (compliance_status.rule_id = applicable_rules.rule_id)
  • Rows Removed by Join Filter: 488,932
2. 4.972 698.876 ↑ 14.0 10,265 1

Merge Left Join (cost=12,710,039.47..12,711,258.47 rows=143,472 width=533) (actual time=693.106..698.876 rows=10,265 loops=1)

  • Merge Cond: ((resource.resource_id = ce_resource.resource_id) AND (applicable_rules.rule_checksum = ces_rule.rule_checksum))
3. 17.490 693.871 ↑ 14.0 10,265 1

Sort (cost=12,708,467.03..12,708,825.71 rows=143,472 width=525) (actual time=693.067..693.871 rows=10,265 loops=1)

  • Sort Key: resource.resource_id, applicable_rules.rule_checksum
  • Sort Method: quicksort Memory: 7,995kB
4. 43.260 676.381 ↑ 14.0 10,265 1

Hash Join (cost=11,591,918.89..12,662,340.36 rows=143,472 width=525) (actual time=594.817..676.381 rows=10,265 loops=1)

  • Hash Cond: ((resource.resource_type_id = rt.resource_type_id) AND (p.policy_plan_id = applicable_rules.policy_plan_id))
5. 13.493 365.729 ↑ 2.4 16,818 1

Nested Loop (cost=11,580,276.48..12,572,055.23 rows=40,000 width=82) (actual time=327.098..365.729 rows=16,818 loops=1)

6. 2.410 335.418 ↑ 2.4 16,818 1

Unique (cost=11,580,276.19..12,558,951.47 rows=40,000 width=16) (actual time=327.073..335.418 rows=16,818 loops=1)

7. 3.895 333.008 ↑ 2,586.3 16,818 1

Unique (cost=11,580,276.19..11,906,501.29 rows=43,496,679 width=16) (actual time=327.072..333.008 rows=16,818 loops=1)

8. 15.071 329.113 ↑ 1,794.4 24,240 1

Sort (cost=11,580,276.19..11,689,017.89 rows=43,496,679 width=16) (actual time=327.071..329.113 rows=24,240 loops=1)

  • Sort Key: p.policy_plan_id, rgr.resource_id
  • Sort Method: quicksort Memory: 1,905kB
9. 2.485 314.042 ↑ 1,794.4 24,240 1

Append (cost=3,503,144.52..4,575,056.12 rows=43,496,679 width=16) (actual time=235.003..314.042 rows=24,240 loops=1)

10. 15.021 309.755 ↑ 1,794.4 24,240 1

Hash Join (cost=3,503,144.52..4,139,860.82 rows=43,495,487 width=16) (actual time=235.002..309.755 rows=24,240 loops=1)

  • Hash Cond: (prg.parent_resource_group_id = rmrg.resource_group_id)
11. 27.038 291.757 ↑ 259.7 79,113 1

Hash Join (cost=3,502,987.45..3,916,468.51 rows=20,543,590 width=16) (actual time=224.892..291.757 rows=79,113 loops=1)

  • Hash Cond: (rgr.resource_id = rmr.resource_id)
12. 24.667 261.237 ↑ 259.7 79,113 1

Unique (cost=3,502,708.00..3,656,784.92 rows=20,543,590 width=16) (actual time=221.385..261.237 rows=79,113 loops=1)

13. 146.425 236.570 ↑ 177.9 115,501 1

Sort (cost=3,502,708.00..3,554,066.97 rows=20,543,590 width=16) (actual time=221.382..236.570 rows=115,501 loops=1)

  • Sort Key: prg.parent_resource_group_id, rgr.resource_id
  • Sort Method: quicksort Memory: 8,487kB
14. 14.358 90.145 ↑ 177.9 115,501 1

Append (cost=138,100.96..656,376.06 rows=20,543,590 width=16) (actual time=14.576..90.145 rows=115,501 loops=1)

15. 30.922 67.129 ↑ 240.8 85,193 1

Merge Join (cost=138,100.96..450,214.13 rows=20,512,487 width=16) (actual time=14.575..67.129 rows=85,193 loops=1)

  • Merge Cond: (rgr.resource_group_id = prg.child_resource_group_id)
16. 12.858 12.858 ↑ 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,584.22 rows=31,103 width=16) (actual time=0.025..12.858 rows=29,978 loops=1)

17. 8.364 23.349 ↑ 8.1 86,679 1

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

18. 2.372 14.985 ↑ 227.3 3,104 1

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

  • Sort Key: prg.child_resource_group_id
  • Sort Method: quicksort Memory: 242kB
19. 0.641 12.613 ↑ 227.3 3,104 1

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

20. 1.587 11.972 ↑ 227.3 3,104 1

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

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

CTE flat_resource_group_relations

22. 2.383 5.155 ↑ 227.3 3,104 1

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

23. 0.372 0.372 ↑ 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.013..0.372 rows=1,187 loops=1)

24. 1.230 2.400 ↑ 220.2 320 6

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

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

26. 0.419 0.744 ↑ 1.0 1,187 1

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

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

28. 1.717 9.409 ↑ 227.3 3,104 1

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

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

30. 0.524 0.965 ↓ 1.0 1,680 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
31. 0.441 0.441 ↓ 1.0 1,680 1

Seq Scan on rm_resource_group parent_rg (cost=0.00..64.75 rows=1,675 width=8) (actual time=0.009..0.441 rows=1,680 loops=1)

32. 0.534 0.976 ↓ 1.0 1,680 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
33. 0.442 0.442 ↓ 1.0 1,680 1

Seq Scan on rm_resource_group child_rg (cost=0.00..64.75 rows=1,675 width=8) (actual time=0.010..0.442 rows=1,680 loops=1)

34. 8.658 8.658 ↑ 1.0 30,308 1

Seq Scan on rm_resource_group_resource rmrgr (cost=0.00..726.03 rows=31,103 width=16) (actual time=0.013..8.658 rows=30,308 loops=1)

35. 1.647 3.482 ↑ 1.0 5,499 1

Hash (cost=210.31..210.31 rows=5,531 width=8) (actual time=3.482..3.482 rows=5,499 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 279kB
36. 1.835 1.835 ↑ 1.0 5,499 1

Seq Scan on rm_resource rmr (cost=0.00..210.31 rows=5,531 width=8) (actual time=0.014..1.835 rows=5,499 loops=1)

37. 0.408 2.977 ↓ 1.0 1,195 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 82kB
38. 0.648 2.569 ↓ 1.0 1,195 1

Hash Join (cost=94.86..142.17 rows=1,192 width=24) (actual time=1.705..2.569 rows=1,195 loops=1)

  • Hash Cond: (p.target_id = t.target_id)
39. 0.246 0.246 ↓ 1.0 1,195 1

Seq Scan on policy_plan_subscription p (cost=0.00..30.92 rows=1,192 width=16) (actual time=0.015..0.246 rows=1,195 loops=1)

40. 0.160 1.675 ↑ 1.1 527 1

Hash (cost=87.83..87.83 rows=563 width=24) (actual time=1.675..1.675 rows=527 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 37kB
41. 0.649 1.515 ↑ 1.1 527 1

Hash Join (cost=18.67..87.83 rows=563 width=24) (actual time=0.334..1.515 rows=527 loops=1)

  • Hash Cond: (rmrg.resource_group_id = t.resource_group_id)
42. 0.555 0.555 ↓ 1.0 1,680 1

Seq Scan on rm_resource_group rmrg (cost=0.00..64.75 rows=1,675 width=8) (actual time=0.010..0.555 rows=1,680 loops=1)

43. 0.151 0.311 ↓ 1.0 566 1

Hash (cost=11.63..11.63 rows=563 width=16) (actual time=0.311..0.311 rows=566 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
44. 0.160 0.160 ↓ 1.0 566 1

Seq Scan on target_resource_group t (cost=0.00..11.63 rows=563 width=16) (actual time=0.014..0.160 rows=566 loops=1)

45. 0.150 1.802 ↓ 0.0 0 1

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

  • Hash Cond: (p_1.target_id = t_1.target_id)
46. 0.148 0.148 ↓ 1.0 1,195 1

Seq Scan on policy_plan_subscription p_1 (cost=0.00..30.92 rows=1,192 width=16) (actual time=0.014..0.148 rows=1,195 loops=1)

47. 0.740 1.504 ↓ 1.0 5,525 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 323kB
48. 0.764 0.764 ↓ 1.0 5,525 1

Seq Scan on target_resource t_1 (cost=0.00..112.20 rows=5,520 width=16) (actual time=0.009..0.764 rows=5,525 loops=1)

49. 16.818 16.818 ↑ 1.0 1 16,818

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

  • Index Cond: (resource_id = rgr.resource_id)
50. 148.009 267.392 ↓ 1.0 69,585 1

Hash (cost=6,384.66..6,384.66 rows=69,584 width=467) (actual time=267.392..267.392 rows=69,585 loops=1)

  • Buckets: 32,768 Batches: 4 Memory Usage: 9,382kB
51. 94.094 119.383 ↓ 1.0 69,585 1

Hash Join (cost=5.18..6,384.66 rows=69,584 width=467) (actual time=0.131..119.383 rows=69,585 loops=1)

  • Hash Cond: ((applicable_rules.resource_type_uuid)::text = (rt.uuid)::text)
52. 25.202 25.202 ↓ 1.0 69,585 1

Seq Scan on applicable_rules (cost=0.00..6,188.84 rows=69,584 width=496) (actual time=0.015..25.202 rows=69,585 loops=1)

53. 0.042 0.087 ↑ 1.0 97 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
54. 0.045 0.045 ↑ 1.0 97 1

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

55. 0.027 0.033 ↓ 0.0 0 1

Sort (cost=1,572.43..1,619.91 rows=18,993 width=16) (actual time=0.033..0.033 rows=0 loops=1)

  • Sort Key: ce_resource.resource_id, ces_rule.rule_checksum
  • Sort Method: quicksort Memory: 25kB
56. 0.001 0.006 ↓ 0.0 0 1

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

  • Hash Cond: (ce_resource.compliance_exclusion_id = ces.compliance_exclusion_id)
57. 0.005 0.005 ↓ 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.005..0.005 rows=0 loops=1)

58. 0.000 0.000 ↓ 0.0 0

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

59. 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)
60. 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)

61. 0.000 0.000 ↓ 0.0 0

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

62. 0.000 0.000 ↓ 0.0 0

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

63. 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()))
64. 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)
65. 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)
66. 33.364 219.819 ↓ 54.9 500,284 1

Sort (cost=21,057.82..21,080.60 rows=9,115 width=535) (actual time=195.793..219.819 rows=500,284 loops=1)

  • Sort Key: compliance_status.resource_id
  • Sort Method: quicksort Memory: 6,544kB
67. 4.318 186.455 ↑ 1.0 9,011 1

Hash Left Join (cost=15,241.47..20,458.32 rows=9,115 width=535) (actual time=139.165..186.455 rows=9,011 loops=1)

  • Hash Cond: (job_result.job_id = job.job_id)
68. 40.653 146.611 ↑ 1.0 9,011 1

Hash Left Join (cost=11,870.38..17,063.31 rows=9,115 width=513) (actual time=103.023..146.611 rows=9,011 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))
69. 2.993 2.993 ↑ 1.0 9,011 1

Seq Scan on compliance_status (cost=0.00..334.15 rows=9,115 width=48) (actual time=0.011..2.993 rows=9,011 loops=1)

70. 69.477 102.965 ↑ 1.0 72,614 1

Hash (cost=5,970.50..5,970.50 rows=72,850 width=489) (actual time=102.965..102.965 rows=72,614 loops=1)

  • Buckets: 32,768 Batches: 4 Memory Usage: 9,516kB
71. 33.488 33.488 ↑ 1.0 72,614 1

Seq Scan on job_result (cost=0.00..5,970.50 rows=72,850 width=489) (actual time=0.010..33.488 rows=72,614 loops=1)

72. 18.765 35.526 ↑ 1.0 77,547 1

Hash (cost=2,401.04..2,401.04 rows=77,604 width=30) (actual time=35.526..35.526 rows=77,547 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 5,920kB
73. 16.761 16.761 ↑ 1.0 77,547 1

Seq Scan on job (cost=0.00..2,401.04 rows=77,604 width=30) (actual time=0.009..16.761 rows=77,547 loops=1)

Planning time : 9.778 ms
Execution time : 1,000.510 ms