explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q5Ky

Settings
# exclusive inclusive rows x rows loops node
1. 3.744 31,749.555 ↓ 3,286.0 3,286 1

WindowAgg (cost=2,947,683.75..2,947,683.79 rows=1 width=315) (actual time=31,743.820..31,749.555 rows=3,286 loops=1)

2. 9.935 31,745.811 ↓ 3,286.0 3,286 1

HashAggregate (cost=2,947,683.75..2,947,683.77 rows=1 width=315) (actual time=31,743.799..31,745.811 rows=3,286 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. 3.467 31,735.876 ↓ 3,294.0 3,294 1

Nested Loop Semi Join (cost=2,368,430.03..2,947,683.67 rows=1 width=315) (actual time=23,107.707..31,735.876 rows=3,294 loops=1)

  • Join Filter: (account_forms_collections.contact_id = contacts_1.id)
4. 3.325 31,722.527 ↓ 3,294.0 3,294 1

Nested Loop Left Join (cost=2,368,429.60..2,947,676.22 rows=1 width=331) (actual time=23,107.681..31,722.527 rows=3,294 loops=1)

5. 3.372 31,709.359 ↓ 3,281.0 3,281 1

Nested Loop Left Join (cost=2,368,429.18..2,947,668.81 rows=1 width=312) (actual time=23,107.665..31,709.359 rows=3,281 loops=1)

6. 1.392 31,699.425 ↓ 3,281.0 3,281 1

Hash Join (cost=2,368,428.89..2,947,660.50 rows=1 width=304) (actual time=23,107.638..31,699.425 rows=3,281 loops=1)

  • Hash Cond: (owners_contacts_join.workgroup_id = workgroups.id)
7. 122.870 31,697.998 ↓ 27.3 3,281 1

Hash Join (cost=2,368,412.28..2,947,643.43 rows=120 width=278) (actual time=23,107.593..31,697.998 rows=3,281 loops=1)

  • Hash Cond: (contacts.rep_code_mapping_id = rep_code_mappings_contacts_join.id)
8. 410.987 31,575.072 ↓ 1.4 1,796,180 1

Hash Left Join (cost=2,368,373.30..2,942,638.43 rows=1,324,007 width=269) (actual time=22,902.917..31,575.072 rows=1,796,180 loops=1)

  • Hash Cond: (custodial_assets.custodial_asset_product_subgroup_id = custodial_asset_product_subgroups.id)
9. 420.930 31,164.047 ↓ 1.4 1,796,180 1

Hash Left Join (cost=2,368,370.21..2,924,430.24 rows=1,324,007 width=252) (actual time=22,902.858..31,164.047 rows=1,796,180 loops=1)

  • Hash Cond: (custodial_assets.custodial_asset_class_id = custodial_asset_classes.id)
10. 314.096 30,743.106 ↓ 1.4 1,796,180 1

Hash Left Join (cost=2,368,368.98..2,906,223.92 rows=1,324,007 width=241) (actual time=22,902.831..30,743.106 rows=1,796,180 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. 2,063.877 30,429.009 ↓ 1.4 1,796,180 1

Hash Join (cost=2,368,332.34..2,803,576.73 rows=1,324,007 width=235) (actual time=22,902.808..30,429.009 rows=1,796,180 loops=1)

  • Hash Cond: (accounts.account_forms_collection_id = account_forms_collections.id)
12. 1,163.631 15,709.433 ↑ 1.6 1,801,721 1

Hash Join (cost=846,011.00..1,106,579.54 rows=2,827,060 width=153) (actual time=10,243.322..15,709.433 rows=1,801,721 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_security_id = custodial_securities.id)
13. 2,197.888 14,248.465 ↑ 1.6 1,807,374 1

Hash Join (cost=815,361.82..1,022,862.65 rows=2,833,956 width=104) (actual time=9,945.835..14,248.465 rows=1,807,374 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_account_id = custodial_accounts.id)
14. 1,224.361 2,444.990 ↑ 1.0 2,793,495 1

Hash Left Join (cost=39,700.67..172,234.45 rows=2,799,041 width=32) (actual time=339.215..2,444.990 rows=2,793,495 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_security_id = securities_custodial_latest_positions_mvw.id)
15. 881.589 881.589 ↑ 1.0 2,793,495 1

Seq Scan on custodial_latest_positions_mvw (cost=0.00..80,119.86 rows=2,799,041 width=16) (actual time=0.015..881.589 rows=2,793,495 loops=1)

  • Filter: ((valued_on >= '2019-07-31'::date) AND (valued_on <= '2020-01-31'::date))
  • Rows Removed by Filter: 110096
16. 107.048 339.040 ↓ 1.0 657,713 1

Hash (cost=31,479.46..31,479.46 rows=657,697 width=24) (actual time=339.040..339.040 rows=657,713 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 35955kB
17. 163.020 231.992 ↓ 1.0 657,713 1

Hash Left Join (cost=18.68..31,479.46 rows=657,697 width=24) (actual time=0.166..231.992 rows=657,713 loops=1)

  • Hash Cond: (securities_custodial_latest_positions_mvw.custodial_asset_id = custodial_assets.id)
18. 68.819 68.819 ↓ 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..68.819 rows=657,713 loops=1)

19. 0.061 0.153 ↓ 1.0 521 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
20. 0.092 0.092 ↓ 1.0 521 1

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

21. 437.220 9,605.587 ↑ 1.0 1,418,568 1

Hash (cost=739,918.33..739,918.33 rows=1,418,625 width=80) (actual time=9,605.587..9,605.587 rows=1,418,568 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 39841kB
22. 303.284 9,168.367 ↑ 1.0 1,418,568 1

Hash Left Join (cost=549,595.53..739,918.33 rows=1,418,625 width=80) (actual time=4,568.275..9,168.367 rows=1,418,568 loops=1)

  • Hash Cond: (accounts.held_where_id = held_wheres.id)
23. 2,026.390 8,864.515 ↑ 1.0 1,418,568 1

Hash Join (cost=549,521.38..722,167.64 rows=1,418,625 width=59) (actual time=4,567.693..8,864.515 rows=1,418,568 loops=1)

  • Hash Cond: (accounts_custodial_accounts_mvw.account_id = accounts.id)
24. 1,981.921 4,006.696 ↑ 1.0 1,418,625 1

Hash Join (cost=218,923.92..320,602.96 rows=1,418,625 width=39) (actual time=1,735.928..4,006.696 rows=1,418,625 loops=1)

  • Hash Cond: (accounts_custodial_accounts_mvw.custodial_account_id = custodial_accounts.id)
25. 289.090 289.090 ↑ 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.007..289.090 rows=1,418,625 loops=1)

  • Filter: ((number)::text <> ''::text)
  • Rows Removed by Filter: 1
26. 838.790 1,735.685 ↓ 1.0 4,867,218 1

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

  • Buckets: 131072 Batches: 8 Memory Usage: 33865kB
27. 896.895 896.895 ↓ 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..896.895 rows=4,867,218 loops=1)

28. 605.343 2,831.429 ↑ 1.0 2,831,206 1

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

  • Buckets: 131072 Batches: 4 Memory Usage: 44266kB
29. 2,226.086 2,226.086 ↑ 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.024..2,226.086 rows=2,831,206 loops=1)

30. 0.268 0.568 ↓ 1.0 2,053 1

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

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

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

32. 141.828 297.337 ↓ 1.0 657,713 1

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

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

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

34. 683.563 12,655.699 ↓ 1.7 1,074,231 1

Hash (cost=1,504,860.07..1,504,860.07 rows=620,822 width=98) (actual time=12,655.699..12,655.699 rows=1,074,231 loops=1)

  • Buckets: 65536 Batches: 4 (originally 2) Memory Usage: 65537kB
35. 3,360.478 11,972.136 ↓ 1.7 1,074,231 1

Hash Join (cost=251,427.21..1,504,860.07 rows=620,822 width=98) (actual time=1,064.415..11,972.136 rows=1,074,231 loops=1)

  • Hash Cond: (contacts.id = account_forms_collections.contact_id)
36. 2,502.871 8,259.467 ↓ 1.7 1,720,233 1

Hash Join (cost=200,290.85..1,432,627.10 rows=992,559 width=82) (actual time=711.527..8,259.467 rows=1,720,233 loops=1)

  • Hash Cond: (contacts.owner_id = owners_contacts_join.id)
37. 5,045.155 5,045.155 ↓ 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.060..5,045.155 rows=1,725,538 loops=1)

38. 6.440 711.441 ↓ 1.0 14,563 1

Hash (cost=200,109.91..200,109.91 rows=14,475 width=39) (actual time=711.441..711.441 rows=14,563 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 1046kB
39. 20.210 705.001 ↓ 1.0 14,563 1

Nested Loop (cost=0.43..200,109.91 rows=14,475 width=39) (actual time=0.032..705.001 rows=14,563 loops=1)

40. 6.983 6.983 ↓ 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.002..6.983 rows=25,104 loops=1)

41. 677.808 677.808 ↑ 1.0 1 25,104

Index Scan using contacts_pkey on contacts contacts_classic_users (cost=0.43..7.91 rows=1 width=23) (actual time=0.027..0.027 rows=1 loops=25,104)

  • Index Cond: (id = owners_contacts_join.contact_id)
42. 184.829 352.191 ↑ 1.2 1,076,079 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 50442kB
43. 167.362 167.362 ↓ 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.006..167.362 rows=1,307,579 loops=1)

44. 0.000 0.001 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
45. 0.001 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)
46. 0.000 0.000 ↓ 0.0 0 1

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

47. 0.000 0.000 ↓ 0.0 0

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

48. 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)

49. 0.003 0.011 ↑ 1.0 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
50. 0.008 0.008 ↑ 1.0 10 1

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

51. 0.026 0.038 ↑ 1.0 93 1

Hash (cost=1.93..1.93 rows=93 width=25) (actual time=0.038..0.038 rows=93 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
52. 0.012 0.012 ↑ 1.0 93 1

Seq Scan on custodial_asset_product_subgroups (cost=0.00..1.93 rows=93 width=25) (actual time=0.003..0.012 rows=93 loops=1)

53. 0.000 0.056 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
54. 0.004 0.056 ↑ 1.0 2 1

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

55. 0.042 0.042 ↑ 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.023..0.042 rows=2 loops=1)

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

  • Index Cond: (rep_code_id = rep_codes.id)
57. 0.003 0.035 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
58. 0.004 0.032 ↑ 1.0 1 1

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

59. 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)
60. 0.013 0.013 ↑ 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.012..0.013 rows=1 loops=1)

  • Index Cond: (workgroup_id = workgroups.id)
61. 6.562 6.562 ↑ 1.0 1 3,281

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

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

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,281)

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

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,294)

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