explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5Yek

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1,019.884 ↑ 1.0 10 1

Limit (cost=57,153.07..57,510.97 rows=10 width=1,314) (actual time=1,019.817..1,019.884 rows=10 loops=1)

2. 0.022 1,019.882 ↑ 102.8 10 1

Result (cost=57,153.07..93,945.17 rows=1,028 width=1,314) (actual time=1,019.815..1,019.882 rows=10 loops=1)

3. 0.637 1,019.780 ↑ 102.8 10 1

Sort (cost=57,153.07..57,155.64 rows=1,028 width=1,298) (actual time=1,019.779..1,019.780 rows=10 loops=1)

  • Sort Key: api_rule.rule_id
  • Sort Method: top-N heapsort Memory: 48kB
4. 23.614 1,019.143 ↑ 1.0 1,028 1

GroupAggregate (cost=54,630.25..57,130.86 rows=1,028 width=1,298) (actual time=962.486..1,019.143 rows=1,028 loops=1)

  • Group Key: api_rule.id, (CASE WHEN (acks.id IS NULL) THEN true ELSE false END), (CASE WHEN (acks.id IS NULL) THEN 'enabled'::text WHEN ((acks.id IS NOT NULL) AND (((acks.created_by)::text <> 'Red Hat Insights'::text) OR (acks.created_by IS NULL))) THEN 'disabled'::text WHEN ((acks.id IS NOT NULL) AND ((acks.created_by)::text = 'Red Hat Insights'::text)) THEN 'rhdisabled'::text ELSE NULL::text END), (COALESCE(((SubPlan 3))::integer, 0)), api_rulecategory.id, api_ruleimpact.id
5. 178.868 995.529 ↓ 48.2 49,554 1

Sort (cost=54,630.25..54,632.82 rows=1,028 width=1,306) (actual time=962.474..995.529 rows=49,554 loops=1)

  • Sort Key: api_rule.id, (CASE WHEN (acks.id IS NULL) THEN true ELSE false END), (CASE WHEN (acks.id IS NULL) THEN 'enabled'::text WHEN ((acks.id IS NOT NULL) AND (((acks.created_by)::text <> 'Red Hat Insights'::text) OR (acks.created_by IS NULL))) THEN 'disabled'::text WHEN ((acks.id IS NOT NULL) AND ((acks.created_by)::text = 'Red Hat Insights'::text)) THEN 'rhdisabled'::text ELSE NULL::text END), (COALESCE(((SubPlan 3))::integer, 0)), api_rulecategory.id, api_ruleimpact.id
  • Sort Method: external merge Disk: 77,208kB
6. 89.700 816.661 ↓ 48.2 49,554 1

Nested Loop Left Join (cost=34,667.80..54,578.82 rows=1,028 width=1,306) (actual time=44.460..816.661 rows=49,554 loops=1)

7. 40.023 231.421 ↓ 48.2 49,554 1

Nested Loop (cost=34,667.66..51,932.91 rows=1,028 width=1,250) (actual time=44.419..231.421 rows=49,554 loops=1)

  • Join Filter: (api_rule.category_id = api_rulecategory.id)
  • Rows Removed by Join Filter: 80,808
8. 50.388 191.398 ↓ 48.2 49,554 1

Hash Right Join (cost=34,667.66..51,879.43 rows=1,028 width=1,235) (actual time=44.400..191.398 rows=49,554 loops=1)

  • Hash Cond: (ac.rule_id = api_rule.id)
  • Join Filter: (acks.id IS NULL)
  • Rows Removed by Join Filter: 10,510
9. 69.087 138.104 ↓ 78.2 180,494 1

Hash Join (cost=34,372.49..51,558.72 rows=2,308 width=20) (actual time=33.522..138.104 rows=180,494 loops=1)

  • Hash Cond: (ac.system_uuid = hosts_v1_1.id)
10. 35.528 35.528 ↑ 1.0 184,443 1

Index Only Scan using api_currentreport_account_rule_id_system_uuid_f48d4524_uniq on api_currentreport ac (cost=0.56..16,696.06 rows=186,943 width=20) (actual time=0.021..35.528 rows=184,443 loops=1)

  • Index Cond: (account = '729650'::text)
  • Heap Fetches: 1
11. 5.378 33.489 ↓ 3.0 19,162 1

Hash (cost=34,291.03..34,291.03 rows=6,472 width=16) (actual time=33.488..33.489 rows=19,162 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1,155kB
12. 25.504 28.111 ↓ 3.0 19,162 1

Bitmap Heap Scan on hosts_v1_1 (cost=651.67..34,291.03 rows=6,472 width=16) (actual time=5.041..28.111 rows=19,162 loops=1)

  • Recheck Cond: ((account)::text = '729650'::text)
  • Filter: ((stale_timestamp + '7 days'::interval) > '2020-10-11 18:35:21.020412+00'::timestamp with time zone)
  • Rows Removed by Filter: 368
  • Heap Blocks: exact=12,016
13. 2.607 2.607 ↓ 1.0 19,531 1

Bitmap Index Scan on hosts_v1_1_account_index (cost=0.00..650.05 rows=19,417 width=0) (actual time=2.607..2.607 rows=19,531 loops=1)

  • Index Cond: ((account)::text = '729650'::text)
14. 0.617 2.906 ↑ 1.0 1,028 1

Hash (cost=282.32..282.32 rows=1,028 width=1,219) (actual time=2.906..2.906 rows=1,028 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 1,259kB
15. 0.679 2.289 ↑ 1.0 1,028 1

Hash Right Join (cost=253.90..282.32 rows=1,028 width=1,219) (actual time=1.451..2.289 rows=1,028 loops=1)

  • Hash Cond: (acks.rule_id = api_rule.id)
16. 0.172 0.172 ↑ 1.0 351 1

Seq Scan on api_ack acks (cost=0.00..27.50 rows=351 width=11) (actual time=0.007..0.172 rows=351 loops=1)

  • Filter: ((account)::text = '729650'::text)
  • Rows Removed by Filter: 889
17. 0.602 1.438 ↑ 1.0 1,028 1

Hash (cost=241.05..241.05 rows=1,028 width=1,212) (actual time=1.438..1.438 rows=1,028 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 1,257kB
18. 0.836 0.836 ↑ 1.0 1,028 1

Seq Scan on api_rule (cost=0.00..241.05 rows=1,028 width=1,212) (actual time=0.008..0.836 rows=1,028 loops=1)

  • Filter: active
  • Rows Removed by Filter: 377
19. 0.000 0.000 ↑ 1.3 3 49,554

Materialize (cost=0.00..1.06 rows=4 width=15) (actual time=0.000..0.000 rows=3 loops=49,554)

20. 0.010 0.010 ↑ 1.0 4 1

Seq Scan on api_rulecategory (cost=0.00..1.04 rows=4 width=15) (actual time=0.009..0.010 rows=4 loops=1)

21. 49.554 49.554 ↑ 1.0 1 49,554

Index Scan using api_ruleimpact_pkey on api_ruleimpact (cost=0.14..0.17 rows=1 width=26) (actual time=0.001..0.001 rows=1 loops=49,554)

  • Index Cond: (id = api_rule.impact_id)
22.          

SubPlan (for Nested Loop Left Join)

23. 445.986 445.986 ↓ 0.0 0 49,554

Seq Scan on api_rulerating u0_1 (cost=0.00..2.40 rows=1 width=2) (actual time=0.009..0.009 rows=0 loops=49,554)

  • Filter: (((rated_by)::text = 'rhn-support-dkuc'::text) AND (rule_id = api_rule.id))
  • Rows Removed by Filter: 93
24.          

SubPlan (for Result)

25. 0.010 0.060 ↓ 0.0 0 10

GroupAggregate (cost=0.55..16.61 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=10)

  • Group Key: u1.rule_id
26. 0.000 0.050 ↓ 0.0 0 10

Nested Loop (cost=0.55..16.60 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=10)

27. 0.030 0.030 ↑ 1.0 1 10

Index Scan using api_resolution_rule_id_43e25d91 on api_resolution u1 (cost=0.28..8.29 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=10)

  • Index Cond: (rule_id = api_rule.id)
28. 0.020 0.020 ↓ 0.0 0 10

Index Scan using api_playbook_resolution_id_ce9879e0 on api_playbook u0 (cost=0.28..8.29 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=10)

  • Index Cond: (resolution_id = u1.id)
29. 0.000 0.020 ↑ 1.0 1 10

Aggregate (cost=16.75..16.76 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=10)

30. 0.010 0.020 ↓ 0.0 0 10

Nested Loop (cost=0.70..16.75 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=10)

31. 0.010 0.010 ↓ 0.0 0 10

Index Scan using api_hostack_rule_id_account_system_uuid_uindex on api_hostack v0 (cost=0.28..8.30 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=10)

  • Index Cond: ((rule_id = api_rule.id) AND ((account)::text = '729650'::text))
32. 0.000 0.000 ↓ 0.0 0

Index Scan using hosts_v1_1_pkey on hosts_v1_1 hosts_v1_1_1 (cost=0.42..8.45 rows=1 width=16) (never executed)

  • Index Cond: (id = v0.system_uuid)
  • Filter: (((account)::text = '729650'::text) AND ((stale_timestamp + '7 days'::interval) > '2020-10-11 18:35:21.02595+00'::timestamp with time zone))
Planning time : 2.521 ms
Execution time : 1,029.950 ms