explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wshS : Optimization for: plan #RLa6

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 86.899 1,148.624 ↑ 1.0 4 1

GroupAggregate (cost=67,033.22..98,779.28 rows=4 width=19) (actual time=967.871..1,148.624 rows=4 loops=1)

  • Group Key: api_rulecategory.name
2. 73.613 1,061.725 ↑ 1.3 136,431 1

Nested Loop (cost=67,033.22..97,876.52 rows=180,544 width=27) (actual time=653.432..1,061.725 rows=136,431 loops=1)

  • Join Filter: (api_rule.category_id = api_rulecategory.id)
  • Rows Removed by Join Filter: 409293
3. 0.016 0.016 ↑ 1.0 4 1

Index Scan using api_rulecategory_name_key on api_rulecategory (cost=0.13..12.19 rows=4 width=15) (actual time=0.003..0.016 rows=4 loops=1)

4. 82.900 988.096 ↑ 1.3 136,431 4

Materialize (cost=67,033.09..84,309.05 rows=180,544 width=20) (actual time=159.080..247.024 rows=136,431 loops=4)

5. 133.141 905.196 ↑ 1.3 136,431 1

Hash Join (cost=67,033.09..82,348.33 rows=180,544 width=20) (actual time=636.303..905.196 rows=136,431 loops=1)

  • Hash Cond: (api_currentreport.system_uuid = k.host_id)
6. 45.869 137.134 ↓ 1.2 136,431 1

Hash Join (cost=237.28..11,367.43 rows=109,745 width=20) (actual time=1.214..137.134 rows=136,431 loops=1)

  • Hash Cond: (api_currentreport.rule_id = api_rule.id)
7. 90.164 90.366 ↓ 1.7 211,998 1

Index Only Scan using api_currentreport_account_rule_id_system_uuid_f48d4524_uniq on api_currentreport (cost=22.94..10,831.66 rows=122,018 width=20) (actual time=0.308..90.366 rows=211,998 loops=1)

  • Index Cond: (account = '729650'::text)
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 31245
  • Heap Fetches: 0
8.          

SubPlan (for Index Only Scan)

9. 0.202 0.202 ↑ 1.0 351 1

Seq Scan on api_ack u2 (cost=0.00..21.50 rows=351 width=4) (actual time=0.006..0.202 rows=351 loops=1)

  • Filter: ((account)::text = '729650'::text)
  • Rows Removed by Filter: 649
10. 0.201 0.899 ↑ 1.0 1,073 1

Hash (cost=200.93..200.93 rows=1,073 width=8) (actual time=0.899..0.899 rows=1,073 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 58kB
11. 0.698 0.698 ↑ 1.0 1,073 1

Seq Scan on api_rule (cost=0.00..200.93 rows=1,073 width=8) (actual time=0.005..0.698 rows=1,073 loops=1)

  • Filter: active
  • Rows Removed by Filter: 120
12. 131.244 634.921 ↑ 1.0 534,184 1

Hash (cost=57,509.51..57,509.51 rows=534,184 width=16) (actual time=634.921..634.921 rows=534,184 loops=1)

  • Buckets: 131072 Batches: 16 Memory Usage: 2584kB
13. 85.279 503.677 ↑ 1.0 534,184 1

Subquery Scan on k (cost=0.42..57,509.51 rows=534,184 width=16) (actual time=0.011..503.677 rows=534,184 loops=1)

14. 253.900 418.398 ↑ 1.0 534,184 1

GroupAggregate (cost=0.42..52,167.68 rows=534,184 width=24) (actual time=0.011..418.398 rows=534,184 loops=1)

  • Group Key: api_hosttag_hosts.host_id
  • Filter: (count(*) >= 1)
15. 164.498 164.498 ↑ 1.0 534,184 1

Index Scan using api_hosttag_hosts_host_id_5f68428d on api_hosttag_hosts (cost=0.42..44,154.92 rows=534,184 width=16) (actual time=0.008..164.498 rows=534,184 loops=1)

  • Filter: (hosttag_id = ANY ('{1}'::integer[]))
Planning time : 0.966 ms
Execution time : 1,149.384 ms