explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dP7C

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=468,306.32..468,306.51 rows=1 width=340) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

WindowAgg (cost=468,306.32..468,306.51 rows=1 width=340) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=468,306.32..468,306.45 rows=1 width=340) (actual rows= loops=)

  • 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.prod (...)
4. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=128,976.71..468,306.16 rows=1 width=340) (actual rows= loops=)

  • Join Filter: (account_forms_collections.contact_id = contacts_1.id)
5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=128,976.29..468,298.71 rows=1 width=356) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=128,975.86..468,291.30 rows=1 width=337) (actual rows= loops=)

  • Join Filter: (custodial_asset_product_subgroups.id = custodial_assets.custodial_asset_product_subgroup_id)
7. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=128,975.86..468,288.21 rows=1 width=320) (actual rows= loops=)

  • Join Filter: (custodial_asset_classes.id = custodial_assets.custodial_asset_class_id)
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=128,975.86..468,286.98 rows=1 width=309) (actual rows= loops=)

  • Join Filter: (held_wheres.id = accounts.held_where_id)
9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=128,975.86..468,212.84 rows=1 width=288) (actual rows= loops=)

  • Join Filter: (workgroups.id = owners_contacts_join.workgroup_id)
10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.28..76.83 rows=1 width=42) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Seq Scan on workgroups (cost=0.00..68.53 rows=1 width=37) (actual rows= loops=)

  • Filter: ((classic_urn)::text = 'Workgroup:cfs:RCF'::text)
12. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (workgroup_id = workgroups.id)
13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=128,975.58..468,136.00 rows=1 width=262) (actual rows= loops=)

  • Join Filter: (custodial_securities.id = custodial_cost_basis_summaries.custodial_security_id)
14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=128,975.29..468,129.21 rows=1 width=256) (actual rows= loops=)

  • Join Filter: (custodial_assets.id = securities_custodial_latest_positions_mvw.custodial_asset_id)
15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=128,975.29..468,110.53 rows=1 width=248) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=128,974.87..468,103.07 rows=1 width=248) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=128,974.44..468,095.14 rows=1 width=233) (actual rows= loops=)

  • Join Filter: (rep_code_mappings_contacts_join.id = contacts.rep_code_mapping_id)
18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.57..1,094.77 rows=2 width=33) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..1,078.15 rows=2 width=25) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Seq Scan on rep_codes (cost=0.00..1,055.28 rows=2 width=25) (actual rows= loops=)

  • Filter: ((classic_urn)::text = ANY ('{RepCode:cfs-back-office:C35,RepCode:cfs-registration:C35}'::text[]))
21. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • Index Cond: (rep_code_id = rep_codes.id)
22. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = rep_codes.rep_id)
23. 0.000 0.000 ↓ 0.0

Materialize (cost=128,973.87..466,712.89 rows=10,454 width=216) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=128,973.87..466,660.62 rows=10,454 width=216) (actual rows= loops=)

  • Join Filter: (custodial_latest_positions_mvw.custodial_account_id = custodial_accounts.id)
25. 0.000 0.000 ↓ 0.0

Hash Join (cost=128,973.43..389,030.14 rows=10,454 width=201) (actual rows= loops=)

  • Hash Cond: (contacts.owner_id = owners_contacts_join.id)
26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=126,816.53..386,663.98 rows=10,472 width=193) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash Join (cost=126,816.10..291,772.34 rows=12,736 width=134) (actual rows= loops=)

  • Hash Cond: (accounts.account_forms_collection_id = account_forms_collections.id)
28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=75,679.74..240,314.69 rows=12,929 width=134) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Merge Join (cost=75,679.31..144,292.13 rows=12,929 width=114) (actual rows= loops=)

  • Merge Cond: (accounts_custodial_accounts_mvw.custodial_account_id = custodial_latest_positions_mvw.custodial_account_id)
  • -> Index Scan using index_custodial_account_id_on_acamvw on accounts_custodial_accounts_mvw (cost=0.43..64254.13 rows=1677581 width= (...)
  • Filter: ((number)::text <> ''::text)
30. 0.000 0.000 ↓ 0.0

Sort (cost=75,678.88..75,711.08 rows=12,881 width=98) (actual rows= loops=)

  • Sort Key: custodial_latest_positions_mvw.custodial_account_id
31. 0.000 0.000 ↓ 0.0

Hash Join (cost=33,985.48..74,799.56 rows=12,881 width=98) (actual rows= loops=)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_security_id = custodial_securities.id)
32. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on custodial_latest_positions_mvw (cost=3,346.45..43,918.49 rows=12,940 width=41) (actual rows= loops=)

  • Recheck Cond: ((valued_on >= '2019-12-23'::date) AND (valued_on <= '2020-01-23'::date) AND (valued_on >= '2019-11-23 (...)
  • Filter: ((market_value IS NOT NULL) AND (quantity IS NOT NULL) AND (market_value IS NOT NULL) AND (quantity IS NOT N (...)
33. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on valued_on_custodial_latest_positions_mvw (cost=0.00..3,343.21 rows=31,524 width=0) (actual rows= loops=)

  • Index Cond: ((valued_on >= '2019-12-23'::date) AND (valued_on <= '2020-01-23'::date) AND (valued_on >= '2019-1 (...)
34. 0.000 0.000 ↓ 0.0

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

35. 0.000 0.000 ↓ 0.0

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

36. 0.000 0.000 ↓ 0.0

Index Scan using accounts_pkey on accounts (cost=0.43..7.42 rows=1 width=36) (actual rows= loops=)

  • Index Cond: (id = accounts_custodial_accounts_mvw.account_id)
37. 0.000 0.000 ↓ 0.0

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

38. 0.000 0.000 ↓ 0.0

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

39. 0.000 0.000 ↓ 0.0

Index Scan using contacts_pkey on contacts (cost=0.43..7.44 rows=1 width=59) (actual rows= loops=)

  • Index Cond: (id = account_forms_collections.contact_id)
40. 0.000 0.000 ↓ 0.0

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

41. 0.000 0.000 ↓ 0.0

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

42. 0.000 0.000 ↓ 0.0

Index Scan using custodial_accounts_pkey on custodial_accounts (cost=0.43..7.41 rows=1 width=23) (actual rows= loops=)

  • Index Cond: (id = accounts_custodial_accounts_mvw.custodial_account_id)
43. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = owners_contacts_join.contact_id)
44. 0.000 0.000 ↓ 0.0

Index Scan using custodial_securities_pkey on custodial_securities securities_custodial_latest_positions_mvw (cost=0.42..7.46 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = custodial_latest_positions_mvw.custodial_security_id)
45. 0.000 0.000 ↓ 0.0

Seq Scan on custodial_assets (cost=0.00..12.19 rows=519 width=24) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..6.77 rows=1 width=38) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • Index Cond: (custodial_accounts.id = custodial_account_id)
48. 0.000 0.000 ↓ 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) (actual rows= loops=)

  • Index Cond: (custodial_cost_basis_summary_id = custodial_cost_basis_summaries.id)
49. 0.000 0.000 ↓ 0.0

Seq Scan on held_wheres (cost=0.00..48.51 rows=2,051 width=29) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

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

51. 0.000 0.000 ↓ 0.0

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

52. 0.000 0.000 ↓ 0.0

Index Scan using custodial_account_id_contacts_mvbalances_mvw on contacts_mvbalances_mvw (cost=0.43..7.40 rows=1 width=35) (actual rows= loops=)

  • Index Cond: (custodial_account_id = accounts_custodial_accounts_mvw.custodial_account_id)
53. 0.000 0.000 ↓ 0.0

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

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