explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DWHB

Settings
# exclusive inclusive rows x rows loops node
1. 5.564 49,560.744 ↓ 3,434.0 3,434 1

WindowAgg (cost=2,471,671.24..2,471,671.30 rows=1 width=318) (actual time=49,552.179..49,560.744 rows=3,434 loops=1)

2. 12.360 49,555.180 ↓ 3,434.0 3,434 1

HashAggregate (cost=2,471,671.24..2,471,671.27 rows=1 width=318) (actual time=49,552.151..49,555.180 rows=3,434 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_asset_classes.id, custodial_asset_product_subgroups.id
3. 2.544 49,542.820 ↓ 3,442.0 3,442 1

Nested Loop Semi Join (cost=2,194,288.40..2,471,671.15 rows=1 width=318) (actual time=35,617.701..49,542.820 rows=3,442 loops=1)

  • Join Filter: (account_forms_collections.contact_id = contacts_1.id)
4. 2.679 49,526.508 ↓ 3,442.0 3,442 1

Nested Loop Left Join (cost=2,194,287.97..2,471,663.70 rows=1 width=334) (actual time=35,617.675..49,526.508 rows=3,442 loops=1)

5. 1.323 49,506.684 ↓ 3,429.0 3,429 1

Nested Loop Left Join (cost=2,194,287.54..2,471,656.29 rows=1 width=315) (actual time=35,617.650..49,506.684 rows=3,429 loops=1)

6. 4.831 49,501.932 ↓ 3,429.0 3,429 1

Nested Loop Left Join (cost=2,194,287.40..2,471,654.11 rows=1 width=298) (actual time=35,617.643..49,501.932 rows=3,429 loops=1)

  • Join Filter: (custodial_asset_classes.id = custodial_assets.custodial_asset_class_id)
  • Rows Removed by Join Filter: 30,861
7. 0.737 49,493.672 ↓ 3,429.0 3,429 1

Nested Loop Left Join (cost=2,194,287.40..2,471,652.89 rows=1 width=287) (actual time=35,617.633..49,493.672 rows=3,429 loops=1)

8. 3.518 49,486.077 ↓ 3,429.0 3,429 1

Nested Loop Left Join (cost=2,194,287.12..2,471,646.15 rows=1 width=266) (actual time=35,617.622..49,486.077 rows=3,429 loops=1)

9. 3.549 49,479.130 ↓ 3,429.0 3,429 1

Nested Loop Left Join (cost=2,194,286.84..2,471,637.85 rows=1 width=269) (actual time=35,617.595..49,479.130 rows=3,429 loops=1)

10. 1.796 49,472.152 ↓ 3,429.0 3,429 1

Nested Loop (cost=2,194,286.56..2,471,629.54 rows=1 width=261) (actual time=35,617.569..49,472.152 rows=3,429 loops=1)

11. 1.080 49,463.498 ↓ 3,429.0 3,429 1

Merge Join (cost=2,194,286.13..2,471,621.61 rows=1 width=246) (actual time=35,617.552..49,463.498 rows=3,429 loops=1)

  • Merge Cond: (owners_contacts_join.workgroup_id = workgroups.id)
12. 15,795.048 49,462.133 ↓ 15.9 3,429 1

Nested Loop (cost=2,194,277.83..2,671,294.29 rows=215 width=217) (actual time=35,617.487..49,462.133 rows=3,429 loops=1)

  • Join Filter: (contacts.owner_id = owners_contacts_join.id)
  • Rows Removed by Join Filter: 86,078,187
13. 128.141 128.141 ↓ 1.0 25,104 1

Index Scan using index_classic_users_on_workgroup_id on classic_users owners_contacts_join (cost=0.29..78,116.43 rows=25,018 width=24) (actual time=0.006..128.141 rows=25,104 loops=1)

14. 3,318.032 33,538.944 ↓ 15.9 3,429 25,104

Materialize (cost=2,194,277.54..2,512,120.08 rows=216 width=209) (actual time=0.992..1.336 rows=3,429 loops=25,104)

15. 120.786 30,220.912 ↓ 15.9 3,429 1

Hash Join (cost=2,194,277.54..2,512,119.00 rows=216 width=209) (actual time=24,894.799..30,220.912 rows=3,429 loops=1)

  • Hash Cond: (contacts.rep_code_mapping_id = rep_code_mappings_contacts_join.id)
16. 2,741.216 30,099.265 ↑ 1.3 1,841,727 1

Hash Join (cost=2,194,238.56..2,503,128.42 rows=2,386,617 width=200) (actual time=24,719.818..30,099.265 rows=1,841,727 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_account_id = custodial_accounts.id)
17. 1,626.868 3,532.647 ↓ 1.0 2,907,871 1

Hash Join (cost=48,050.67..228,072.12 rows=2,907,078 width=98) (actual time=891.477..3,532.647 rows=2,907,871 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_security_id = custodial_securities.id)
18. 1,014.560 1,014.560 ↓ 1.0 2,914,985 1

Seq Scan on custodial_latest_positions_mvw (cost=0.00..65,875.69 rows=2,914,169 width=41) (actual time=0.003..1,014.560 rows=2,914,985 loops=1)

19. 198.278 891.219 ↓ 1.0 657,749 1

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

  • Buckets: 65,536 Batches: 2 Memory Usage: 28,554kB
20. 208.586 692.941 ↓ 1.0 657,749 1

Hash Left Join (cost=18.68..31,479.46 rows=657,697 width=73) (actual time=0.219..692.941 rows=657,749 loops=1)

  • Hash Cond: (custodial_securities.custodial_asset_id = custodial_assets.id)
21. 484.167 484.167 ↓ 1.0 657,749 1

Seq Scan on custodial_securities (cost=0.00..22,427.97 rows=657,697 width=65) (actual time=0.017..484.167 rows=657,749 loops=1)

22. 0.082 0.188 ↓ 1.0 521 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 29kB
23. 0.106 0.106 ↓ 1.0 521 1

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

24. 557.272 23,825.402 ↓ 1.2 1,418,469 1

Hash (cost=2,111,587.95..2,111,587.95 rows=1,150,315 width=118) (actual time=23,825.402..23,825.402 rows=1,418,469 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 56,028kB
25. 2,382.228 23,268.130 ↓ 1.2 1,418,469 1

Hash Join (cost=1,867,387.62..2,111,587.95 rows=1,150,315 width=118) (actual time=16,298.905..23,268.130 rows=1,418,469 loops=1)

  • Hash Cond: (accounts_custodial_accounts_mvw.custodial_account_id = custodial_accounts.id)
26. 2,093.079 18,757.290 ↓ 1.2 1,418,469 1

Hash Join (cost=1,648,463.70..1,808,987.62 rows=1,150,315 width=95) (actual time=14,166.632..18,757.290 rows=1,418,469 loops=1)

  • Hash Cond: (accounts.account_forms_collection_id = account_forms_collections.id)
27. 1,940.884 5,890.911 ↑ 1.0 1,418,642 1

Hash Join (cost=330,597.46..426,071.16 rows=1,418,646 width=36) (actual time=3,384.704..5,890.911 rows=1,418,642 loops=1)

  • Hash Cond: (accounts_custodial_accounts_mvw.account_id = accounts.id)
28. 567.297 567.297 ↑ 1.0 1,418,646 1

Seq Scan on accounts_custodial_accounts_mvw (cost=0.00..32,820.09 rows=1,418,646 width=16) (actual time=0.017..567.297 rows=1,418,646 loops=1)

  • Filter: ((number)::text <> ''::text)
  • Rows Removed by Filter: 1
29. 645.657 3,382.730 ↑ 1.0 2,831,238 1

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

  • Buckets: 131,072 Batches: 4 Memory Usage: 44,266kB
30. 2,737.073 2,737.073 ↑ 1.0 2,831,238 1

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

31. 475.487 10,773.300 ↓ 1.0 1,076,079 1

Hash (cost=1,290,784.42..1,290,784.42 rows=1,074,866 width=75) (actual time=10,773.300..10,773.300 rows=1,076,079 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 59,121kB
32. 3,566.679 10,297.813 ↓ 1.0 1,076,079 1

Hash Join (cost=51,136.36..1,290,784.42 rows=1,074,866 width=75) (actual time=589.330..10,297.813 rows=1,076,079 loops=1)

  • Hash Cond: (contacts.id = account_forms_collections.contact_id)
33. 6,142.293 6,142.293 ↓ 1.0 1,726,253 1

Seq Scan on contacts (cost=0.00..1,203,095.56 rows=1,720,256 width=59) (actual time=0.020..6,142.293 rows=1,726,253 loops=1)

34. 233.387 588.841 ↑ 1.2 1,076,079 1

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

  • Buckets: 131,072 Batches: 1 Memory Usage: 50,442kB
35. 355.454 355.454 ↓ 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.009..355.454 rows=1,307,579 loops=1)

36. 876.436 2,128.612 ↓ 1.0 4,868,294 1

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

  • Buckets: 131,072 Batches: 8 Memory Usage: 33,872kB
37. 1,252.176 1,252.176 ↓ 1.0 4,868,294 1

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

38. 0.004 0.861 ↑ 1.0 2 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
39. 0.004 0.857 ↑ 1.0 2 1

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

40. 0.803 0.803 ↑ 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.075..0.803 rows=2 loops=1)

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

  • Index Cond: (rep_code_id = rep_codes.id)
42. 0.248 0.285 ↑ 1.0 1 1

Sort (cost=8.30..8.31 rows=1 width=37) (actual time=0.058..0.285 rows=1 loops=1)

  • Sort Key: workgroups.id
  • Sort Method: quicksort Memory: 25kB
43. 0.037 0.037 ↑ 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.037..0.037 rows=1 loops=1)

  • Index Cond: ((classic_urn)::text = 'Workgroup:cfs:RCF'::text)
44. 6.858 6.858 ↑ 1.0 1 3,429

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

  • Index Cond: (id = owners_contacts_join.contact_id)
45. 3.429 3.429 ↑ 1.0 1 3,429

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

  • Index Cond: (id = rep_codes.rep_id)
46. 3.429 3.429 ↑ 1.0 1 3,429

Index Scan using index_programs_on_workgroup_id on programs (cost=0.28..8.29 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=3,429)

  • Index Cond: (workgroup_id = workgroups.id)
47. 6.858 6.858 ↑ 1.0 1 3,429

Index Scan using held_wheres_pkey on held_wheres (cost=0.28..6.73 rows=1 width=29) (actual time=0.002..0.002 rows=1 loops=3,429)

  • Index Cond: (id = accounts.held_where_id)
48. 3.429 3.429 ↑ 1.0 10 3,429

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

49. 3.429 3.429 ↑ 1.0 1 3,429

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

  • Index Cond: (id = custodial_assets.custodial_asset_product_subgroup_id)
50. 17.145 17.145 ↑ 1.0 1 3,429

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.005..0.005 rows=1 loops=3,429)

  • Index Cond: (custodial_account_id = accounts_custodial_accounts_mvw.custodial_account_id)
51. 13.768 13.768 ↑ 1.0 1 3,442

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

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