explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 16Kr : Optimization for: Test_Query; plan #8IRJ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 39,224.288 ↓ 0.0 0 1

HashAggregate (cost=1,115,518.21..1,115,519.69 rows=99 width=158) (actual time=39,224.288..39,224.288 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=1926247 read=1940312 written=5
2. 0.000 39,224.286 ↓ 0.0 0 1

Nested Loop (cost=1,104,391.33..1,115,511.03 rows=99 width=158) (actual time=39,224.286..39,224.286 rows=0 loops=1)

  • Buffers: shared hit=1926247 read=1940312 written=5
3. 0.000 39,224.286 ↓ 0.0 0 1

Nested Loop (cost=1,104,390.22..1,115,491.78 rows=11 width=166) (actual time=39,224.286..39,224.286 rows=0 loops=1)

  • Buffers: shared hit=1926247 read=1940312 written=5
4. 0.001 39,224.286 ↓ 0.0 0 1

Nested Loop (cost=1,104,389.79..1,115,406.30 rows=11 width=162) (actual time=39,224.286..39,224.286 rows=0 loops=1)

  • Buffers: shared hit=1926247 read=1940312 written=5
5. 0.000 39,224.285 ↓ 0.0 0 1

Nested Loop (cost=1,104,389.23..1,113,722.04 rows=1 width=170) (actual time=39,224.285..39,224.285 rows=0 loops=1)

  • Buffers: shared hit=1926247 read=1940312 written=5
6. 0.000 39,224.285 ↓ 0.0 0 1

Nested Loop (cost=1,104,388.94..1,113,714.40 rows=1 width=160) (actual time=39,224.285..39,224.285 rows=0 loops=1)

  • Buffers: shared hit=1926247 read=1940312 written=5
7. 0.002 39,224.285 ↓ 0.0 0 1

Nested Loop (cost=1,104,388.66..1,113,706.81 rows=1 width=160) (actual time=39,224.285..39,224.285 rows=0 loops=1)

  • Buffers: shared hit=1926247 read=1940312 written=5
8. 0.000 39,224.283 ↓ 0.0 0 1

Nested Loop (cost=1,104,388.37..1,113,698.49 rows=1 width=160) (actual time=39,224.283..39,224.283 rows=0 loops=1)

  • Buffers: shared hit=1926247 read=1940312 written=5
9. 0.029 39,224.283 ↓ 0.0 0 1

Nested Loop (cost=1,104,387.94..1,113,690.03 rows=1 width=118) (actual time=39,224.283..39,224.283 rows=0 loops=1)

  • Buffers: shared hit=1926247 read=1940312 written=5
10. 0.015 39,224.140 ↓ 3.0 3 1

Nested Loop (cost=1,104,387.36..1,113,249.28 rows=1 width=24) (actual time=38,600.321..39,224.140 rows=3 loops=1)

  • Buffers: shared hit=1926244 read=1940300 written=5
11. 105.400 39,224.023 ↓ 3.0 3 1

Hash Join (cost=1,104,386.92..1,113,243.69 rows=1 width=16) (actual time=38,600.284..39,224.023 rows=3 loops=1)

  • Hash Cond: (a.id = accounts.id)
  • Buffers: shared hit=1926241 read=1940292 written=5
12. 2,319.747 39,118.564 ↓ 4.2 1,576,579 1

HashAggregate (cost=1,104,354.07..1,108,083.23 rows=372,916 width=26) (actual time=38,547.941..39,118.564 rows=1,576,579 loops=1)

  • Group Key: b.id, a.id, a.account_number, b.id
  • Buffers: shared hit=1926239 read=1940284 written=5
13. 6,162.150 36,798.817 ↓ 6.2 2,300,705 1

Merge Join (cost=594,635.09..1,100,624.91 rows=372,916 width=26) (actual time=19,410.657..36,798.817 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=1926239 read=1940284 written=5
14. 10,666.760 10,666.760 ↑ 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.007..10,666.760 rows=3,411,557 loops=1)

  • Filter: (cusip IS NULL)
  • Rows Removed by Filter: 809130
  • Buffers: shared hit=1926214 read=1783799 written=5
15. 18,459.710 19,969.907 ↑ 1.1 2,932,326 1

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

  • Sort Key: a.account_number, cp.id
  • Sort Method: quicksort Memory: 327547kB
  • Buffers: shared hit=25 read=156485
16. 805.540 1,510.197 ↑ 1.1 2,935,588 1

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

  • Hash Cond: ((a.where_held_classic_id)::text = (c.where_held_classic_id)::text)
  • Buffers: shared hit=25 read=156485
17. 704.315 704.315 ↑ 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.006..704.315 rows=2,416,513 loops=1)

  • Buffers: shared hit=25 read=156480
18. 0.073 0.342 ↑ 1.0 605 1

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

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

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

  • Hash Cond: ((c.custodial_provider_name)::text = (cp.name)::text)
  • Buffers: shared read=5
20. 0.064 0.064 ↑ 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.064 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.007..0.009 rows=34 loops=1)

  • Buffers: shared read=1
23. 0.003 0.059 ↓ 1.5 3 1

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

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

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

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

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

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

  • Index Cond: (id = b.id)
  • Heap Fetches: 1
  • Buffers: shared hit=3 read=8
28. 0.114 0.114 ↓ 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.038..0.038 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=12
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.511 ms
Execution time : 39,264.986 ms