explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r6Wa

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 127.647 ↑ 1.2 80 1

Limit (cost=36,195.52..39,887.77 rows=100 width=1,313) (actual time=126.417..127.647 rows=80 loops=1)

2. 0.156 127.639 ↑ 3.4 80 1

Result (cost=36,195.52..46,275.37 rows=273 width=1,313) (actual time=126.416..127.639 rows=80 loops=1)

3. 0.133 126.363 ↑ 3.4 80 1

Sort (cost=36,195.52..36,196.20 rows=273 width=1,293) (actual time=126.358..126.363 rows=80 loops=1)

  • Sort Key: hit_counts.count DESC NULLS LAST, api_rule.rule_id
  • Sort Method: quicksort Memory: 170kB
4. 0.034 126.230 ↑ 3.4 80 1

Hash Left Join (cost=36,136.72..36,185.08 rows=273 width=1,293) (actual time=125.676..126.230 rows=80 loops=1)

  • Hash Cond: (api_rule.impact_id = api_ruleimpact.id)
5. 0.053 126.175 ↑ 3.4 80 1

Nested Loop (cost=36,133.83..36,180.08 rows=273 width=1,242) (actual time=125.642..126.175 rows=80 loops=1)

  • Join Filter: (api_rule.category_id = api_rulecategory.id)
  • Rows Removed by Join Filter: 92
6. 0.097 126.122 ↑ 3.4 80 1

Hash Join (cost=36,133.83..36,165.10 rows=273 width=1,227) (actual time=125.627..126.122 rows=80 loops=1)

  • Hash Cond: (api_rule.id = hit_counts.rule_id)
7. 0.690 2.468 ↑ 1.0 1,032 1

Hash Right Join (cost=262.95..291.50 rows=1,032 width=1,219) (actual time=1.641..2.468 rows=1,032 loops=1)

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

Seq Scan on api_ack acks (cost=0.00..27.62 rows=351 width=12) (actual time=0.008..0.154 rows=351 loops=1)

  • Filter: ((account)::text = '729650'::text)
  • Rows Removed by Filter: 900
9. 0.800 1.624 ↑ 1.0 1,032 1

Hash (cost=250.05..250.05 rows=1,032 width=1,211) (actual time=1.624..1.624 rows=1,032 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 1,263kB
10. 0.824 0.824 ↑ 1.0 1,032 1

Seq Scan on api_rule (cost=0.00..250.05 rows=1,032 width=1,211) (actual time=0.006..0.824 rows=1,032 loops=1)

  • Filter: active
  • Rows Removed by Filter: 373
11. 0.020 123.557 ↑ 4.7 80 1

Hash (cost=35,866.23..35,866.23 rows=372 width=12) (actual time=123.557..123.557 rows=80 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
12. 0.008 123.537 ↑ 4.7 80 1

Subquery Scan on hit_counts (cost=35,843.69..35,866.23 rows=372 width=12) (actual time=113.239..123.537 rows=80 loops=1)

13. 3.404 123.529 ↑ 4.7 80 1

GroupAggregate (cost=35,843.69..35,862.51 rows=372 width=12) (actual time=113.238..123.529 rows=80 loops=1)

  • Group Key: cr.rule_id
  • Filter: (count(cr.rule_id) IS NOT NULL)
14. 4.493 120.125 ↓ 48.6 48,770 1

Merge Anti Join (cost=35,843.69..35,851.24 rows=1,004 width=4) (actual time=113.227..120.125 rows=48,770 loops=1)

  • Merge Cond: ((cr.rule_id = u0.rule_id) AND (cr.system_uuid = u0.system_uuid))
15. 9.938 115.612 ↓ 48.6 48,770 1

Sort (cost=35,835.38..35,837.89 rows=1,004 width=20) (actual time=113.202..115.612 rows=48,770 loops=1)

  • Sort Key: cr.rule_id, cr.system_uuid
  • Sort Method: quicksort Memory: 5,347kB
16. 13.408 105.674 ↓ 48.6 48,770 1

Hash Join (cost=26,801.25..35,785.33 rows=1,004 width=20) (actual time=32.872..105.674 rows=48,770 loops=1)

  • Hash Cond: (cr.system_uuid = hosts_v1_1.id)
17. 63.323 66.399 ↑ 1.7 49,146 1

Index Only Scan using api_currentreport_account_rule_id_system_uuid_f48d4524_uniq on api_currentreport cr (cost=299.95..9,065.50 rows=83,249 width=20) (actual time=6.986..66.399 rows=49,146 loops=1)

  • Index Cond: (account = '729650'::text)
  • Filter: (NOT (hashed SubPlan 4))
  • Rows Removed by Filter: 127,658
  • Heap Fetches: 15,590
18.          

SubPlan (for Index Only Scan)

19. 0.652 3.076 ↑ 1.1 655 1

HashAggregate (cost=290.35..297.59 rows=724 width=4) (actual time=2.975..3.076 rows=655 loops=1)

  • Group Key: api_rule_1.id
20. 0.169 2.424 ↑ 1.0 724 1

Append (cost=0.00..288.54 rows=724 width=4) (actual time=0.004..2.424 rows=724 loops=1)

21. 1.091 1.091 ↑ 1.0 373 1

Seq Scan on api_rule api_rule_1 (cost=0.00..250.05 rows=373 width=4) (actual time=0.003..1.091 rows=373 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,032
22. 1.164 1.164 ↑ 1.0 351 1

Seq Scan on api_ack (cost=0.00..27.62 rows=351 width=4) (actual time=0.087..1.164 rows=351 loops=1)

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 900
23. 2.853 25.867 ↓ 3.1 19,273 1

Hash (cost=26,422.56..26,422.56 rows=6,299 width=16) (actual time=25.867..25.867 rows=19,273 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1,160kB
24. 20.519 23.014 ↓ 3.1 19,273 1

Bitmap Heap Scan on hosts_v1_1 (cost=1,315.73..26,422.56 rows=6,299 width=16) (actual time=4.072..23.014 rows=19,273 loops=1)

  • Recheck Cond: ((account)::text = '729650'::text)
  • Filter: ((stale_timestamp + '7 days'::interval) > '2020-10-12 15:07:40.763728+00'::timestamp with time zone)
  • Rows Removed by Filter: 121
  • Heap Blocks: exact=12,769
25. 2.495 2.495 ↓ 1.1 20,456 1

Bitmap Index Scan on hosts_v1_1_account_index (cost=0.00..1,314.16 rows=18,898 width=0) (actual time=2.495..2.495 rows=20,456 loops=1)

  • Index Cond: ((account)::text = '729650'::text)
26. 0.008 0.020 ↓ 0.0 0 1

Sort (cost=8.30..8.31 rows=1 width=20) (actual time=0.020..0.020 rows=0 loops=1)

  • Sort Key: u0.rule_id, u0.system_uuid
  • Sort Method: quicksort Memory: 25kB
27. 0.012 0.012 ↓ 0.0 0 1

Index Scan using api_hostack_account_97691895_like on api_hostack u0 (cost=0.28..8.29 rows=1 width=20) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: ((account)::text = '729650'::text)
28. 0.000 0.000 ↑ 2.0 2 80

Materialize (cost=0.00..1.06 rows=4 width=15) (actual time=0.000..0.000 rows=2 loops=80)

29. 0.009 0.009 ↑ 1.0 4 1

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

30. 0.012 0.021 ↑ 1.0 84 1

Hash (cost=1.84..1.84 rows=84 width=26) (actual time=0.021..0.021 rows=84 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
31. 0.009 0.009 ↑ 1.0 84 1

Seq Scan on api_ruleimpact (cost=0.00..1.84 rows=84 width=26) (actual time=0.003..0.009 rows=84 loops=1)

32.          

SubPlan (for Result)

33. 0.000 0.320 ↑ 1.0 1 80

GroupAggregate (cost=0.55..16.61 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=80)

  • Group Key: u1.rule_id
34. 0.078 0.320 ↑ 1.0 1 80

Nested Loop (cost=0.55..16.60 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=80)

35. 0.160 0.160 ↑ 1.0 1 80

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

  • Index Cond: (rule_id = api_rule.id)
36. 0.082 0.082 ↑ 1.0 1 82

Index Scan using api_playbook_resolution_id_ce9879e0 on api_playbook u0_1 (cost=0.28..8.29 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=82)

  • Index Cond: (resolution_id = u1.id)
37. 0.000 0.080 ↑ 1.0 1 80

Aggregate (cost=16.75..16.76 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=80)

38. 0.000 0.080 ↓ 0.0 0 80

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

39. 0.080 0.080 ↓ 0.0 0 80

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=80)

  • Index Cond: ((rule_id = api_rule.id) AND ((account)::text = '729650'::text))
40. 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-12 15:07:40.776569+00'::timestamp with time zone))
41. 0.720 0.720 ↓ 0.0 0 80

Seq Scan on api_rulerating u0_2 (cost=0.00..3.53 rows=1 width=2) (actual time=0.009..0.009 rows=0 loops=80)

  • Filter: (((rated_by)::text = 'rhn-support-dkuc'::text) AND (rule_id = api_rule.id))
  • Rows Removed by Filter: 102
Planning time : 1.970 ms
Execution time : 127.835 ms