explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PiCZ

Settings
# exclusive inclusive rows x rows loops node
1. 123.739 162,235.060 ↑ 178.2 10,265 1

Hash Right Join (cost=810,789.08..994,004.24 rows=1,829,296 width=1,141) (actual time=161,931.235..162,235.060 rows=10,265 loops=1)

  • Hash Cond: ((compliance_status.resource_id = resource.resource_id) AND (compliance_status.rule_id = applicable_rules_test.rule_id))
2. 11.929 265.254 ↑ 1.0 9,011 1

Nested Loop Left Join (cost=0.87..27,889.77 rows=9,115 width=535) (actual time=0.079..265.254 rows=9,011 loops=1)

3. 17.236 217.281 ↑ 1.0 9,011 1

Nested Loop Left Join (cost=0.58..24,143.74 rows=9,115 width=513) (actual time=0.062..217.281 rows=9,011 loops=1)

4. 10.814 10.814 ↑ 1.0 9,011 1

Index Scan using compliance_status_if3 on compliance_status (cost=0.29..865.36 rows=9,115 width=48) (actual time=0.019..10.814 rows=9,011 loops=1)

5. 189.231 189.231 ↑ 1.0 1 9,011

Index Scan using job_result_if2 on job_result (cost=0.29..2.54 rows=1 width=489) (actual time=0.021..0.021 rows=1 loops=9,011)

  • Index Cond: (job_id = compliance_status.job_id)
  • Filter: ((rule_id = compliance_status.rule_id) AND (resource_id = compliance_status.resource_id))
  • Rows Removed by Filter: 38
6. 36.044 36.044 ↑ 1.0 1 9,011

Index Scan using job_pkey on job (cost=0.29..0.41 rows=1 width=30) (actual time=0.004..0.004 rows=1 loops=9,011)

  • Index Cond: (job_id = job_result.job_id)
7. 37.373 161,846.067 ↑ 178.2 10,265 1

Hash (cost=636,861.77..636,861.77 rows=1,829,296 width=629) (actual time=161,846.067..161,846.067 rows=10,265 loops=1)

  • Buckets: 32,768 Batches: 128 Memory Usage: 314kB
8. 60.097 161,808.694 ↑ 178.2 10,265 1

Hash Right Join (cost=476,023.11..636,861.77 rows=1,829,296 width=629) (actual time=161,748.662..161,808.694 rows=10,265 loops=1)

  • Hash Cond: ((ce_resource.resource_id = resource.resource_id) AND (ces_rule.rule_checksum = applicable_rules_test.rule_checksum))
9. 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)
10. 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)

11. 0.000 0.000 ↓ 0.0 0

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

12. 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)
13. 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)

14. 0.000 0.000 ↓ 0.0 0

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

15. 0.000 0.000 ↓ 0.0 0

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

16. 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()))
17. 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)
18. 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)
19. 38.638 161,748.589 ↑ 178.2 10,265 1

Hash (cost=303,793.29..303,793.29 rows=1,829,296 width=621) (actual time=161,748.589..161,748.589 rows=10,265 loops=1)

  • Buckets: 32,768 Batches: 128 Memory Usage: 312kB
20. 5.818 161,709.951 ↑ 178.2 10,265 1

Hash Left Join (cost=249,008.64..303,793.29 rows=1,829,296 width=621) (actual time=161,590.279..161,709.951 rows=10,265 loops=1)

  • Hash Cond: (resource.resource_id = rr.child_resource_id)
21. 65.509 161,704.082 ↑ 178.2 10,265 1

Merge Join (cost=248,987.62..298,966.68 rows=1,829,296 width=613) (actual time=161,590.206..161,704.082 rows=10,265 loops=1)

  • Merge Cond: ((applicable_rules_test.policy_plan_id = policy_plan_subscription.policy_plan_id) AND ((applicable_rules_test.resource_type_uuid)::text = (rt.uuid)::text))
22. 81.677 81.677 ↑ 1.0 69,585 1

Index Scan using applicable_rules_idx1_test on applicable_rules_test (cost=0.42..16,343.88 rows=69,585 width=519) (actual time=0.096..81.677 rows=69,585 loops=1)

23. 5.163 161,556.896 ↑ 28.0 18,246 1

Materialize (cost=248,987.20..251,537.20 rows=510,000 width=139) (actual time=161,549.464..161,556.896 rows=18,246 loops=1)

24. 10.192 161,551.733 ↑ 30.3 16,818 1

Sort (cost=248,987.20..250,262.20 rows=510,000 width=139) (actual time=161,549.460..161,551.733 rows=16,818 loops=1)

  • Sort Key: policy_plan_subscription.policy_plan_id, rt.uuid
  • Sort Method: quicksort Memory: 5,236kB
25. 3.198 161,541.541 ↑ 30.3 16,818 1

Hash Join (cost=147,255.33..164,032.35 rows=510,000 width=139) (actual time=161,529.562..161,541.541 rows=16,818 loops=1)

  • Hash Cond: (resource.resource_type_id = rt.resource_type_id)
26. 4.874 161,538.262 ↑ 30.3 16,818 1

Hash Join (cost=147,250.15..162,629.92 rows=510,000 width=110) (actual time=161,529.472..161,538.262 rows=16,818 loops=1)

  • Hash Cond: ((get_target_resources(policy_plan_subscription.target_id)) = resource.resource_id)
27. 2.722 161,528.588 ↑ 30.3 16,818 1

Unique (cost=146,970.70..155,910.70 rows=510,000 width=16) (actual time=161,524.654..161,528.588 rows=16,818 loops=1)

28. 12.001 161,525.866 ↑ 49.2 24,240 1

Sort (cost=146,970.70..149,950.70 rows=1,192,000 width=16) (actual time=161,524.653..161,525.866 rows=24,240 loops=1)

  • Sort Key: policy_plan_subscription.policy_plan_id, (get_target_resources(policy_plan_subscription.target_id))
  • Sort Method: quicksort Memory: 1,905kB
29. 161,512.631 161,513.865 ↑ 49.2 24,240 1

ProjectSet (cost=0.00..6,294.88 rows=1,192,000 width=16) (actual time=241.350..161,513.865 rows=24,240 loops=1)

30. 1.234 1.234 ↓ 1.0 1,195 1

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

31. 2.387 4.800 ↑ 1.0 5,499 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 825kB
32. 2.413 2.413 ↑ 1.0 5,499 1

Seq Scan on rm_resource resource (cost=0.00..210.31 rows=5,531 width=102) (actual time=0.011..2.413 rows=5,499 loops=1)

33. 0.033 0.081 ↑ 1.0 97 1

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

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

35. 0.014 0.051 ↑ 14.8 33 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
36. 0.037 0.037 ↑ 14.8 33 1

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