explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dFBN

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 677.274 ↑ 2.0 1 1

Unique (cost=19,527.27..19,527.30 rows=2 width=38) (actual time=677.274..677.274 rows=1 loops=1)

  • Buffers: shared hit=10 read=82814
2. 0.012 677.260 ↑ 2.0 1 1

Sort (cost=19,527.27..19,527.27 rows=2 width=38) (actual time=677.260..677.260 rows=1 loops=1)

  • Sort Key: b.id, a.id, a.account_number, b.number, b.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=10 read=82814
3. 0.002 677.248 ↑ 2.0 1 1

Append (cost=101.10..19,527.26 rows=2 width=38) (actual time=677.247..677.248 rows=1 loops=1)

  • Buffers: shared hit=10 read=82814
4. 0.001 0.106 ↓ 0.0 0 1

HashAggregate (cost=101.10..101.11 rows=1 width=38) (actual time=0.106..0.106 rows=0 loops=1)

  • Group Key: b.id, a.id, a.account_number, b.number, b.id
  • Buffers: shared hit=1 read=8
5. 0.013 0.105 ↓ 0.0 0 1

Hash Join (cost=86.88..101.09 rows=1 width=38) (actual time=0.105..0.105 rows=0 loops=1)

  • Hash Cond: ((b.custodial_provider_id = cp.id) AND ((c.custodial_provider_name)::text = (cp.name)::text))
  • Buffers: shared hit=1 read=8
6. 0.010 0.072 ↓ 0.0 0 1

Hash Join (cost=85.03..99.08 rows=20 width=50) (actual time=0.072..0.072 rows=0 loops=1)

  • Hash Cond: ((c.where_held_classic_id)::text = (a.where_held_classic_id)::text)
  • Buffers: shared hit=1 read=7
7. 0.007 0.007 ↑ 606.0 1 1

Seq Scan on provider_where_held_mapping c (cost=0.00..10.06 rows=606 width=9) (actual time=0.007..0.007 rows=1 loops=1)

  • Buffers: shared read=1
8. 0.000 0.055 ↓ 0.0 0 1

Hash (cost=84.85..84.85 rows=15 width=50) (actual time=0.055..0.055 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
  • Buffers: shared hit=1 read=6
9. 0.002 0.055 ↓ 0.0 0 1

Nested Loop (cost=5.00..84.85 rows=15 width=50) (actual time=0.055..0.055 rows=0 loops=1)

  • Buffers: shared hit=1 read=6
10. 0.026 0.026 ↑ 1.0 1 1

Index Scan using custodial_accounts_pkey on custodial_accounts b (cost=0.43..8.45 rows=1 width=28) (actual time=0.025..0.026 rows=1 loops=1)

  • Index Cond: (id = 3880101::bigint)
  • Buffers: shared read=4
11. 0.005 0.027 ↓ 0.0 0 1

Bitmap Heap Scan on accounts a (cost=4.57..76.22 rows=18 width=22) (actual time=0.027..0.027 rows=0 loops=1)

  • Recheck Cond: ((account_number)::text = (b.number)::text)
  • Buffers: shared hit=1 read=2
12. 0.022 0.022 ↓ 0.0 0 1

Bitmap Index Scan on index_accounts_on_account_number_where_held_classic_id (cost=0.00..4.56 rows=18 width=0) (actual time=0.022..0.022 rows=0 loops=1)

  • Index Cond: ((account_number)::text = (b.number)::text)
  • Buffers: shared hit=1 read=2
13. 0.007 0.020 ↓ 1.1 36 1

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

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

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

  • Buffers: shared read=1
15. 0.008 677.140 ↑ 1.0 1 1

HashAggregate (cost=19,426.11..19,426.12 rows=1 width=38) (actual time=677.139..677.140 rows=1 loops=1)

  • Group Key: b_1.id, a_1.id, a_1.account_number, b_1.number, b_1.id
  • Buffers: shared hit=9 read=82806
16. 43.576 677.132 ↑ 1.0 1 1

Nested Loop (cost=18,239.10..19,426.10 rows=1 width=38) (actual time=488.701..677.132 rows=1 loops=1)

  • Join Filter: (((b_1.number)::text = "left"((a_1.account_number)::text, (-9))) AND ((b_1.cusip)::text = "right"((a_1.account_number)::text, 9)))
  • Rows Removed by Join Filter: 129939
  • Buffers: shared hit=9 read=82806
17. 0.029 0.176 ↓ 2.0 2 1

Hash Join (cost=20.83..22.43 rows=1 width=34) (actual time=0.160..0.176 rows=2 loops=1)

  • Hash Cond: ((cp_1.id = b_1.custodial_provider_id) AND ((cp_1.name)::text = (c_1.custodial_provider_name)::text))
  • Buffers: shared hit=6 read=3
18. 0.004 0.004 ↓ 1.1 36 1

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

  • Buffers: shared hit=1
19. 0.002 0.143 ↓ 2.0 2 1

Hash (cost=20.81..20.81 rows=1 width=46) (actual time=0.143..0.143 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=5 read=3
20. 0.063 0.141 ↓ 2.0 2 1

Hash Join (cost=8.47..20.81 rows=1 width=46) (actual time=0.061..0.141 rows=2 loops=1)

  • Hash Cond: ((c_1.fund_company)::text = (b_1.where_held)::text)
  • Buffers: shared hit=5 read=3
21. 0.069 0.069 ↑ 1.0 606 1

Seq Scan on provider_where_held_mapping c_1 (cost=0.00..10.06 rows=606 width=14) (actual time=0.002..0.069 rows=606 loops=1)

  • Buffers: shared hit=1 read=3
22. 0.001 0.009 ↑ 1.0 1 1

Hash (cost=8.46..8.46 rows=1 width=40) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=4
23. 0.008 0.008 ↑ 1.0 1 1

Index Scan using custodial_accounts_pkey on custodial_accounts b_1 (cost=0.43..8.46 rows=1 width=40) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (id = 3880101::bigint)
  • Filter: ((where_held)::text = ANY ('{ALLNZACMAC,FTGFF,AMVAM,JHKJH,MASFM,PMCPM}'::text[]))
  • Buffers: shared hit=4
24. 361.586 633.380 ↓ 11.9 64,970 2

Bitmap Heap Scan on accounts a_1 (cost=18,218.27..19,294.69 rows=5,449 width=22) (actual time=139.566..316.690 rows=64,970 loops=2)

  • Recheck Cond: ((where_held_classic_id)::text = (c_1.where_held_classic_id)::text)
  • Rows Removed by Index Recheck: 182841
  • Heap Blocks: exact=32058 lossy=26786
  • Buffers: shared hit=3 read=82803
25. 271.794 271.794 ↓ 11.9 64,970 2

Bitmap Index Scan on index_accounts_on_account_number_where_held_classic_id (cost=0.00..18,216.91 rows=5,449 width=0) (actual time=135.897..135.897 rows=64,970 loops=2)

  • Index Cond: ((where_held_classic_id)::text = (c_1.where_held_classic_id)::text)
  • Buffers: shared hit=3 read=23959
Planning time : 2.452 ms
Execution time : 677.582 ms