explain.depesz.com

PostgreSQL's explain analyze made readable

Result: M31r : Test_Query

Settings
# exclusive inclusive rows x rows loops node
1. 2.036 38,793.749 ↓ 1.3 132 1

HashAggregate (cost=1,118,800.78..1,118,802.27 rows=99 width=158) (actual time=38,793.703..38,793.749 rows=132 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=1917312 read=1931936 written=23
2. 0.333 38,791.713 ↓ 13.7 1,355 1

Nested Loop (cost=1,103,619.35..1,118,793.61 rows=99 width=158) (actual time=37,956.987..38,791.713 rows=1,355 loops=1)

  • Buffers: shared hit=1917312 read=1931936 written=23
3. 0.026 38,791.109 ↓ 24.6 271 1

Nested Loop (cost=1,103,618.23..1,118,774.36 rows=11 width=166) (actual time=37,956.850..38,791.109 rows=271 loops=1)

  • Buffers: shared hit=1917311 read=1931925 written=23
4. 0.065 38,790.541 ↓ 24.6 271 1

Nested Loop (cost=1,103,617.81..1,118,688.89 rows=11 width=162) (actual time=37,956.825..38,790.541 rows=271 loops=1)

  • Buffers: shared hit=1916235 read=1931917 written=23
5. 0.078 38,788.876 ↓ 160.0 160 1

Hash Join (cost=1,103,617.25..1,117,008.15 rows=1 width=170) (actual time=37,956.793..38,788.876 rows=160 loops=1)

  • Hash Cond: (rep_codes.rep_id = reps.id)
  • Buffers: shared hit=1915341 read=1931900 written=23
6. 0.049 38,788.759 ↓ 17.8 160 1

Nested Loop (cost=1,103,608.93..1,116,999.79 rows=9 width=160) (actual time=37,956.742..38,788.759 rows=160 loops=1)

  • Buffers: shared hit=1915341 read=1931897 written=23
7. 0.008 38,777.343 ↓ 3.0 3 1

Nested Loop (cost=1,103,608.36..1,112,493.65 rows=1 width=66) (actual time=37,956.704..38,777.343 rows=3 loops=1)

  • Buffers: shared hit=1915341 read=1931724 written=23
8. 0.011 38,777.281 ↓ 3.0 3 1

Nested Loop (cost=1,103,608.07..1,112,486.05 rows=1 width=66) (actual time=37,956.685..38,777.281 rows=3 loops=1)

  • Buffers: shared hit=1915341 read=1931715 written=23
9. 0.009 38,777.213 ↓ 3.0 3 1

Nested Loop (cost=1,103,607.79..1,112,477.74 rows=1 width=66) (actual time=37,956.663..38,777.213 rows=3 loops=1)

  • Buffers: shared hit=1915341 read=1931706 written=23
10. 0.012 38,777.126 ↓ 3.0 3 1

Nested Loop (cost=1,103,607.36..1,112,469.28 rows=1 width=24) (actual time=37,956.634..38,777.126 rows=3 loops=1)

  • Buffers: shared hit=1915337 read=1931698 written=23
11. 123.796 38,777.009 ↓ 3.0 3 1

Hash Join (cost=1,103,606.92..1,112,463.69 rows=1 width=16) (actual time=37,956.594..38,777.009 rows=3 loops=1)

  • Hash Cond: (a.id = accounts.id)
  • Buffers: shared hit=1915337 read=1931687 written=23
12. 2,197.550 38,653.141 ↓ 4.2 1,576,579 1

HashAggregate (cost=1,103,574.07..1,107,303.23 rows=372,916 width=26) (actual time=37,913.310..38,653.141 rows=1,576,579 loops=1)

  • Group Key: b.id, a.id, a.account_number, b.id
  • Buffers: shared hit=1915335 read=1931679 written=23
13. 6,383.495 36,455.591 ↓ 6.2 2,300,705 1

Merge Join (cost=594,635.09..1,099,844.91 rows=372,916 width=26) (actual time=18,391.029..36,455.591 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=1915335 read=1931679 written=23
14. 11,224.557 11,224.557 ↑ 1.0 3,411,544 1

Index Scan using index_cust_accounts_on_cust_account_custodial_provider_id on custodial_accounts b (cost=0.43..464,139.00 rows=3,435,207 width=28) (actual time=0.006..11,224.557 rows=3,411,544 loops=1)

  • Filter: (cusip IS NULL)
  • Rows Removed by Filter: 809130
  • Buffers: shared hit=1915090 read=1775414 written=23
15. 17,417.743 18,847.539 ↑ 1.1 2,932,326 1

Sort (cost=591,040.81..599,208.82 rows=3,267,205 width=26) (actual time=18,391.015..18,847.539 rows=2,932,326 loops=1)

  • Sort Key: a.account_number, cp.id
  • Sort Method: quicksort Memory: 327547kB
  • Buffers: shared hit=245 read=156265
16. 793.473 1,429.796 ↑ 1.1 2,935,588 1

Hash Join (cost=27.73..237,535.34 rows=3,267,205 width=26) (actual time=0.361..1,429.796 rows=2,935,588 loops=1)

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

Seq Scan on accounts a (cost=0.00..180,670.28 rows=2,416,528 width=22) (actual time=0.007..635.982 rows=2,416,513 loops=1)

  • Buffers: shared hit=245 read=156260
18. 0.102 0.341 ↑ 1.0 605 1

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

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

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

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

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

  • Buffers: shared read=4
21. 0.006 0.015 ↑ 1.0 34 1

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

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared hit=2 read=8
24. 0.003 0.071 ↓ 1.5 3 1

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

  • Buffers: shared hit=2 read=8
25. 0.019 0.019 ↑ 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.019..0.019 rows=1 loops=1)

  • Index Cond: (contact_id = 177199)
  • Buffers: shared hit=1 read=3
26. 0.049 0.049 ↑ 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.040..0.049 rows=3 loops=1)

  • Index Cond: (account_forms_collection_id = account_forms_collections.id)
  • Buffers: shared hit=1 read=5
27. 0.105 0.105 ↑ 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.034..0.035 rows=1 loops=3)

  • Index Cond: (id = b.id)
  • Heap Fetches: 1
  • Buffers: shared read=11
28. 0.078 0.078 ↑ 1.0 1 3

Index Scan using contacts_pkey on contacts (cost=0.43..8.45 rows=1 width=50) (actual time=0.026..0.026 rows=1 loops=3)

  • Index Cond: (id = 177199)
  • Buffers: shared hit=4 read=8
29. 0.057 0.057 ↑ 1.0 1 3

Index Scan using rep_code_mappings_pkey on rep_code_mappings (cost=0.29..8.31 rows=1 width=16) (actual time=0.019..0.019 rows=1 loops=3)

  • Index Cond: (id = contacts.rep_code_mapping_id)
  • Buffers: shared read=9
30. 0.054 0.054 ↑ 1.0 1 3

Index Scan using rep_codes_pkey on rep_codes (cost=0.29..7.59 rows=1 width=16) (actual time=0.018..0.018 rows=1 loops=3)

  • Index Cond: (id = rep_code_mappings.rep_code_id)
  • Buffers: shared read=9
31. 11.367 11.367 ↑ 26.6 53 3

Index Scan using index_custodial_positions_on_custodial_account_id on custodial_positions (cost=0.57..4,492.03 rows=1,411 width=94) (actual time=0.035..3.789 rows=53 loops=3)

  • Index Cond: (custodial_account_id = custodial_accounts.id)
  • Filter: ((market_value IS NOT NULL) AND (quantity IS NOT NULL) AND (quantity <> 0.0))
  • Rows Removed by Filter: 19
  • Buffers: shared read=173
32. 0.001 0.039 ↑ 1.0 1 1

Hash (cost=8.30..8.30 rows=1 width=26) (actual time=0.039..0.039 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
  • Buffers: shared read=3
33. 0.038 0.038 ↑ 1.0 1 1

Index Scan using index_reps_on_classic_urn on reps (cost=0.29..8.30 rows=1 width=26) (actual time=0.037..0.038 rows=1 loops=1)

  • Index Cond: ((classic_urn)::text = 'Rep:cfs-registration:1666'::text)
  • Buffers: shared read=3
34. 1.600 1.600 ↑ 242.5 2 160

Index Scan using index_custodial_transactions_on_custodial_account_id on custodial_transactions (cost=0.56..1,675.88 rows=485 width=16) (actual time=0.009..0.010 rows=2 loops=160)

  • Index Cond: (custodial_account_id = custodial_accounts.id)
  • Buffers: shared hit=894 read=17
35. 0.542 0.542 ↑ 1.0 1 271

Index Scan using custodial_securities_pkey on custodial_securities (cost=0.42..7.76 rows=1 width=20) (actual time=0.001..0.002 rows=1 loops=271)

  • Index Cond: (id = custodial_transactions.custodial_security_id)
  • Buffers: shared hit=1076 read=8
36. 0.134 0.271 ↑ 1.8 5 271

Materialize (cost=1.11..17.54 rows=9 width=16) (actual time=0.001..0.001 rows=5 loops=271)

  • Buffers: shared hit=1 read=11
37. 0.001 0.137 ↑ 1.8 5 1

Nested Loop (cost=1.11..17.49 rows=9 width=16) (actual time=0.129..0.137 rows=5 loops=1)

  • Buffers: shared hit=1 read=11
38. 0.078 0.078 ↑ 3.0 1 1

Index Only Scan using index_addresses_on_owner_type_kind_and_position on addresses (cost=0.56..8.76 rows=3 width=8) (actual time=0.072..0.078 rows=1 loops=1)

  • Index Cond: ((owner_type = 'Contacts::Contact'::text) AND (owner_id = 177199))
  • Filter: ((type)::text = ANY ('{Contacts::Address,Org::ProgramAddress,Org::BranchAddress,Org::RepAddress}'::text[]))
  • Rows Removed by Filter: 4
  • Heap Fetches: 1
  • Buffers: shared read=7
39. 0.003 0.058 ↓ 1.7 5 1

Materialize (cost=0.56..8.63 rows=3 width=8) (actual time=0.056..0.058 rows=5 loops=1)

  • Buffers: shared hit=1 read=4
40. 0.055 0.055 ↓ 1.7 5 1

Index Only Scan using index_phones_on_owner_type_and_owner_id on phones (cost=0.56..8.62 rows=3 width=8) (actual time=0.054..0.055 rows=5 loops=1)

  • Index Cond: ((owner_type = 'Contacts::Contact'::text) AND (owner_id = 177199))
  • Heap Fetches: 0
  • Buffers: shared hit=1 read=4
Planning time : 4.332 ms
Execution time : 38,810.511 ms