explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Bp6w : pos30

Settings
# exclusive inclusive rows x rows loops node
1. 12.392 36,427.684 ↓ 7,779.0 7,779 1

WindowAgg (cost=2,744,566.64..2,744,566.71 rows=1 width=340) (actual time=36,407.604..36,427.684 rows=7,779 loops=1)

2. 31.690 36,415.292 ↓ 7,779.0 7,779 1

HashAggregate (cost=2,744,566.64..2,744,566.68 rows=1 width=340) (actual time=36,407.578..36,415.292 rows=7,779 loops=1)

  • Group Key: custodial_securities.cusip, custodial_securities.fund, custodial_securities.sub_fund, programs.program_type, workgroups.name, workgroups.code, reps.last_name, reps.first_name, rep_codes.code, rep_codes.rep_type_code, rep_codes.product_type_code, rep_codes.tier_class_code, contacts_classic_users.id, contacts.classic_urn, contacts.last_name, contacts.first_name, contacts_mvbalances_mvw.contact_market_value, contacts_mvbalances_mvw.contact_money_market_balance, custodial_accounts.number, accounts.acctype, accounts.title, held_wheres.id, contacts_mvbalances_mvw.account_market_value, contacts_mvbalances_mvw.account_money_market_balance, custodial_accounts.opened_on, custodial_securities.symbol, custodial_securities.full_description, custodial_cost_basis_details.cost_basis, custodial_asset_classes.id, custodial_asset_product_subgroups.id
3. 2.690 36,383.602 ↓ 7,790.0 7,790 1

Nested Loop Semi Join (cost=2,224,453.90..2,744,566.54 rows=1 width=340) (actual time=27,324.514..36,383.602 rows=7,790 loops=1)

  • Join Filter: (account_forms_collections.contact_id = contacts_1.id)
4. 3.729 36,357.542 ↓ 7,790.0 7,790 1

Nested Loop Left Join (cost=2,224,453.47..2,744,559.09 rows=1 width=356) (actual time=27,324.491..36,357.542 rows=7,790 loops=1)

5. 5.780 36,330.554 ↓ 7,753.0 7,753 1

Nested Loop Left Join (cost=2,224,453.05..2,744,551.68 rows=1 width=337) (actual time=27,324.475..36,330.554 rows=7,753 loops=1)

6. 12.896 36,317.021 ↓ 7,753.0 7,753 1

Nested Loop Left Join (cost=2,224,452.90..2,744,549.50 rows=1 width=320) (actual time=27,324.463..36,317.021 rows=7,753 loops=1)

  • Join Filter: (custodial_asset_classes.id = custodial_assets.custodial_asset_class_id)
  • Rows Removed by Join Filter: 69777
7. 7.353 36,296.372 ↓ 7,753.0 7,753 1

Nested Loop Left Join (cost=2,224,452.90..2,744,548.27 rows=1 width=309) (actual time=27,324.450..36,296.372 rows=7,753 loops=1)

8. 1.040 36,281.266 ↓ 7,753.0 7,753 1

Nested Loop Left Join (cost=2,224,452.62..2,744,541.54 rows=1 width=288) (actual time=27,324.437..36,281.266 rows=7,753 loops=1)

  • Join Filter: (custodial_securities.id = custodial_cost_basis_summaries.custodial_security_id)
9. 3.761 36,272.473 ↓ 7,753.0 7,753 1

Hash Join (cost=2,224,452.33..2,744,534.75 rows=1 width=282) (actual time=27,324.427..36,272.473 rows=7,753 loops=1)

  • Hash Cond: (owners_contacts_join.workgroup_id = workgroups.id)
10. 3.442 36,268.659 ↓ 68.6 7,753 1

Hash Left Join (cost=2,224,435.72..2,744,517.71 rows=113 width=256) (actual time=27,324.356..36,268.659 rows=7,753 loops=1)

  • Hash Cond: (securities_custodial_latest_positions_mvw.custodial_asset_id = custodial_assets.id)
11. 4.699 36,265.027 ↓ 68.6 7,753 1

Nested Loop Left Join (cost=2,224,417.05..2,744,497.48 rows=113 width=248) (actual time=27,324.152..36,265.027 rows=7,753 loops=1)

12. 7.544 36,229.316 ↓ 68.6 7,753 1

Nested Loop (cost=2,224,416.62..2,743,660.37 rows=113 width=248) (actual time=27,324.133..36,229.316 rows=7,753 loops=1)

13. 167.153 36,206.266 ↓ 39.8 7,753 1

Hash Join (cost=2,224,416.19..2,742,114.89 rows=195 width=233) (actual time=27,324.112..36,206.266 rows=7,753 loops=1)

  • Hash Cond: (contacts.rep_code_mapping_id = rep_code_mappings_contacts_join.id)
14. 806.837 36,038.967 ↓ 1.1 2,479,087 1

Hash Join (cost=2,224,360.59..2,733,970.01 rows=2,156,708 width=216) (actual time=27,070.904..36,038.967 rows=2,479,087 loops=1)

  • Hash Cond: (contacts.owner_id = owners_contacts_join.id)
15. 1,627.223 35,171.479 ↓ 1.1 2,479,087 1

Hash Join (cost=2,222,203.68..2,688,641.50 rows=2,160,453 width=208) (actual time=27,010.174..35,171.479 rows=2,479,087 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_security_id = custodial_securities.id)
16. 3,069.069 33,148.295 ↓ 1.1 2,490,000 1

Hash Join (cost=2,191,564.65..2,617,407.23 rows=2,170,365 width=151) (actual time=26,613.684..33,148.295 rows=2,490,000 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_account_id = custodial_accounts.id)
17. 3,468.594 3,468.594 ↓ 1.0 2,679,136 1

Seq Scan on custodial_latest_positions_mvw (cost=0.00..324,196.60 rows=2,669,773 width=41) (actual time=0.049..3,468.594 rows=2,679,136 loops=1)

  • Filter: ((valued_on >= '2019-12-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-11-30'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-10-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-09-30'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-08-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-07-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-12-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-11-30'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-10-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-09-30'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-08-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-07-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-12-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-11-30'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-10-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-09-30'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-08-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-07-31'::date) AND (valued_on <= '2020-01-31'::date))
  • Rows Removed by Filter: 196267
18. 1,134.370 26,610.632 ↓ 1.2 1,677,156 1

Hash (cost=2,150,695.21..2,150,695.21 rows=1,358,755 width=118) (actual time=26,610.632..26,610.632 rows=1,677,156 loops=1)

  • Buckets: 65536 Batches: 8 (originally 4) Memory Usage: 65537kB
19. 3,305.505 25,476.262 ↓ 1.2 1,677,156 1

Hash Join (cost=1,875,177.81..2,150,695.21 rows=1,358,755 width=118) (actual time=15,702.954..25,476.262 rows=1,677,156 loops=1)

  • Hash Cond: (accounts_custodial_accounts_mvw.custodial_account_id = custodial_accounts.id)
20. 3,255.101 20,177.373 ↓ 1.2 1,677,156 1

Hash Join (cost=1,656,028.60..1,837,808.34 rows=1,358,755 width=95) (actual time=13,705.787..20,177.373 rows=1,677,156 loops=1)

  • Hash Cond: (accounts.account_forms_collection_id = account_forms_collections.id)
21. 2,855.190 6,303.622 ↑ 1.0 1,677,371 1

Hash Join (cost=330,104.67..437,467.11 rows=1,677,581 width=36) (actual time=3,086.073..6,303.622 rows=1,677,371 loops=1)

  • Hash Cond: (accounts_custodial_accounts_mvw.account_id = accounts.id)
22. 365.327 365.327 ↑ 1.0 1,677,476 1

Seq Scan on accounts_custodial_accounts_mvw (cost=0.00..37,450.79 rows=1,677,581 width=16) (actual time=0.013..365.327 rows=1,677,476 loops=1)

  • Filter: ((number)::text <> ''::text)
  • Rows Removed by Filter: 107
23. 679.242 3,083.105 ↑ 1.0 2,823,876 1

Hash (cost=272,716.30..272,716.30 rows=2,825,230 width=36) (actual time=3,083.105..3,083.105 rows=2,823,876 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 44152kB
24. 2,403.863 2,403.863 ↑ 1.0 2,823,876 1

Seq Scan on accounts (cost=0.00..272,716.30 rows=2,825,230 width=36) (actual time=0.024..2,403.863 rows=2,823,876 loops=1)

25. 1,122.573 10,618.650 ↓ 1.0 1,076,054 1

Hash (cost=1,298,842.10..1,298,842.10 rows=1,074,866 width=75) (actual time=10,618.650..10,618.650 rows=1,076,054 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 59128kB
26. 4,168.088 9,496.077 ↓ 1.0 1,076,054 1

Hash Join (cost=51,136.36..1,298,842.10 rows=1,074,866 width=75) (actual time=507.151..9,496.077 rows=1,076,054 loops=1)

  • Hash Cond: (contacts.id = account_forms_collections.contact_id)
27. 4,821.553 4,821.553 ↑ 1.2 1,724,610 1

Seq Scan on contacts (cost=0.00..1,206,318.63 rows=2,042,563 width=59) (actual time=0.013..4,821.553 rows=1,724,610 loops=1)

28. 239.266 506.436 ↑ 1.2 1,076,054 1

Hash (cost=34,795.05..34,795.05 rows=1,307,305 width=16) (actual time=506.436..506.436 rows=1,076,054 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 50441kB
29. 267.170 267.170 ↓ 1.0 1,307,574 1

Seq Scan on account_forms_collections (cost=0.00..34,795.05 rows=1,307,305 width=16) (actual time=0.005..267.170 rows=1,307,574 loops=1)

30. 1,044.921 1,993.384 ↓ 1.0 4,862,209 1

Hash (cost=129,997.76..129,997.76 rows=4,855,876 width=23) (actual time=1,993.384..1,993.384 rows=4,862,209 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 33829kB
31. 948.463 948.463 ↓ 1.0 4,862,209 1

Seq Scan on custodial_accounts (cost=0.00..129,997.76 rows=4,855,876 width=23) (actual time=0.005..948.463 rows=4,862,209 loops=1)

32. 186.041 395.961 ↓ 1.0 657,249 1

Hash (cost=22,423.46..22,423.46 rows=657,246 width=57) (actual time=395.961..395.961 rows=657,249 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 46717kB
33. 209.920 209.920 ↓ 1.0 657,249 1

Seq Scan on custodial_securities (cost=0.00..22,423.46 rows=657,246 width=57) (actual time=0.014..209.920 rows=657,249 loops=1)

34. 4.663 60.651 ↓ 1.0 25,096 1

Hash (cost=1,844.18..1,844.18 rows=25,018 width=24) (actual time=60.651..60.651 rows=25,096 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 1209kB
35. 55.988 55.988 ↓ 1.0 25,096 1

Seq Scan on classic_users owners_contacts_join (cost=0.00..1,844.18 rows=25,018 width=24) (actual time=0.006..55.988 rows=25,096 loops=1)

36. 0.002 0.146 ↑ 1.0 2 1

Hash (cost=55.58..55.58 rows=2 width=33) (actual time=0.146..0.146 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
37. 0.000 0.144 ↑ 1.0 2 1

Nested Loop Left Join (cost=0.98..55.58 rows=2 width=33) (actual time=0.125..0.144 rows=2 loops=1)

38. 0.001 0.122 ↑ 1.0 2 1

Nested Loop (cost=0.70..38.96 rows=2 width=25) (actual time=0.105..0.122 rows=2 loops=1)

39. 0.083 0.083 ↑ 1.0 2 1

Index Scan using index_rep_codes_on_classic_urn on rep_codes (cost=0.41..16.09 rows=2 width=25) (actual time=0.050..0.083 rows=2 loops=1)

  • Index Cond: ((classic_urn)::text = ANY ('{RepCode:cfs-back-office:C35,RepCode:cfs-registration:C35}'::text[]))
40. 0.038 0.038 ↑ 2.0 1 2

Index Scan using index_rep_code_mappings_on_rep_code_id on rep_code_mappings rep_code_mappings_contacts_join (cost=0.29..11.42 rows=2 width=16) (actual time=0.011..0.019 rows=1 loops=2)

  • Index Cond: (rep_code_id = rep_codes.id)
41. 0.022 0.022 ↑ 1.0 1 2

Index Scan using reps_pkey on reps (cost=0.29..8.30 rows=1 width=24) (actual time=0.011..0.011 rows=1 loops=2)

  • Index Cond: (id = rep_codes.rep_id)
42. 15.506 15.506 ↑ 1.0 1 7,753

Index Scan using contacts_pkey on contacts contacts_classic_users (cost=0.43..7.92 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=7,753)

  • Index Cond: (id = owners_contacts_join.contact_id)
43. 31.012 31.012 ↑ 1.0 1 7,753

Index Scan using custodial_securities_pkey on custodial_securities securities_custodial_latest_positions_mvw (cost=0.42..7.40 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=7,753)

  • Index Cond: (id = custodial_latest_positions_mvw.custodial_security_id)
44. 0.097 0.190 ↓ 1.0 521 1

Hash (cost=12.19..12.19 rows=519 width=24) (actual time=0.190..0.190 rows=521 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
45. 0.093 0.093 ↓ 1.0 521 1

Seq Scan on custodial_assets (cost=0.00..12.19 rows=519 width=24) (actual time=0.007..0.093 rows=521 loops=1)

46. 0.002 0.053 ↑ 1.0 1 1

Hash (cost=16.60..16.60 rows=1 width=42) (actual time=0.053..0.053 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
47. 0.003 0.051 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.55..16.60 rows=1 width=42) (actual time=0.050..0.051 rows=1 loops=1)

48. 0.028 0.028 ↑ 1.0 1 1

Index Scan using index_workgroups_on_classic_urn on workgroups (cost=0.28..8.29 rows=1 width=37) (actual time=0.028..0.028 rows=1 loops=1)

  • Index Cond: ((classic_urn)::text = 'Workgroup:cfs:RCF'::text)
49. 0.020 0.020 ↑ 1.0 1 1

Index Scan using index_programs_on_workgroup_id on programs (cost=0.28..8.29 rows=1 width=13) (actual time=0.019..0.020 rows=1 loops=1)

  • Index Cond: (workgroup_id = workgroups.id)
50. 7.753 7.753 ↓ 0.0 0 7,753

Nested Loop (cost=0.29..6.77 rows=1 width=38) (actual time=0.001..0.001 rows=0 loops=7,753)

51. 0.000 0.000 ↓ 0.0 0 7,753

Index Scan using index_on_cus_cost_basis_acc_sec_and_portf_acc_type on custodial_cost_basis_summaries (cost=0.14..3.36 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=7,753)

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

Index Scan using index_on_cost_basis_record_identifier on custodial_cost_basis_details (cost=0.15..3.40 rows=1 width=30) (never executed)

  • Index Cond: (custodial_cost_basis_summary_id = custodial_cost_basis_summaries.id)
53. 7.753 7.753 ↑ 1.0 1 7,753

Index Scan using held_wheres_pkey on held_wheres (cost=0.28..6.73 rows=1 width=29) (actual time=0.001..0.001 rows=1 loops=7,753)

  • Index Cond: (id = accounts.held_where_id)
54. 7.753 7.753 ↑ 1.0 10 7,753

Seq Scan on custodial_asset_classes (cost=0.00..1.10 rows=10 width=19) (actual time=0.000..0.001 rows=10 loops=7,753)

55. 7.753 7.753 ↑ 1.0 1 7,753

Index Scan using custodial_asset_product_subgroups_pkey on custodial_asset_product_subgroups (cost=0.14..2.17 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=7,753)

  • Index Cond: (id = custodial_assets.custodial_asset_product_subgroup_id)
56. 23.259 23.259 ↑ 1.0 1 7,753

Index Scan using custodial_account_id_contacts_mvbalances_mvw on contacts_mvbalances_mvw (cost=0.43..7.40 rows=1 width=35) (actual time=0.003..0.003 rows=1 loops=7,753)

  • Index Cond: (custodial_account_id = accounts_custodial_accounts_mvw.custodial_account_id)
57. 23.370 23.370 ↑ 1.0 1 7,790

Index Scan using contacts_pkey on contacts contacts_1 (cost=0.43..7.44 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=7,790)

  • Index Cond: (id = contacts.id)
  • Filter: (realm_id = ANY ('{1,2,5,7,3,4}'::integer[]))