explain.depesz.com

PostgreSQL's explain analyze made readable

Result: afE2

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

  • Hash Cond: (job_result.job_id = job.job_id)
2. 8.140 601.712 ↑ 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=575.919..601.712 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. 17.784 582.805 ↑ 9.6 8,897 1

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

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

  • Hash Cond: (ce_resource.compliance_exclusion_id = ces.compliance_exclusion_id)
5. 0.007 0.007 ↓ 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.007..0.007 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. 18.824 565.013 ↑ 9.6 8,897 1

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

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

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

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

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

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

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

19. 3.119 269.529 ↑ 2.4 16,810 1

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

20. 5.286 266.410 ↑ 2,494.0 16,810 1

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

21. 20.152 261.124 ↑ 1,730.1 24,232 1

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

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

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

23. 13.955 235.257 ↑ 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=152.934..235.257 rows=24,232 loops=1)

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

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

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

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

26. 104.561 164.491 ↑ 171.9 115,186 1

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

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

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

28. 21.524 43.110 ↑ 232.1 85,193 1

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

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

30. 5.545 13.175 ↑ 8.1 86,679 1

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

31. 1.303 7.630 ↑ 227.3 3,104 1

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

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

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

33. 0.714 5.954 ↑ 227.3 3,104 1

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

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

CTE flat_resource_group_relations

35. 1.610 2.857 ↑ 227.3 3,104 1

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

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

37. 0.570 1.050 ↑ 220.2 320 6

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

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

39. 0.155 0.312 ↑ 1.0 1,187 1

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

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

41. 0.696 4.814 ↑ 227.3 3,104 1

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

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

43. 0.207 0.416 ↓ 1.0 1,678 1

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

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

45. 0.207 0.426 ↓ 1.0 1,678 1

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

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

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

48. 0.688 1.629 ↓ 1.0 5,496 1

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

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

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

50. 0.199 1.378 ↓ 1.0 1,193 1

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

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

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

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

53. 0.077 0.673 ↑ 1.1 526 1

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

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

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

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

56. 0.073 0.171 ↓ 1.0 564 1

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

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

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

58. 0.306 3.552 ↓ 0.0 0 1

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

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

60. 1.418 2.987 ↓ 1.0 5,521 1

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

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

62. 33.620 33.620 ↑ 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.002..0.002 rows=1 loops=16,810)

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

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

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

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

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

66. 0.070 0.142 ↑ 1.0 97 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
67. 0.072 0.072 ↑ 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.072 rows=97 loops=1)

68. 0.012 0.036 ↑ 14.8 33 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
69. 0.024 0.024 ↑ 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.024 rows=33 loops=1)

70. 1.718 4.654 ↓ 1.0 4,943 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 433kB
71. 2.936 2.936 ↓ 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.936 rows=4,943 loops=1)

72. 3.603 10.767 ↓ 1.0 4,958 1

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

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

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

74. 1.568 5.150 ↓ 1.0 4,935 1

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

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

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