explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ydURB

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 2,133.912 ↑ 2.0 1 1

Unique (cost=20,000,269,399.48..20,000,269,399.51 rows=2 width=38) (actual time=2,133.912..2,133.912 rows=1 loops=1)

  • Buffers: shared hit=11 read=164412 written=4722
2. 0.012 2,133.911 ↑ 2.0 1 1

Sort (cost=20,000,269,399.48..20,000,269,399.49 rows=2 width=38) (actual time=2,133.911..2,133.911 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=11 read=164412 written=4722
3. 0.002 2,133.899 ↑ 2.0 1 1

Append (cost=10,000,000,107.87..20,000,269,399.47 rows=2 width=38) (actual time=2,133.898..2,133.899 rows=1 loops=1)

  • Buffers: shared hit=11 read=164412 written=4722
4. 0.000 0.079 ↓ 0.0 0 1

HashAggregate (cost=10,000,000,107.87..10,000,000,107.88 rows=1 width=38) (actual time=0.079..0.079 rows=0 loops=1)

  • Group Key: b.id, a.id, a.account_number, b.number, b.id
  • Buffers: shared hit=2 read=7
5. 0.010 0.079 ↓ 0.0 0 1

Hash Join (cost=10,000,000,093.24..10,000,000,107.86 rows=1 width=38) (actual time=0.079..0.079 rows=0 loops=1)

  • Hash Cond: (((c.where_held_classic_id)::text = (a.where_held_classic_id)::text) AND ((c.custodial_provider_name)::text = (cp.name)::text))
  • Buffers: shared hit=2 read=7
6. 0.000 0.000 ↓ 0.0 0

Seq Scan on provider_where_held_mapping c (cost=10,000,000,000.00..10,000,000,010.06 rows=606 width=9) (never executed)

7. 0.001 0.069 ↓ 0.0 0 1

Hash (cost=93.02..93.02 rows=15 width=160) (actual time=0.069..0.069 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
  • Buffers: shared hit=2 read=7
8. 0.001 0.068 ↓ 0.0 0 1

Nested Loop (cost=5.14..93.02 rows=15 width=160) (actual time=0.068..0.068 rows=0 loops=1)

  • Buffers: shared hit=2 read=7
9. 0.004 0.043 ↑ 1.0 1 1

Nested Loop (cost=0.57..16.62 rows=1 width=138) (actual time=0.042..0.043 rows=1 loops=1)

  • Buffers: shared hit=1 read=5
10. 0.021 0.021 ↑ 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.021..0.021 rows=1 loops=1)

  • Index Cond: (id = 3880101::bigint)
  • Buffers: shared hit=1 read=3
11. 0.018 0.018 ↑ 1.0 1 1

Index Scan using custodial_providers_pkey on custodial_providers cp (cost=0.14..8.16 rows=1 width=126) (actual time=0.018..0.018 rows=1 loops=1)

  • Index Cond: (id = b.custodial_provider_id)
  • Buffers: shared read=2
12. 0.005 0.024 ↓ 0.0 0 1

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

  • Recheck Cond: ((account_number)::text = (b.number)::text)
  • Buffers: shared hit=1 read=2
13. 0.019 0.019 ↓ 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.019..0.019 rows=0 loops=1)

  • Index Cond: ((account_number)::text = (b.number)::text)
  • Buffers: shared hit=1 read=2
14. 0.007 2,133.818 ↑ 1.0 1 1

HashAggregate (cost=10,000,269,291.56..10,000,269,291.57 rows=1 width=38) (actual time=2,133.818..2,133.818 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=164405 written=4722
15. 0.117 2,133.811 ↑ 1.0 1 1

Hash Join (cost=10,000,269,274.66..10,000,269,291.55 rows=1 width=38) (actual time=2,133.730..2,133.811 rows=1 loops=1)

  • Hash Cond: (((c_1.fund_company)::text = (b_1.where_held)::text) AND ((c_1.where_held_classic_id)::text = (a_1.where_held_classic_id)::text) AND ((c_1.custodial_provider_name)::text = (cp_1.name)::text))
  • Buffers: shared hit=9 read=164405 written=4722
16. 0.055 0.055 ↑ 1.0 606 1

Seq Scan on provider_where_held_mapping c_1 (cost=10,000,000,000.00..10,000,000,010.06 rows=606 width=14) (actual time=0.010..0.055 rows=606 loops=1)

  • Buffers: shared read=4
17. 0.004 2,133.639 ↑ 1.0 1 1

Hash (cost=269,274.65..269,274.65 rows=1 width=163) (actual time=2,133.639..2,133.639 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=9 read=164401 written=4722
18. 0.006 2,133.635 ↑ 1.0 1 1

Nested Loop (cost=64,928.79..269,274.65 rows=1 width=163) (actual time=1,231.036..2,133.635 rows=1 loops=1)

  • Buffers: shared hit=9 read=164401 written=4722
19. 736.241 2,133.613 ↑ 1.0 1 1

Hash Join (cost=64,928.65..269,266.48 rows=1 width=53) (actual time=1,231.015..2,133.613 rows=1 loops=1)

  • Hash Cond: (("left"((a_1.account_number)::text, (-9)) = (b_1.number)::text) AND ("right"((a_1.account_number)::text, 9) = (b_1.cusip)::text))
  • Buffers: shared hit=9 read=164399 written=4722
20. 1,111.756 1,397.364 ↑ 1.0 2,342,366 1

Bitmap Heap Scan on accounts a_1 (cost=64,920.17..245,810.58 rows=2,344,741 width=22) (actual time=294.385..1,397.364 rows=2,342,366 loops=1)

  • Recheck Cond: (account_number IS NOT NULL)
  • Rows Removed by Index Recheck: 56332
  • Heap Blocks: exact=47077 lossy=105551
  • Buffers: shared hit=5 read=164399 written=4722
21. 285.608 285.608 ↑ 1.0 2,342,423 1

Bitmap Index Scan on index_accounts_on_account_number_where_held_classic_id (cost=0.00..64,333.99 rows=2,344,741 width=0) (actual time=285.608..285.608 rows=2,342,423 loops=1)

  • Index Cond: (account_number IS NOT NULL)
  • Buffers: shared hit=4 read=11772 written=1415
22. 0.003 0.008 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=4
23. 0.005 0.005 ↑ 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.005..0.005 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. 0.016 0.016 ↑ 1.0 1 1

Index Scan using custodial_providers_pkey on custodial_providers cp_1 (cost=0.14..8.16 rows=1 width=126) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: (id = b_1.custodial_provider_id)
  • Buffers: shared read=2
Planning time : 2.187 ms
Execution time : 2,134.168 ms