explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WRbg : Optimization for: Test_Query; plan #8IRJ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 35,650.066 ↓ 0.0 0 1

HashAggregate (cost=1,115,522.54..1,115,524.02 rows=99 width=158) (actual time=35,650.066..35,650.066 rows=0 loops=1)

  • Group Key: custodial_positions.id, custodial_positions.custodial_account_id, custodial_positions.custodial_original_account_id, custodial_positions.custodial_security_id, custodial_positions.custodial_job_id, custodial_positions.quantity, custodial_positions.market_value, custodial_positions.segregation, custodial_positions.legal_quantity, custodial_positions.safe_quantity, custodial_positions.transfer_quantity, custodial_positions.holding_type, custodial_positions.traded_on, custodial_positions.valued_on, custodial_positions.settle_on, custodial_positions.priced_on, custodial_positions.short_on, custodial_positions.status, custodial_positions.fund_code, contacts.classic_urn, contacts.last_name, contacts.first_name, (((reps.last_name)::text || ' '::text) || (reps.first_name)::text), custodial_positions.market_value, custodial_positions.quantity, custodial_positions.quantity, custodial_positions.market_value, custodial_securities.symbol, custodial_securities.cusip
  • Buffers: shared hit=1935280 read=1931277 written=2
2. 0.001 35,650.065 ↓ 0.0 0 1

Nested Loop (cost=1,104,395.61..1,115,515.36 rows=99 width=158) (actual time=35,650.065..35,650.065 rows=0 loops=1)

  • Buffers: shared hit=1935280 read=1931277 written=2
3. 0.000 35,650.064 ↓ 0.0 0 1

Nested Loop (cost=1,104,394.50..1,115,496.11 rows=11 width=166) (actual time=35,650.064..35,650.064 rows=0 loops=1)

  • Buffers: shared hit=1935280 read=1931277 written=2
4. 0.000 35,650.064 ↓ 0.0 0 1

Nested Loop (cost=1,104,394.08..1,115,410.63 rows=11 width=162) (actual time=35,650.064..35,650.064 rows=0 loops=1)

  • Buffers: shared hit=1935280 read=1931277 written=2
5. 0.000 35,650.064 ↓ 0.0 0 1

Nested Loop (cost=1,104,393.51..1,113,726.37 rows=1 width=170) (actual time=35,650.064..35,650.064 rows=0 loops=1)

  • Buffers: shared hit=1935280 read=1931277 written=2
6. 0.002 35,650.064 ↓ 0.0 0 1

Nested Loop (cost=1,104,393.23..1,113,718.74 rows=1 width=160) (actual time=35,650.064..35,650.064 rows=0 loops=1)

  • Buffers: shared hit=1935280 read=1931277 written=2
7. 0.000 35,650.062 ↓ 0.0 0 1

Nested Loop (cost=1,104,392.94..1,113,711.14 rows=1 width=160) (actual time=35,650.062..35,650.062 rows=0 loops=1)

  • Buffers: shared hit=1935280 read=1931277 written=2
8. 0.001 35,650.062 ↓ 0.0 0 1

Nested Loop (cost=1,104,392.65..1,113,702.82 rows=1 width=160) (actual time=35,650.062..35,650.062 rows=0 loops=1)

  • Buffers: shared hit=1935280 read=1931277 written=2
9. 0.031 35,650.061 ↓ 0.0 0 1

Nested Loop (cost=1,104,392.22..1,113,694.36 rows=1 width=118) (actual time=35,650.061..35,650.061 rows=0 loops=1)

  • Buffers: shared hit=1935280 read=1931277 written=2
10. 0.011 35,649.964 ↓ 3.0 3 1

Nested Loop (cost=1,104,391.64..1,113,253.61 rows=1 width=24) (actual time=35,091.161..35,649.964 rows=3 loops=1)

  • Buffers: shared hit=1935277 read=1931268 written=2
11. 102.034 35,649.866 ↓ 3.0 3 1

Hash Join (cost=1,104,391.21..1,113,248.02 rows=1 width=16) (actual time=35,091.124..35,649.866 rows=3 loops=1)

  • Hash Cond: (a.id = accounts.id)
  • Buffers: shared hit=1935274 read=1931260 written=2
12. 1,867.401 35,547.774 ↓ 4.2 1,576,579 1

HashAggregate (cost=1,104,358.36..1,108,087.54 rows=372,918 width=26) (actual time=35,044.982..35,547.774 rows=1,576,579 loops=1)

  • Group Key: b.id, a.id, a.account_number, b.id
  • Buffers: shared hit=1935273 read=1931251 written=2
13. 5,795.018 33,680.373 ↓ 6.2 2,300,705 1

Merge Join (cost=594,639.20..1,100,629.18 rows=372,918 width=26) (actual time=18,692.915..33,680.373 rows=2,300,705 loops=1)

  • Merge Cond: (((b.number)::text = (a.account_number)::text) AND (b.custodial_provider_id = cp.id))
  • Buffers: shared hit=1935273 read=1931251 written=2
14. 8,758.535 8,758.535 ↑ 1.0 3,411,557 1

Index Scan using index_cust_accounts_on_cust_account_custodial_provider_id on custodial_accounts b (cost=0.43..464,919.00 rows=3,435,207 width=28) (actual time=0.005..8,758.535 rows=3,411,557 loops=1)

  • Filter: (cusip IS NULL)
  • Rows Removed by Filter: 809130
  • Buffers: shared hit=1935264 read=1774749 written=2
15. 17,765.840 19,126.820 ↑ 1.1 2,932,326 1

Sort (cost=591,044.89..599,212.95 rows=3,267,227 width=26) (actual time=18,692.901..19,126.820 rows=2,932,326 loops=1)

  • Sort Key: a.account_number, cp.id
  • Sort Method: quicksort Memory: 327547kB
  • Buffers: shared hit=9 read=156502
16. 745.040 1,360.980 ↑ 1.1 2,935,588 1

Hash Join (cost=27.73..237,536.88 rows=3,267,227 width=26) (actual time=0.318..1,360.980 rows=2,935,588 loops=1)

  • Hash Cond: ((a.where_held_classic_id)::text = (c.where_held_classic_id)::text)
  • Buffers: shared hit=9 read=156502
17. 615.633 615.633 ↑ 1.0 2,416,513 1

Seq Scan on accounts a (cost=0.00..180,671.44 rows=2,416,544 width=22) (actual time=0.006..615.633 rows=2,416,513 loops=1)

  • Buffers: shared hit=9 read=156497
18. 0.076 0.307 ↑ 1.0 605 1

Hash (cost=20.16..20.16 rows=606 width=13) (actual time=0.307..0.307 rows=605 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
  • Buffers: shared read=5
19. 0.167 0.231 ↑ 1.0 605 1

Hash Join (cost=1.77..20.16 rows=606 width=13) (actual time=0.027..0.231 rows=605 loops=1)

  • Hash Cond: ((c.custodial_provider_name)::text = (cp.name)::text)
  • Buffers: shared read=5
20. 0.051 0.051 ↑ 1.0 606 1

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

  • Buffers: shared read=4
21. 0.005 0.013 ↑ 1.0 34 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
  • Buffers: shared read=1
22. 0.008 0.008 ↑ 1.0 34 1

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

  • Buffers: shared read=1
23. 0.001 0.058 ↓ 1.5 3 1

Hash (cost=32.82..32.82 rows=2 width=16) (actual time=0.058..0.058 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=1 read=9
24. 0.002 0.057 ↓ 1.5 3 1

Nested Loop (cost=0.86..32.82 rows=2 width=16) (actual time=0.045..0.057 rows=3 loops=1)

  • Buffers: shared hit=1 read=9
25. 0.027 0.027 ↑ 1.0 1 1

Index Scan using index_account_forms_collections_on_contact_id on account_forms_collections (cost=0.43..8.45 rows=1 width=16) (actual time=0.027..0.027 rows=1 loops=1)

  • Index Cond: (contact_id = 177199)
  • Buffers: shared read=4
26. 0.028 0.028 ↑ 1.7 3 1

Index Scan using index_accounts_on_account_forms_collection_id on accounts (cost=0.43..24.33 rows=5 width=16) (actual time=0.016..0.028 rows=3 loops=1)

  • Index Cond: (account_forms_collection_id = account_forms_collections.id)
  • Buffers: shared hit=1 read=5
27. 0.087 0.087 ↑ 1.0 1 3

Index Only Scan using custodial_accounts_pkey on custodial_accounts (cost=0.43..5.58 rows=1 width=8) (actual time=0.029..0.029 rows=1 loops=3)

  • Index Cond: (id = b.id)
  • Heap Fetches: 1
  • Buffers: shared hit=3 read=8
28. 0.066 0.066 ↓ 0.0 0 3

Index Scan using index_custodial_positions_on_valued_on_custodial_account_id on custodial_positions (cost=0.58..439.52 rows=123 width=94) (actual time=0.022..0.022 rows=0 loops=3)

  • Index Cond: ((custodial_account_id = custodial_accounts.id) AND (valued_on > (('now'::cstring)::date - '90 days'::interval)) AND (valued_on <= ('now'::cstring)::date))
  • Filter: ((market_value IS NOT NULL) AND (quantity IS NOT NULL) AND (quantity <> 0.0))
  • Buffers: shared hit=3 read=9
29. 0.000 0.000 ↓ 0.0 0

Index Scan using contacts_pkey on contacts (cost=0.43..8.45 rows=1 width=50) (never executed)

  • Index Cond: (id = 177199)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using rep_code_mappings_pkey on rep_code_mappings (cost=0.29..8.31 rows=1 width=16) (never executed)

  • Index Cond: (id = contacts.rep_code_mapping_id)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using rep_codes_pkey on rep_codes (cost=0.29..7.59 rows=1 width=16) (never executed)

  • Index Cond: (id = rep_code_mappings.rep_code_id)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using reps_pkey on reps (cost=0.29..7.62 rows=1 width=26) (never executed)

  • Index Cond: (id = rep_codes.rep_id)
  • Filter: ((classic_urn)::text = 'Rep:cfs-registration:1666'::text)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using index_custodial_transactions_on_custodial_account_id on custodial_transactions (cost=0.56..1,679.40 rows=486 width=16) (never executed)

  • Index Cond: (custodial_account_id = custodial_accounts.id)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using custodial_securities_pkey on custodial_securities (cost=0.42..7.76 rows=1 width=20) (never executed)

  • Index Cond: (id = custodial_transactions.custodial_security_id)
35. 0.000 0.000 ↓ 0.0 0

Materialize (cost=1.11..17.54 rows=9 width=16) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.11..17.49 rows=9 width=16) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_addresses_on_owner_type_kind_and_position on addresses (cost=0.56..8.76 rows=3 width=8) (never executed)

  • Index Cond: ((owner_type = 'Contacts::Contact'::text) AND (owner_id = 177199))
  • Filter: ((type)::text = ANY ('{Contacts::Address,Org::ProgramAddress,Org::BranchAddress,Org::RepAddress}'::text[]))
  • Heap Fetches: 0
38. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.56..8.63 rows=3 width=8) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Index Only Scan using index_phones_on_owner_type_and_owner_id on phones (cost=0.56..8.62 rows=3 width=8) (never executed)

  • Index Cond: ((owner_type = 'Contacts::Contact'::text) AND (owner_id = 177199))
  • Heap Fetches: 0
Planning time : 4.305 ms
Execution time : 35,678.755 ms