explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MlKg

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.359 ↑ 2.0 1 1

Unique (cost=64.12..64.15 rows=2 width=26) (actual time=0.359..0.359 rows=1 loops=1)

  • Buffers: shared hit=12 read=12
2. 0.005 0.358 ↑ 2.0 1 1

Sort (cost=64.12..64.13 rows=2 width=26) (actual time=0.358..0.358 rows=1 loops=1)

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

Append (cost=33.18..64.11 rows=2 width=26) (actual time=0.352..0.353 rows=1 loops=1)

  • Buffers: shared hit=12 read=12
4. 0.000 0.213 ↓ 0.0 0 1

HashAggregate (cost=33.18..33.19 rows=1 width=26) (actual time=0.213..0.213 rows=0 loops=1)

  • Group Key: b.id, a.id, a.account_number, b.id
  • Buffers: shared read=8
5. 0.000 0.213 ↓ 0.0 0 1

Nested Loop (cost=8.89..33.17 rows=1 width=26) (actual time=0.213..0.213 rows=0 loops=1)

  • Join Filter: ((b.custodial_provider_id = cp.id) AND ((c.custodial_provider_name)::text = (cp.name)::text))
  • Buffers: shared read=8
6. 0.001 0.213 ↓ 0.0 0 1

Nested Loop (cost=8.89..31.32 rows=1 width=38) (actual time=0.213..0.213 rows=0 loops=1)

  • Buffers: shared read=8
7. 0.053 0.212 ↓ 0.0 0 1

Hash Join (cost=8.46..22.95 rows=1 width=37) (actual time=0.212..0.212 rows=0 loops=1)

  • Hash Cond: ((c.fund_company)::text = (b.where_held)::text)
  • Buffers: shared read=8
8. 0.122 0.122 ↑ 1.0 572 1

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

  • Filter: ((custodial_provider_name)::text = ANY ('{DAZL,DST,DTCC}'::text[]))
  • Rows Removed by Filter: 34
  • Buffers: shared read=4
9. 0.001 0.037 ↑ 1.0 1 1

Hash (cost=8.45..8.45 rows=1 width=31) (actual time=0.037..0.037 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared read=4
10. 0.036 0.036 ↑ 1.0 1 1

Index Scan using custodial_accounts_pkey on custodial_accounts b (cost=0.43..8.45 rows=1 width=31) (actual time=0.035..0.036 rows=1 loops=1)

  • Index Cond: (id = 123)
  • Buffers: shared read=4
11. 0.000 0.000 ↓ 0.0 0

Index Scan using index_accounts_on_account_number_where_held_classic_id on accounts a (cost=0.43..8.36 rows=1 width=22) (never executed)

  • Index Cond: (((account_number)::text = (b.number)::text) AND ((where_held_classic_id)::text = (c.where_held_classic_id)::text))
12. 0.000 0.000 ↓ 0.0 0

Seq Scan on custodial_providers cp (cost=0.00..1.34 rows=34 width=126) (never executed)

13. 0.002 0.138 ↑ 1.0 1 1

HashAggregate (cost=30.90..30.91 rows=1 width=26) (actual time=0.138..0.138 rows=1 loops=1)

  • Group Key: b_1.id, a_1.id, a_1.account_number, b_1.id
  • Buffers: shared hit=12 read=4
14. 0.004 0.136 ↑ 1.0 1 1

Nested Loop (cost=10.38..30.89 rows=1 width=26) (actual time=0.075..0.136 rows=1 loops=1)

  • Buffers: shared hit=12 read=4
15. 0.009 0.098 ↓ 2.0 2 1

Hash Join (cost=9.95..22.42 rows=1 width=25) (actual time=0.036..0.098 rows=2 loops=1)

  • Hash Cond: ((c_1.custodial_provider_name)::text = (cp_1.name)::text)
  • Buffers: shared hit=8 read=1
16. 0.066 0.066 ↑ 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.004..0.066 rows=34 loops=1)

  • Filter: ((custodial_provider_name)::text <> ALL ('{DAZL,DST,DTCC}'::text[]))
  • Rows Removed by Filter: 572
  • Buffers: shared hit=4
17. 0.001 0.023 ↑ 1.0 1 1

Hash (cost=9.94..9.94 rows=1 width=138) (actual time=0.023..0.023 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=4 read=1
18. 0.006 0.022 ↑ 1.0 1 1

Hash Join (cost=8.46..9.94 rows=1 width=138) (actual time=0.019..0.022 rows=1 loops=1)

  • Hash Cond: (cp_1.id = b_1.custodial_provider_id)
  • Buffers: shared hit=4 read=1
19. 0.011 0.011 ↓ 1.1 36 1

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

  • Buffers: shared read=1
20. 0.000 0.005 ↑ 1.0 1 1

Hash (cost=8.45..8.45 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=1)

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

Index Scan using custodial_accounts_pkey on custodial_accounts b_1 (cost=0.43..8.45 rows=1 width=28) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (id = 123)
  • Filter: (cusip IS NULL)
  • Buffers: shared hit=4
22. 0.034 0.034 ↓ 0.0 0 2

Index Scan using index_accounts_on_account_number_where_held_classic_id on accounts a_1 (cost=0.43..8.46 rows=1 width=22) (actual time=0.017..0.017 rows=0 loops=2)

  • Index Cond: (((account_number)::text = (b_1.number)::text) AND ((where_held_classic_id)::text = (c_1.where_held_classic_id)::text))
  • Buffers: shared hit=4 read=3
Planning time : 2.460 ms
Execution time : 0.563 ms