explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5DAE

Settings
# exclusive inclusive rows x rows loops node
1. 5.461 29,009.487 ↓ 3,286.0 3,286 1

WindowAgg (cost=3,001,010.45..3,001,010.52 rows=1 width=340) (actual time=29,000.952..29,009.487 rows=3,286 loops=1)

2. 14.199 29,004.026 ↓ 3,286.0 3,286 1

HashAggregate (cost=3,001,010.45..3,001,010.49 rows=1 width=340) (actual time=29,000.922..29,004.026 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. 2.515 28,989.827 ↓ 3,294.0 3,294 1

Nested Loop Semi Join (cost=2,346,150.31..3,001,010.36 rows=1 width=340) (actual time=20,329.451..28,989.827 rows=3,294 loops=1)

  • Join Filter: (account_forms_collections.contact_id = contacts_1.id)
4. 1.440 28,974.136 ↓ 3,294.0 3,294 1

Nested Loop Left Join (cost=2,346,149.88..3,001,002.91 rows=1 width=356) (actual time=20,329.429..28,974.136 rows=3,294 loops=1)

5. 5.008 28,959.572 ↓ 3,281.0 3,281 1

Nested Loop Left Join (cost=2,346,149.45..3,000,995.50 rows=1 width=337) (actual time=20,329.416..28,959.572 rows=3,281 loops=1)

6. 3.649 28,948.002 ↓ 3,281.0 3,281 1

Hash Join (cost=2,346,149.17..3,000,987.19 rows=1 width=329) (actual time=20,329.401..28,948.002 rows=3,281 loops=1)

  • Hash Cond: (owners_contacts_join.workgroup_id = workgroups.id)
7. 123.921 28,944.325 ↓ 27.3 3,281 1

Hash Join (cost=2,346,132.56..3,000,970.12 rows=120 width=303) (actual time=20,329.364..28,944.325 rows=3,281 loops=1)

  • Hash Cond: (contacts.rep_code_mapping_id = rep_code_mappings_contacts_join.id)
8. 424.976 28,820.343 ↓ 1.4 1,796,219 1

Hash Left Join (cost=2,346,093.57..2,995,963.76 rows=1,324,371 width=294) (actual time=20,161.003..28,820.343 rows=1,796,219 loops=1)

  • Hash Cond: (custodial_assets.custodial_asset_product_subgroup_id = custodial_asset_product_subgroups.id)
9. 439.859 28,395.340 ↓ 1.4 1,796,219 1

Hash Left Join (cost=2,346,090.48..2,977,750.56 rows=1,324,371 width=277) (actual time=20,160.969..28,395.340 rows=1,796,219 loops=1)

  • Hash Cond: (custodial_assets.custodial_asset_class_id = custodial_asset_classes.id)
10. 335.547 27,955.474 ↓ 1.4 1,796,219 1

Hash Left Join (cost=2,346,089.26..2,959,539.24 rows=1,324,371 width=266) (actual time=20,160.956..27,955.474 rows=1,796,219 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,302.323 27,619.926 ↓ 1.4 1,796,219 1

Hash Join (cost=2,346,052.62..2,856,863.84 rows=1,324,371 width=260) (actual time=20,160.944..27,619.926 rows=1,796,219 loops=1)

  • Hash Cond: (accounts.account_forms_collection_id = account_forms_collections.id)
12. 2,689.883 15,476.552 ↑ 1.6 1,801,720 1

Hash Join (cost=823,711.82..1,143,232.74 rows=2,827,838 width=178) (actual time=10,315.396..15,476.552 rows=1,801,720 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_account_id = custodial_accounts.id)
13. 1,632.083 2,945.946 ↑ 1.0 2,786,455 1

Hash Join (cost=48,050.67..238,176.88 rows=2,792,999 width=106) (actual time=469.471..2,945.946 rows=2,786,455 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_security_id = custodial_securities.id)
14. 844.495 844.495 ↑ 1.0 2,793,495 1

Seq Scan on custodial_latest_positions_mvw (cost=0.00..80,131.86 rows=2,799,812 width=41) (actual time=0.005..844.495 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
15. 186.215 469.368 ↓ 1.0 657,733 1

Hash (cost=31,479.46..31,479.46 rows=657,697 width=73) (actual time=469.368..469.368 rows=657,733 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 28554kB
16. 193.220 283.153 ↓ 1.0 657,733 1

Hash Left Join (cost=18.68..31,479.46 rows=657,697 width=73) (actual time=0.151..283.153 rows=657,733 loops=1)

  • Hash Cond: (custodial_securities.custodial_asset_id = custodial_assets.id)
17. 89.790 89.790 ↓ 1.0 657,733 1

Seq Scan on custodial_securities (cost=0.00..22,427.97 rows=657,697 width=65) (actual time=0.004..89.790 rows=657,733 loops=1)

18. 0.066 0.143 ↓ 1.0 521 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
19. 0.077 0.077 ↓ 1.0 521 1

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

20. 412.757 9,840.723 ↑ 1.0 1,418,567 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 39841kB
21. 341.233 9,427.966 ↑ 1.0 1,418,567 1

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

  • Hash Cond: (accounts.held_where_id = held_wheres.id)
22. 2,139.730 9,086.192 ↑ 1.0 1,418,567 1

Hash Join (cost=549,521.38..722,167.64 rows=1,418,625 width=59) (actual time=4,593.011..9,086.192 rows=1,418,567 loops=1)

  • Hash Cond: (accounts_custodial_accounts_mvw.account_id = accounts.id)
23. 2,075.602 4,140.025 ↑ 1.0 1,418,625 1

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

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

  • Filter: ((number)::text <> ''::text)
  • Rows Removed by Filter: 1
25. 831.384 1,778.210 ↓ 1.0 4,868,268 1

Hash (cost=129,918.52..129,918.52 rows=4,847,952 width=23) (actual time=1,778.210..1,778.210 rows=4,868,268 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 33872kB
26. 946.826 946.826 ↓ 1.0 4,868,268 1

Seq Scan on custodial_accounts (cost=0.00..129,918.52 rows=4,847,952 width=23) (actual time=0.008..946.826 rows=4,868,268 loops=1)

27. 588.362 2,806.437 ↑ 1.0 2,831,213 1

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

  • Buckets: 131072 Batches: 4 Memory Usage: 44266kB
28. 2,218.075 2,218.075 ↑ 1.0 2,831,213 1

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

29. 0.280 0.541 ↓ 1.0 2,053 1

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

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

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

31. 573.708 9,841.051 ↓ 1.7 1,074,231 1

Hash (cost=1,504,879.53..1,504,879.53 rows=620,822 width=98) (actual time=9,841.051..9,841.051 rows=1,074,231 loops=1)

  • Buckets: 65536 Batches: 4 (originally 2) Memory Usage: 65537kB
32. 2,299.875 9,267.343 ↓ 1.7 1,074,231 1

Hash Join (cost=251,383.22..1,504,879.53 rows=620,822 width=98) (actual time=437.767..9,267.343 rows=1,074,231 loops=1)

  • Hash Cond: (contacts.id = account_forms_collections.contact_id)
33. 2,357.648 6,596.918 ↓ 1.7 1,720,233 1

Hash Join (cost=200,246.85..1,432,631.15 rows=993,586 width=82) (actual time=67.034..6,596.918 rows=1,720,233 loops=1)

  • Hash Cond: (contacts.owner_id = owners_contacts_join.id)
34. 4,172.311 4,172.311 ↓ 1.0 1,725,538 1

Seq Scan on contacts (cost=0.00..1,203,095.56 rows=1,720,256 width=59) (actual time=0.047..4,172.311 rows=1,725,538 loops=1)

35. 3.185 66.959 ↓ 1.0 14,563 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 1046kB
36. 10.062 63.774 ↓ 1.0 14,563 1

Nested Loop (cost=0.43..200,065.92 rows=14,475 width=39) (actual time=0.021..63.774 rows=14,563 loops=1)

37. 3.504 3.504 ↓ 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..3.504 rows=25,104 loops=1)

38. 50.208 50.208 ↑ 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.002..0.002 rows=1 loops=25,104)

  • Index Cond: (id = owners_contacts_join.contact_id)
39. 176.179 370.550 ↑ 1.2 1,076,079 1

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

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

41. 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
42. 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)
43. 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)

44. 0.000 0.000 ↓ 0.0 0

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

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

46. 0.002 0.007 ↑ 1.0 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
47. 0.005 0.005 ↑ 1.0 10 1

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

48. 0.015 0.027 ↑ 1.0 93 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 6kB
49. 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.002..0.012 rows=93 loops=1)

50. 0.002 0.061 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
51. 0.001 0.059 ↑ 1.0 2 1

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

52. 0.046 0.046 ↑ 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.021..0.046 rows=2 loops=1)

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

  • Index Cond: (rep_code_id = rep_codes.id)
54. 0.002 0.028 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
55. 0.004 0.026 ↑ 1.0 1 1

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

56. 0.016 0.016 ↑ 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.016..0.016 rows=1 loops=1)

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

  • Index Cond: (workgroup_id = workgroups.id)
58. 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)
59. 13.124 13.124 ↑ 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.004 rows=1 loops=3,281)

  • Index Cond: (custodial_account_id = accounts_custodial_accounts_mvw.custodial_account_id)
60. 13.176 13.176 ↑ 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.004..0.004 rows=1 loops=3,294)

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