explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MGCa0

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

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

  • Sort Key: (count(cr.system_uuid)) DESC
  • Sort Method: quicksort Memory: 28kB
2. 3.398 108.346 ↑ 4.7 80 1

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

  • Group Key: cr.rule_id
3. 4.753 104.948 ↓ 46.3 48,765 1

Merge Anti Join (cost=36,262.94..36,270.86 rows=1,054 width=20) (actual time=96.013..104.948 rows=48,765 loops=1)

  • Merge Cond: ((cr.rule_id = u0.rule_id) AND (cr.system_uuid = u0.system_uuid))
4. 14.012 100.180 ↓ 46.3 48,765 1

Sort (cost=36,254.63..36,257.27 rows=1,054 width=20) (actual time=95.995..100.180 rows=48,765 loops=1)

  • Sort Key: cr.rule_id, cr.system_uuid
  • Sort Method: external merge Disk: 1,432kB
5. 9.748 86.168 ↓ 46.3 48,765 1

Hash Join (cost=27,221.64..36,201.71 rows=1,054 width=20) (actual time=28.038..86.168 rows=48,765 loops=1)

  • Hash Cond: (cr.system_uuid = hosts_v1_1.id)
6. 50.875 51.815 ↑ 1.7 49,141 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.417..51.815 rows=49,141 loops=1)

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

SubPlan (for Index Only Scan)

8. 0.195 0.940 ↑ 1.1 655 1

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

  • Group Key: api_rule.id
9. 0.061 0.745 ↑ 1.0 724 1

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

10. 0.510 0.510 ↑ 1.0 373 1

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

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

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

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

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

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1,160kB
13. 19.254 21.688 ↓ 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.020..21.688 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.434 2.434 ↓ 1.0 20,209 1

Bitmap Index Scan on hosts_v1_1_account_index (cost=0.00..1,381.29 rows=19,849 width=0) (actual time=2.434..2.434 rows=20,209 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.583 ms
Execution time : 108.684 ms