explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bI8a

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

Limit (cost=37,078.53..37,078.55 rows=10 width=12) (actual time=111.499..111.501 rows=10 loops=1)

2. 0.018 111.499 ↑ 38.7 10 1

Sort (cost=37,078.53..37,079.50 rows=387 width=12) (actual time=111.498..111.499 rows=10 loops=1)

  • Sort Key: (count(cr.rule_id)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
3. 3.680 111.481 ↑ 4.8 80 1

GroupAggregate (cost=37,052.19..37,070.17 rows=387 width=12) (actual time=99.075..111.481 rows=80 loops=1)

  • Group Key: cr.rule_id
4. 4.494 107.801 ↓ 43.4 48,908 1

Merge Anti Join (cost=37,052.19..37,060.66 rows=1,127 width=4) (actual time=99.067..107.801 rows=48,908 loops=1)

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

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

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

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

  • Hash Cond: (cr.system_uuid = hosts_v1_1.id)
7. 51.651 52.558 ↑ 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.473..52.558 rows=49,219 loops=1)

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

SubPlan (for Index Only Scan)

9. 0.236 0.907 ↑ 1.1 655 1

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

  • Group Key: api_rule.id
10. 0.067 0.671 ↑ 1.0 724 1

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

11. 0.492 0.492 ↑ 1.0 373 1

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

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

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

  • Recheck Cond: ((account)::text = '729650'::text)
  • Filter: (branch_id IS NULL)
  • Heap Blocks: exact=7
13. 0.024 0.024 ↑ 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.024 rows=351 loops=1)

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

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

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1,163kB
15. 19.137 22.429 ↓ 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=5.069..22.429 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. 3.292 3.292 ↓ 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=3.292..3.292 rows=20,035 loops=1)

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

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

  • Sort Key: u0.rule_id, u0.system_uuid
  • Sort Method: quicksort Memory: 25kB
18. 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.571 ms
Execution time : 111.850 ms