explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aWar

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 106.261 ↑ 4.7 80 1

Sort (cost=36,295.85..36,296.79 rows=374 width=12) (actual time=106.257..106.261 rows=80 loops=1)

  • Sort Key: (count(cr.rule_id)) DESC
  • Sort Method: quicksort Memory: 28kB
2. 3.401 106.240 ↑ 4.7 80 1

GroupAggregate (cost=36,262.94..36,279.87 rows=374 width=12) (actual time=96.101..106.240 rows=80 loops=1)

  • Group Key: cr.rule_id
3. 4.452 102.839 ↓ 46.3 48,764 1

Merge Anti Join (cost=36,262.94..36,270.86 rows=1,054 width=4) (actual time=96.092..102.839 rows=48,764 loops=1)

  • Merge Cond: ((cr.rule_id = u0.rule_id) AND (cr.system_uuid = u0.system_uuid))
4. 9.372 98.372 ↓ 46.3 48,764 1

Sort (cost=36,254.63..36,257.27 rows=1,054 width=20) (actual time=96.074..98.372 rows=48,764 loops=1)

  • Sort Key: cr.rule_id, cr.system_uuid
  • Sort Method: quicksort Memory: 5,346kB
5. 10.124 89.000 ↓ 46.3 48,764 1

Hash Join (cost=27,221.64..36,201.71 rows=1,054 width=20) (actual time=27.633..89.000 rows=48,764 loops=1)

  • Hash Cond: (cr.system_uuid = hosts_v1_1.id)
6. 53.891 54.830 ↑ 1.7 49,140 1

Index Only Scan using api_currentreport_account_rule_id_system_uuid_f48d4524_uniq on api_currentreport cr (cost=299.95..9,061.50 rows=83,249 width=20) (actual time=3.571..54.830 rows=49,140 loops=1)

  • Index Cond: (account = '729650'::text)
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 127,650
  • Heap Fetches: 5,407
7.          

SubPlan (for Index Only Scan)

8. 0.195 0.939 ↑ 1.1 655 1

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

  • Group Key: api_rule.id
9. 0.070 0.744 ↑ 1.0 724 1

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

10. 0.503 0.503 ↑ 1.0 373 1

Seq Scan on api_rule (cost=0.00..250.05 rows=373 width=4) (actual time=0.004..0.503 rows=373 loops=1)

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

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

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 900
12. 2.615 24.046 ↓ 2.9 19,271 1

Hash (cost=26,838.98..26,838.98 rows=6,616 width=16) (actual time=24.046..24.046 rows=19,271 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1,160kB
13. 18.987 21.431 ↓ 2.9 19,271 1

Bitmap Heap Scan on hosts_v1_1 (cost=1,382.94..26,838.98 rows=6,616 width=16) (actual time=4.004..21.431 rows=19,271 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,768
14. 2.444 2.444 ↓ 1.0 20,289 1

Bitmap Index Scan on hosts_v1_1_account_index (cost=0.00..1,381.29 rows=19,849 width=0) (actual time=2.444..2.444 rows=20,289 loops=1)

  • Index Cond: ((account)::text = '729650'::text)
15. 0.006 0.015 ↓ 0.0 0 1

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

  • Sort Key: u0.rule_id, u0.system_uuid
  • Sort Method: quicksort Memory: 25kB
16. 0.009 0.009 ↓ 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.009..0.009 rows=0 loops=1)

  • Index Cond: ((account)::text = '729650'::text)
Planning time : 0.577 ms
Execution time : 106.343 ms