explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pwlia

Settings
# exclusive inclusive rows x rows loops node
1. 3.252 455.769 ↑ 9.6 8,897 1

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

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

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

Hash Right Join (cost=12,172,138.15..12,179,560.38 rows=85,297 width=582) (actual time=408.247..418.018 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.000 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.006 0.006 ↓ 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.006..0.006 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. 11.381 407.867 ↑ 9.6 8,897 1

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

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

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

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

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

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

18. 2.295 195.504 ↑ 2.4 16,810 1

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

19. 3.531 193.209 ↑ 2,494.0 16,810 1

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

20. 14.735 189.678 ↑ 1,730.1 24,232 1

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

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

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

22. 8.650 171.067 ↑ 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=122.279..171.067 rows=24,232 loops=1)

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

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

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

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

25. 73.504 126.247 ↑ 171.9 115,186 1

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

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

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

27. 17.562 40.939 ↑ 232.1 85,193 1

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

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

29. 4.686 13.337 ↑ 8.1 86,679 1

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

30. 1.400 8.651 ↑ 227.3 3,104 1

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

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

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

32. 0.846 6.930 ↑ 227.3 3,104 1

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

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

CTE flat_resource_group_relations

34. 1.901 3.429 ↑ 227.3 3,104 1

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

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

36. 0.671 1.206 ↑ 220.2 320 6

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

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

38. 0.189 0.343 ↑ 1.0 1,187 1

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

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

40. 0.823 5.614 ↑ 227.3 3,104 1

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

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

42. 0.269 0.498 ↓ 1.0 1,678 1

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

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

44. 0.249 0.470 ↓ 1.0 1,678 1

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

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

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

47. 1.039 2.804 ↓ 1.0 5,496 1

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

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

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

49. 0.232 1.904 ↓ 1.0 1,193 1

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

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

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

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

52. 0.111 1.086 ↑ 1.1 526 1

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

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

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

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

55. 0.145 0.291 ↓ 1.0 564 1

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

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

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

57. 0.147 2.354 ↓ 0.0 0 1

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

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

59. 0.941 2.068 ↓ 1.0 5,521 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 323kB
60. 1.127 1.127 ↓ 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..1.127 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. 55.493 142.751 ↓ 1.0 41,370 1

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

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

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

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

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

65. 0.078 0.164 ↑ 1.0 97 1

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

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

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

67. 1.468 4.341 ↓ 1.0 4,943 1

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

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

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

69. 4.037 21.921 ↓ 1.0 4,958 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 2,217kB
70. 17.884 17.884 ↓ 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..17.884 rows=4,958 loops=1)

71. 1.403 7.825 ↓ 1.0 4,935 1

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

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

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