explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nmdc

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 171.943 ↑ 4.8 80 1

Sort (cost=253,253.23..253,254.20 rows=387 width=12) (actual time=171.938..171.943 rows=80 loops=1)

  • Sort Key: (count(cr.system_uuid)) DESC
  • Sort Method: quicksort Memory: 28kB
2. 4.440 171.914 ↑ 4.8 80 1

GroupAggregate (cost=253,218.35..253,236.60 rows=387 width=12) (actual time=155.983..171.914 rows=80 loops=1)

  • Group Key: cr.rule_id
3. 5.937 167.474 ↓ 42.4 48,765 1

Merge Anti Join (cost=253,218.35..253,226.98 rows=1,149 width=20) (actual time=155.974..167.474 rows=48,765 loops=1)

  • Merge Cond: ((cr.rule_id = u0.rule_id) AND (cr.system_uuid = u0.system_uuid))
4. 18.386 161.521 ↓ 42.4 48,765 1

Sort (cost=253,210.04..253,212.91 rows=1,149 width=20) (actual time=155.954..161.521 rows=48,765 loops=1)

  • Sort Key: cr.rule_id, cr.system_uuid
  • Sort Method: external merge Disk: 1,432kB
5. 10.412 143.135 ↓ 42.4 48,765 1

Hash Join (cost=27,221.64..253,151.64 rows=1,149 width=20) (actual time=30.677..143.135 rows=48,765 loops=1)

  • Hash Cond: (cr.system_uuid = hosts_v1_1.id)
6. 107.561 108.507 ↑ 1.8 49,141 1

Index Only Scan using api_currentreport_account_rule_id_system_uuid_f48d4524_uniq on api_currentreport cr (cost=299.95..225,991.83 rows=90,714 width=20) (actual time=6.445..108.507 rows=49,141 loops=1)

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

SubPlan (for Index Only Scan)

8. 0.192 0.946 ↑ 1.1 655 1

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

  • Group Key: api_rule.id
9. 0.075 0.754 ↑ 1.0 724 1

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

10. 0.508 0.508 ↑ 1.0 373 1

Seq Scan on api_rule (cost=0.00..250.05 rows=373 width=4) (actual time=0.004..0.508 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.005..0.171 rows=351 loops=1)

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

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

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1,160kB
13. 19.109 21.547 ↓ 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.011..21.547 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,766
14. 2.438 2.438 ↓ 1.0 20,196 1

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

  • Index Cond: ((account)::text = '729650'::text)
15. 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
16. 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.585 ms
Execution time : 172.273 ms