explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SzLt

Settings
# exclusive inclusive rows x rows loops node
1. 3.626 550.784 ↑ 9.6 8,897 1

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

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

Hash Left Join (cost=12,177,280.36..12,185,374.30 rows=85,297 width=957) (actual time=528.206..543.738 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. 10.509 531.157 ↑ 9.6 8,897 1

Hash Right Join (cost=12,172,138.15..12,179,560.38 rows=85,297 width=582) (actual time=520.713..531.157 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.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)
5. 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)

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. 12.158 520.642 ↑ 9.6 8,897 1

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

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

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

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

Hash Join (cost=11,159,627.95..12,163,288.29 rows=85,297 width=526) (actual time=433.919..498.937 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. 14.951 293.941 ↑ 2.4 16,810 1

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

18. 2.592 262.180 ↑ 2.4 16,810 1

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

19. 4.359 259.588 ↑ 2,494.0 16,810 1

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

20. 15.030 255.229 ↑ 1,730.1 24,232 1

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

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

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

22. 11.279 236.597 ↑ 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=168.644..236.597 rows=24,232 loops=1)

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

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

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

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

25. 93.669 175.573 ↑ 171.9 115,186 1

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

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

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

27. 30.001 64.564 ↑ 232.1 85,193 1

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

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

29. 8.001 23.886 ↑ 8.1 86,679 1

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

30. 2.387 15.885 ↑ 227.3 3,104 1

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

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

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

32. 1.693 12.744 ↑ 227.3 3,104 1

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

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

CTE flat_resource_group_relations

34. 2.737 5.662 ↑ 227.3 3,104 1

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

35. 0.399 0.399 ↑ 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.015..0.399 rows=1,187 loops=1)

36. 1.436 2.526 ↑ 220.2 320 6

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

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

38. 0.314 0.652 ↑ 1.0 1,187 1

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

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

40. 1.726 10.172 ↑ 227.3 3,104 1

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

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

42. 0.479 0.939 ↓ 1.0 1,678 1

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

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

44. 0.445 0.879 ↓ 1.0 1,678 1

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

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

46. 4.853 4.853 ↓ 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.014..4.853 rows=29,993 loops=1)

47. 1.396 2.999 ↓ 1.0 5,496 1

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

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

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

49. 0.361 2.591 ↓ 1.0 1,193 1

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

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

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

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

52. 0.160 1.379 ↑ 1.1 526 1

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

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

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

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

55. 0.163 0.333 ↓ 1.0 564 1

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

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

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

57. 0.179 1.919 ↓ 0.0 0 1

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

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

59. 0.745 1.572 ↓ 1.0 5,521 1

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

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

61. 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=74) (actual time=0.001..0.001 rows=1 loops=16,810)

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

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

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

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

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

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

65. 0.041 0.086 ↑ 1.0 97 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
66. 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.013..0.045 rows=97 loops=1)

67. 1.245 3.510 ↓ 1.0 4,943 1

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

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

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

69. 2.211 7.467 ↓ 1.0 4,958 1

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

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

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

71. 0.913 3.420 ↓ 1.0 4,935 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 374kB
72. 2.507 2.507 ↓ 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.507 rows=4,935 loops=1)