explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cewb

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

Limit (cost=37,101.92..37,101.95 rows=10 width=24) (actual time=129.526..129.528 rows=10 loops=1)

2. 2.096 129.526 ↑ 112.7 10 1

Sort (cost=37,101.92..37,104.74 rows=1,127 width=24) (actual time=129.525..129.526 rows=10 loops=1)

  • Sort Key: (count(cr.rule_id)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
3. 7.759 127.430 ↓ 16.8 18,962 1

GroupAggregate (cost=37,052.19..37,077.57 rows=1,127 width=24) (actual time=110.263..127.430 rows=18,962 loops=1)

  • Group Key: cr.system_uuid
4. 4.752 119.671 ↓ 43.4 48,908 1

Merge Anti Join (cost=37,052.19..37,060.66 rows=1,127 width=20) (actual time=110.255..119.671 rows=48,908 loops=1)

  • Merge Cond: ((cr.system_uuid = u0.system_uuid) AND (cr.rule_id = u0.rule_id))
5. 23.156 114.902 ↓ 43.4 48,908 1

Sort (cost=37,043.88..37,046.70 rows=1,127 width=20) (actual time=110.234..114.902 rows=48,908 loops=1)

  • Sort Key: cr.system_uuid, cr.rule_id
  • Sort Method: external merge Disk: 1,440kB
6. 12.193 91.746 ↓ 43.4 48,908 1

Hash Join (cost=26,753.93..36,986.76 rows=1,127 width=20) (actual time=29.913..91.746 rows=48,908 loops=1)

  • Hash Cond: (cr.system_uuid = hosts_v1_1.id)
7. 52.165 53.037 ↑ 1.9 49,219 1

Index Only Scan using api_currentreport_account_rule_id_system_uuid_f48d4524_uniq on api_currentreport cr (cost=299.71..10,284.82 rows=94,368 width=20) (actual time=3.381..53.037 rows=49,219 loops=1)

  • Index Cond: (account = '729650'::text)
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 126,519
  • Heap Fetches: 2,303
8.          

SubPlan (for Index Only Scan)

9. 0.195 0.872 ↑ 1.1 655 1

HashAggregate (cost=290.11..297.35 rows=724 width=4) (actual time=0.811..0.872 rows=655 loops=1)

  • Group Key: api_rule.id
10. 0.080 0.677 ↑ 1.0 724 1

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

11. 0.513 0.513 ↑ 1.0 373 1

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

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,032
12. 0.059 0.084 ↑ 1.0 351 1

Bitmap Heap Scan on api_ack (cost=11.00..27.39 rows=351 width=4) (actual time=0.030..0.084 rows=351 loops=1)

  • Recheck Cond: ((account)::text = '729650'::text)
  • Filter: (branch_id IS NULL)
  • Heap Blocks: exact=7
13. 0.025 0.025 ↑ 1.0 351 1

Bitmap Index Scan on api_ack_account_0fd242e7_like (cost=0.00..10.91 rows=351 width=0) (actual time=0.024..0.025 rows=351 loops=1)

  • Index Cond: ((account)::text = '729650'::text)
14. 3.153 26.516 ↓ 3.1 19,335 1

Hash (cost=26,375.88..26,375.88 rows=6,267 width=16) (actual time=26.515..26.516 rows=19,335 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1,163kB
15. 20.556 23.363 ↓ 3.1 19,335 1

Bitmap Heap Scan on hosts_v1_1 (cost=1,306.99..26,375.88 rows=6,267 width=16) (actual time=4.546..23.363 rows=19,335 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: 94
  • Heap Blocks: exact=12,813
16. 2.807 2.807 ↓ 1.1 20,035 1

Bitmap Index Scan on hosts_v1_1_account_index (cost=0.00..1,305.42 rows=18,800 width=0) (actual time=2.807..2.807 rows=20,035 loops=1)

  • Index Cond: ((account)::text = '729650'::text)
17. 0.007 0.017 ↓ 0.0 0 1

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

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

  • Index Cond: ((account)::text = '729650'::text)
Planning time : 0.594 ms
Execution time : 129.910 ms