explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RLa6

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 119.912 1,424.266 ↑ 1.0 4 1

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

  • Group Key: api_rulecategory.name
2. 89.149 1,304.354 ↑ 1.3 136,431 1

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

  • Join Filter: (api_rule.category_id = api_rulecategory.id)
  • Rows Removed by Join Filter: 409293
3. 0.045 0.045 ↑ 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.009..0.045 rows=4 loops=1)

4. 106.180 1,215.160 ↑ 1.3 136,431 4

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

5. 208.302 1,108.980 ↑ 1.3 136,431 1

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

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

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

  • Hash Cond: (api_currentreport.rule_id = api_rule.id)
7. 114.688 114.886 ↓ 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.323..114.886 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.198 0.198 ↑ 1.0 351 1

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

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

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

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

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

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

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

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

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

14. 278.380 482.716 ↑ 1.0 534,184 1

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

  • Group Key: api_hosttag_hosts.host_id
  • Filter: (count(*) >= 1)
15. 204.336 204.336 ↑ 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.084..204.336 rows=534,184 loops=1)

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