explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c6UL

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 150.898 ↑ 1.0 10 1

Limit (cost=42,053.93..42,053.96 rows=10 width=12) (actual time=150.896..150.898 rows=10 loops=1)

2. 0.027 150.895 ↑ 37.3 10 1

Sort (cost=42,053.93..42,054.86 rows=373 width=12) (actual time=150.895..150.895 rows=10 loops=1)

  • Sort Key: (count(cr.rule_id)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
3. 7.983 150.868 ↑ 4.9 76 1

GroupAggregate (cost=42,027.93..42,045.87 rows=373 width=12) (actual time=130.678..150.868 rows=76 loops=1)

  • Group Key: cr.rule_id
4. 5.779 142.885 ↓ 42.8 48,595 1

Merge Anti Join (cost=42,027.93..42,036.47 rows=1,135 width=4) (actual time=130.671..142.885 rows=48,595 loops=1)

  • Merge Cond: ((cr.rule_id = u0.rule_id) AND (cr.system_uuid = u0.system_uuid))
5. 18.830 137.090 ↓ 42.8 48,595 1

Sort (cost=42,019.63..42,022.47 rows=1,135 width=20) (actual time=130.652..137.090 rows=48,595 loops=1)

  • Sort Key: cr.rule_id, cr.system_uuid
  • Sort Method: external merge Disk: 1,432kB
6. 21.376 118.260 ↓ 42.8 48,595 1

Hash Join (cost=34,662.75..41,962.04 rows=1,135 width=20) (actual time=32.397..118.260 rows=48,595 loops=1)

  • Hash Cond: (cr.system_uuid = hosts_v1_1.id)
7. 67.685 68.663 ↑ 1.8 49,734 1

Index Scan using api_currentreport2_account_index on api_currentreport2 cr (cost=290.82..7,348.74 rows=91,948 width=20) (actual time=4.131..68.663 rows=49,734 loops=1)

  • Index Cond: ((account)::text = '729650'::text)
  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 134,709
8.          

SubPlan (for Index Scan)

9. 0.274 0.978 ↑ 1.1 660 1

HashAggregate (cost=281.29..288.57 rows=728 width=4) (actual time=0.888..0.978 rows=660 loops=1)

  • Group Key: api_rule.id
10. 0.071 0.704 ↑ 1.0 728 1

Append (cost=0.00..279.47 rows=728 width=4) (actual time=0.005..0.704 rows=728 loops=1)

11. 0.399 0.399 ↑ 1.0 377 1

Seq Scan on api_rule (cost=0.00..241.05 rows=377 width=4) (actual time=0.004..0.399 rows=377 loops=1)

  • Filter: (NOT active)
  • Rows Removed by Filter: 1,028
12. 0.234 0.234 ↑ 1.0 351 1

Seq Scan on api_ack (cost=0.00..27.50 rows=351 width=4) (actual time=0.005..0.234 rows=351 loops=1)

  • Filter: ((branch_id IS NULL) AND ((account)::text = '729650'::text))
  • Rows Removed by Filter: 889
13. 4.184 28.221 ↓ 3.0 19,156 1

Hash (cost=34,291.03..34,291.03 rows=6,472 width=16) (actual time=28.221..28.221 rows=19,156 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1,154kB
14. 21.298 24.037 ↓ 3.0 19,156 1

Bitmap Heap Scan on hosts_v1_1 (cost=651.67..34,291.03 rows=6,472 width=16) (actual time=4.704..24.037 rows=19,156 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: 374
  • Heap Blocks: exact=12,016
15. 2.739 2.739 ↓ 1.0 19,531 1

Bitmap Index Scan on hosts_v1_1_account_index (cost=0.00..650.05 rows=19,417 width=0) (actual time=2.739..2.739 rows=19,531 loops=1)

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

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

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