explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MvM9

Settings
# exclusive inclusive rows x rows loops node
1. 3.914 33,146.029 ↓ 3,378.0 3,378 1

WindowAgg (cost=4,018,475.24..4,018,475.29 rows=1 width=315) (actual time=33,140.081..33,146.029 rows=3,378 loops=1)

2. 10.105 33,142.115 ↓ 3,378.0 3,378 1

HashAggregate (cost=4,018,475.24..4,018,475.26 rows=1 width=315) (actual time=33,140.062..33,142.115 rows=3,378 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.583 33,132.010 ↓ 3,386.0 3,386 1

Nested Loop Semi Join (cost=3,453,453.32..4,018,475.17 rows=1 width=315) (actual time=25,361.624..33,132.010 rows=3,386 loops=1)

  • Join Filter: (account_forms_collections.contact_id = contacts_1.id)
4. 1.342 33,119.269 ↓ 3,386.0 3,386 1

Nested Loop Left Join (cost=3,453,452.89..4,018,467.72 rows=1 width=331) (actual time=25,361.605..33,119.269 rows=3,386 loops=1)

5. 1.488 33,107.808 ↓ 3,373.0 3,373 1

Nested Loop Left Join (cost=3,453,452.46..4,018,460.31 rows=1 width=312) (actual time=25,361.594..33,107.808 rows=3,373 loops=1)

6. 0.723 33,099.574 ↓ 3,373.0 3,373 1

Nested Loop Left Join (cost=3,453,452.18..4,018,452.00 rows=1 width=304) (actual time=25,361.583..33,099.574 rows=3,373 loops=1)

7. 1.781 33,092.105 ↓ 3,373.0 3,373 1

Hash Join (cost=3,453,452.04..4,018,449.81 rows=1 width=287) (actual time=25,361.570..33,092.105 rows=3,373 loops=1)

  • Hash Cond: (owners_contacts_join.workgroup_id = workgroups.id)
8. 134.473 33,090.297 ↓ 27.2 3,373 1

Hash Join (cost=3,453,435.43..4,018,432.73 rows=124 width=261) (actual time=25,361.529..33,090.297 rows=3,373 loops=1)

  • Hash Cond: (contacts.rep_code_mapping_id = rep_code_mappings_contacts_join.id)
9. 472.724 32,955.773 ↓ 1.3 1,825,309 1

Hash Left Join (cost=3,453,396.45..4,013,243.67 rows=1,373,082 width=252) (actual time=25,263.179..32,955.773 rows=1,825,309 loops=1)

  • Hash Cond: (accounts.held_where_id = held_wheres.id)
10. 318.301 32,481.157 ↓ 1.3 1,825,309 1

Hash Left Join (cost=3,453,322.30..3,996,060.46 rows=1,373,082 width=231) (actual time=25,261.273..32,481.157 rows=1,825,309 loops=1)

  • Hash Cond: ((custodial_accounts.id = custodial_cost_basis_summaries.custodial_account_id) AND (custodial_securities.id = custodial_cost_basis_summaries.custodial_security_id))
11. 1,206.640 32,162.854 ↓ 1.3 1,825,309 1

Hash Left Join (cost=3,453,285.66..3,889,609.96 rows=1,373,082 width=225) (actual time=25,261.255..32,162.854 rows=1,825,309 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_security_id = securities_custodial_latest_positions_mvw.id)
12. 1,449.303 30,587.191 ↓ 1.3 1,825,309 1

Hash Join (cost=3,413,576.63..3,824,189.05 rows=1,373,082 width=206) (actual time=24,892.075..30,587.191 rows=1,825,309 loops=1)

  • Hash Cond: (owners_contacts_join.contact_id = contacts_classic_users.id)
13. 654.108 24,553.223 ↑ 1.3 1,831,138 1

Hash Join (cost=2,178,947.87..2,413,911.21 rows=2,373,179 width=191) (actual time=20,305.994..24,553.223 rows=1,831,138 loops=1)

  • Hash Cond: (contacts.owner_id = owners_contacts_join.id)
14. 1,054.130 23,887.649 ↑ 1.3 1,831,138 1

Hash Join (cost=2,176,790.96..2,364,249.51 rows=2,377,300 width=183) (actual time=20,294.486..23,887.649 rows=1,831,138 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_security_id = custodial_securities.id)
15. 2,105.018 22,552.901 ↑ 1.3 1,836,839 1

Hash Join (cost=2,146,141.78..2,288,975.21 rows=2,383,099 width=126) (actual time=20,013.401..22,552.901 rows=1,836,839 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_account_id = custodial_accounts.id)
16. 434.983 434.983 ↓ 1.0 2,903,591 1

Seq Scan on custodial_latest_positions_mvw (cost=0.00..65,605.91 rows=2,902,791 width=16) (actual time=0.003..434.983 rows=2,903,591 loops=1)

17. 690.538 20,012.900 ↓ 1.2 1,418,395 1

Hash (cost=2,111,542.07..2,111,542.07 rows=1,150,297 width=118) (actual time=20,012.900..20,012.900 rows=1,418,395 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 56035kB
18. 2,432.359 19,322.362 ↓ 1.2 1,418,395 1

Hash Join (cost=1,867,343.18..2,111,542.07 rows=1,150,297 width=118) (actual time=12,482.374..19,322.362 rows=1,418,395 loops=1)

  • Hash Cond: (accounts_custodial_accounts_mvw.custodial_account_id = custodial_accounts.id)
19. 2,165.939 15,167.739 ↓ 1.2 1,418,395 1

Hash Join (cost=1,648,419.26..1,808,942.08 rows=1,150,297 width=95) (actual time=10,759.550..15,167.739 rows=1,418,395 loops=1)

  • Hash Cond: (accounts.account_forms_collection_id = account_forms_collections.id)
20. 1,928.608 5,034.430 ↑ 1.0 1,418,568 1

Hash Join (cost=330,597.46..426,070.50 rows=1,418,625 width=36) (actual time=2,791.844..5,034.430 rows=1,418,568 loops=1)

  • Hash Cond: (accounts_custodial_accounts_mvw.account_id = accounts.id)
21. 314.459 314.459 ↑ 1.0 1,418,625 1

Seq Scan on accounts_custodial_accounts_mvw (cost=0.00..32,819.82 rows=1,418,625 width=16) (actual time=0.011..314.459 rows=1,418,625 loops=1)

  • Filter: ((number)::text <> ''::text)
  • Rows Removed by Filter: 1
22. 597.932 2,791.363 ↑ 1.0 2,831,206 1

Hash (cost=272,878.87..272,878.87 rows=2,841,487 width=36) (actual time=2,791.363..2,791.363 rows=2,831,206 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 44266kB
23. 2,193.431 2,193.431 ↑ 1.0 2,831,206 1

Seq Scan on accounts (cost=0.00..272,878.87 rows=2,841,487 width=36) (actual time=0.020..2,193.431 rows=2,831,206 loops=1)

24. 408.959 7,967.370 ↓ 1.0 1,076,079 1

Hash (cost=1,290,739.97..1,290,739.97 rows=1,074,866 width=75) (actual time=7,967.370..7,967.370 rows=1,076,079 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 59128kB
25. 3,326.490 7,558.411 ↓ 1.0 1,076,079 1

Hash Join (cost=51,136.36..1,290,739.97 rows=1,074,866 width=75) (actual time=348.902..7,558.411 rows=1,076,079 loops=1)

  • Hash Cond: (contacts.id = account_forms_collections.contact_id)
26. 3,883.780 3,883.780 ↓ 1.0 1,725,538 1

Seq Scan on contacts (cost=0.00..1,203,077.78 rows=1,718,478 width=59) (actual time=0.041..3,883.780 rows=1,725,538 loops=1)

27. 182.256 348.141 ↑ 1.2 1,076,079 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 50442kB
28. 165.885 165.885 ↓ 1.0 1,307,579 1

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

29. 827.438 1,722.264 ↓ 1.0 4,867,218 1

Hash (cost=129,918.52..129,918.52 rows=4,847,952 width=23) (actual time=1,722.264..1,722.264 rows=4,867,218 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 33865kB
30. 894.826 894.826 ↓ 1.0 4,867,218 1

Seq Scan on custodial_accounts (cost=0.00..129,918.52 rows=4,847,952 width=23) (actual time=0.004..894.826 rows=4,867,218 loops=1)

31. 145.689 280.618 ↓ 1.0 657,713 1

Hash (cost=22,427.97..22,427.97 rows=657,697 width=57) (actual time=280.618..280.618 rows=657,713 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 46754kB
32. 134.929 134.929 ↓ 1.0 657,713 1

Seq Scan on custodial_securities (cost=0.00..22,427.97 rows=657,697 width=57) (actual time=0.005..134.929 rows=657,713 loops=1)

33. 3.979 11.466 ↓ 1.0 25,104 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 1209kB
34. 7.487 7.487 ↓ 1.0 25,104 1

Seq Scan on classic_users owners_contacts_join (cost=0.00..1,844.18 rows=25,018 width=24) (actual time=0.004..7.487 rows=25,104 loops=1)

35. 478.234 4,584.665 ↓ 1.0 1,725,538 1

Hash (cost=1,203,077.78..1,203,077.78 rows=1,718,478 width=23) (actual time=4,584.665..4,584.665 rows=1,725,538 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 46997kB
36. 4,106.431 4,106.431 ↓ 1.0 1,725,538 1

Seq Scan on contacts contacts_classic_users (cost=0.00..1,203,077.78 rows=1,718,478 width=23) (actual time=0.046..4,106.431 rows=1,725,538 loops=1)

37. 144.973 369.023 ↓ 1.0 657,713 1

Hash (cost=31,487.82..31,487.82 rows=657,697 width=35) (actual time=369.023..369.023 rows=657,713 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 41165kB
38. 154.456 224.050 ↓ 1.0 657,713 1

Hash Left Join (cost=27.04..31,487.82 rows=657,697 width=35) (actual time=0.272..224.050 rows=657,713 loops=1)

  • Hash Cond: (securities_custodial_latest_positions_mvw.custodial_asset_id = custodial_assets.id)
39. 69.334 69.334 ↓ 1.0 657,713 1

Seq Scan on custodial_securities securities_custodial_latest_positions_mvw (cost=0.00..22,427.97 rows=657,697 width=16) (actual time=0.005..69.334 rows=657,713 loops=1)

40. 0.066 0.260 ↓ 1.0 521 1

Hash (cost=20.55..20.55 rows=519 width=35) (actual time=0.260..0.260 rows=521 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
41. 0.138 0.194 ↓ 1.0 521 1

Hash Left Join (cost=1.23..20.55 rows=519 width=35) (actual time=0.019..0.194 rows=521 loops=1)

  • Hash Cond: (custodial_assets.custodial_asset_class_id = custodial_asset_classes.id)
42. 0.047 0.047 ↓ 1.0 521 1

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

43. 0.003 0.009 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=19) (actual time=0.009..0.009 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
44. 0.006 0.006 ↑ 1.0 10 1

Seq Scan on custodial_asset_classes (cost=0.00..1.10 rows=10 width=19) (actual time=0.003..0.006 rows=10 loops=1)

45. 0.001 0.002 ↓ 0.0 0 1

Hash (cost=32.29..32.29 rows=290 width=38) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
46. 0.000 0.001 ↓ 0.0 0 1

Hash Join (cost=15.40..32.29 rows=290 width=38) (actual time=0.001..0.001 rows=0 loops=1)

  • Hash Cond: (custodial_cost_basis_details.custodial_cost_basis_summary_id = custodial_cost_basis_summaries.id)
47. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on custodial_cost_basis_details (cost=0.00..12.90 rows=290 width=30) (actual time=0.001..0.001 rows=0 loops=1)

48. 0.000 0.000 ↓ 0.0 0

Hash (cost=12.40..12.40 rows=240 width=24) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Seq Scan on custodial_cost_basis_summaries (cost=0.00..12.40 rows=240 width=24) (never executed)

50. 0.342 1.892 ↓ 1.0 2,053 1

Hash (cost=48.51..48.51 rows=2,051 width=29) (actual time=1.892..1.892 rows=2,053 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 123kB
51. 1.550 1.550 ↓ 1.0 2,053 1

Seq Scan on held_wheres (cost=0.00..48.51 rows=2,051 width=29) (actual time=0.004..1.550 rows=2,053 loops=1)

52. 0.001 0.051 ↑ 1.0 2 1

Hash (cost=38.96..38.96 rows=2 width=25) (actual time=0.051..0.051 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
53. 0.001 0.050 ↑ 1.0 2 1

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

54. 0.041 0.041 ↑ 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.022..0.041 rows=2 loops=1)

  • Index Cond: ((classic_urn)::text = ANY ('{RepCode:cfs-back-office:C35,RepCode:cfs-registration:C35}'::text[]))
55. 0.008 0.008 ↑ 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.003..0.004 rows=1 loops=2)

  • Index Cond: (rep_code_id = rep_codes.id)
56. 0.002 0.027 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
57. 0.005 0.025 ↑ 1.0 1 1

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

58. 0.015 0.015 ↑ 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.015..0.015 rows=1 loops=1)

  • Index Cond: ((classic_urn)::text = 'Workgroup:cfs:RCF'::text)
59. 0.005 0.005 ↑ 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.004..0.005 rows=1 loops=1)

  • Index Cond: (workgroup_id = workgroups.id)
60. 6.746 6.746 ↑ 1.0 1 3,373

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.002 rows=1 loops=3,373)

  • Index Cond: (id = custodial_assets.custodial_asset_product_subgroup_id)
61. 6.746 6.746 ↑ 1.0 1 3,373

Index Scan using reps_pkey on reps (cost=0.29..8.30 rows=1 width=24) (actual time=0.001..0.002 rows=1 loops=3,373)

  • Index Cond: (id = rep_codes.rep_id)
62. 10.119 10.119 ↑ 1.0 1 3,373

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=3,373)

  • Index Cond: (custodial_account_id = accounts_custodial_accounts_mvw.custodial_account_id)
63. 10.158 10.158 ↑ 1.0 1 3,386

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=3,386)

  • Index Cond: (id = contacts.id)
  • Filter: (realm_id = ANY ('{1,2,5,7,3,4}'::integer[]))
Planning time : 10.235 ms
Execution time : 33,152.226 ms