explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xzqn

Settings
# exclusive inclusive rows x rows loops node
1. 0.201 57,924.171 ↑ 113,206.0 1 1

HashAggregate (cost=802,625.53..803,757.59 rows=113,206 width=26) (actual time=57,924.058..57,924.171 rows=1 loops=1)

  • Group Key: (min(b.id)), a.id, a.account_number, (min(b.id))
  • Buffers: shared hit=14711886 read=3811448 written=36
2. 0.005 57,923.970 ↑ 113,206.0 1 1

Append (cost=318,686.52..801,493.47 rows=113,206 width=26) (actual time=57,923.862..57,923.970 rows=1 loops=1)

  • Buffers: shared hit=14711886 read=3811448 written=36
3. 0.009 34,286.274 ↓ 0.0 0 1

HashAggregate (cost=318,686.52..318,727.24 rows=4,072 width=26) (actual time=34,286.274..34,286.274 rows=0 loops=1)

  • Group Key: min(b.id), a.id, a.account_number, min(b.id)
  • Buffers: shared hit=10412854 read=1425874 written=20
4. 1,371.342 34,286.265 ↓ 0.0 0 1

HashAggregate (cost=318,594.90..318,645.80 rows=4,072 width=26) (actual time=34,286.265..34,286.265 rows=0 loops=1)

  • Group Key: a.id, a.account_number
  • Filter: (min(b.id) = 123)
  • Rows Removed by Filter: 867404
  • Buffers: shared hit=10412854 read=1425874 written=20
5. 0.000 32,914.923 ↓ 297.8 1,212,828 1

Nested Loop (cost=30.97..318,544.00 rows=4,072 width=26) (actual time=0.412..32,914.923 rows=1,212,828 loops=1)

  • Join Filter: ((c.where_held_classic_id)::text = (a.where_held_classic_id)::text)
  • Rows Removed by Join Filter: 1186445
  • Buffers: shared hit=10412854 read=1425874 written=20
6. 1,320.971 1,940.382 ↓ 25.5 3,110,386 1

Hash Join (cost=30.54..176,622.64 rows=122,214 width=25) (actual time=0.382..1,940.382 rows=3,110,386 loops=1)

  • Hash Cond: (((b.where_held)::text = (c.fund_company)::text) AND (b.custodial_provider_id = cp.id))
  • Buffers: shared read=82661
7. 619.049 619.049 ↑ 1.0 4,521,544 1

Seq Scan on custodial_accounts b (cost=0.00..129,012.98 rows=4,635,698 width=31) (actual time=0.011..619.049 rows=4,521,544 loops=1)

  • Buffers: shared read=82656
8. 0.104 0.362 ↑ 1.0 572 1

Hash (cost=21.96..21.96 rows=572 width=18) (actual time=0.362..0.362 rows=572 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
  • Buffers: shared read=5
9. 0.136 0.258 ↑ 1.0 572 1

Hash Join (cost=1.77..21.96 rows=572 width=18) (actual time=0.038..0.258 rows=572 loops=1)

  • Hash Cond: ((c.custodial_provider_name)::text = (cp.name)::text)
  • Buffers: shared read=5
10. 0.106 0.106 ↑ 1.0 572 1

Seq Scan on provider_where_held_mapping c (cost=0.00..12.33 rows=572 width=14) (actual time=0.014..0.106 rows=572 loops=1)

  • Filter: ((custodial_provider_name)::text = ANY ('{DAZL,DST,DTCC}'::text[]))
  • Rows Removed by Filter: 34
  • Buffers: shared read=4
11. 0.005 0.016 ↓ 1.1 36 1

Hash (cost=1.34..1.34 rows=34 width=126) (actual time=0.016..0.016 rows=36 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
  • Buffers: shared read=1
12. 0.011 0.011 ↓ 1.1 36 1

Seq Scan on custodial_providers cp (cost=0.00..1.34 rows=34 width=126) (actual time=0.007..0.011 rows=36 loops=1)

  • Buffers: shared read=1
13. 31,103.860 31,103.860 ↑ 18.0 1 3,110,386

Index Scan using index_accounts_on_account_number_where_held_classic_id on accounts a (cost=0.43..0.94 rows=18 width=22) (actual time=0.009..0.010 rows=1 loops=3,110,386)

  • Index Cond: ((account_number)::text = (b.number)::text)
  • Buffers: shared hit=10412854 read=1343213 written=20
14. 0.204 23,637.691 ↑ 109,134.0 1 1

HashAggregate (cost=480,542.83..481,634.17 rows=109,134 width=26) (actual time=23,637.584..23,637.691 rows=1 loops=1)

  • Group Key: min(b_1.id), a_1.id, a_1.account_number, min(b_1.id)
  • Buffers: shared hit=4299032 read=2385574 written=16
15. 1,071.613 23,637.487 ↑ 109,134.0 1 1

HashAggregate (cost=478,087.31..479,451.49 rows=109,134 width=26) (actual time=23,587.553..23,637.487 rows=1 loops=1)

  • Group Key: a_1.id, a_1.account_number
  • Filter: (min(b_1.id) = 123)
  • Rows Removed by Filter: 617335
  • Buffers: shared hit=4299032 read=2385574 written=16
16. 83.276 22,565.874 ↓ 9.8 1,065,858 1

Nested Loop (cost=15.42..476,723.14 rows=109,134 width=26) (actual time=0.306..22,565.874 rows=1,065,858 loops=1)

  • Join Filter: (cp_1.id = b_1.custodial_provider_id)
  • Rows Removed by Join Filter: 969878
  • Buffers: shared hit=4299032 read=2385574 written=16
17. 555.811 1,382.708 ↓ 6.9 1,241,170 1

Hash Join (cost=14.99..207,632.78 rows=180,967 width=26) (actual time=0.166..1,382.708 rows=1,241,170 loops=1)

  • Hash Cond: ((a_1.where_held_classic_id)::text = (c_1.where_held_classic_id)::text)
  • Buffers: shared hit=426 read=157022
18. 826.771 826.771 ↑ 1.0 2,416,517 1

Seq Scan on accounts a_1 (cost=0.00..181,625.56 rows=2,418,256 width=22) (actual time=0.016..826.771 rows=2,416,517 loops=1)

  • Buffers: shared hit=426 read=157017
19. 0.011 0.126 ↑ 1.0 33 1

Hash (cost=14.57..14.57 rows=34 width=13) (actual time=0.126..0.126 rows=33 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
  • Buffers: shared read=5
20. 0.010 0.115 ↑ 1.0 33 1

Hash Join (cost=1.77..14.57 rows=34 width=13) (actual time=0.037..0.115 rows=33 loops=1)

  • Hash Cond: ((c_1.custodial_provider_name)::text = (cp_1.name)::text)
  • Buffers: shared read=5
21. 0.089 0.089 ↑ 1.0 34 1

Seq Scan on provider_where_held_mapping c_1 (cost=0.00..12.33 rows=34 width=9) (actual time=0.016..0.089 rows=34 loops=1)

  • Filter: ((custodial_provider_name)::text <> ALL ('{DAZL,DST,DTCC}'::text[]))
  • Rows Removed by Filter: 572
  • Buffers: shared read=4
22. 0.006 0.016 ↓ 1.1 36 1

Hash (cost=1.34..1.34 rows=34 width=126) (actual time=0.016..0.016 rows=36 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
  • Buffers: shared read=1
23. 0.010 0.010 ↓ 1.1 36 1

Seq Scan on custodial_providers cp_1 (cost=0.00..1.34 rows=34 width=126) (actual time=0.006..0.010 rows=36 loops=1)

  • Buffers: shared read=1
24. 21,099.890 21,099.890 ↑ 10.5 2 1,241,170

Index Scan using index_cust_accounts_on_cust_account on custodial_accounts b_1 (cost=0.43..1.22 rows=21 width=28) (actual time=0.012..0.017 rows=2 loops=1,241,170)

  • Index Cond: ((number)::text = (a_1.account_number)::text)
  • Filter: (cusip IS NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4298606 read=2228552 written=16
Planning time : 2.723 ms
Execution time : 57,925.387 ms