explain.depesz.com

PostgreSQL's explain analyze made readable

Result: F9ao

Settings
# exclusive inclusive rows x rows loops node
1. 5.344 31,568.656 ↓ 3,196.0 3,196 1

WindowAgg (cost=2,688,671.85..2,688,671.92 rows=1 width=342) (actual time=31,560.124..31,568.656 rows=3,196 loops=1)

2. 15.087 31,563.312 ↓ 3,196.0 3,196 1

HashAggregate (cost=2,688,671.85..2,688,671.89 rows=1 width=342) (actual time=31,560.097..31,563.312 rows=3,196 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.808 31,548.225 ↓ 3,204.0 3,204 1

Nested Loop Semi Join (cost=2,177,332.02..2,688,671.75 rows=1 width=342) (actual time=23,911.084..31,548.225 rows=3,204 loops=1)

  • Join Filter: (account_forms_collections.contact_id = contacts_1.id)
4. 1.673 31,519.785 ↓ 3,204.0 3,204 1

Nested Loop Left Join (cost=2,177,331.59..2,688,664.30 rows=1 width=358) (actual time=23,911.059..31,519.785 rows=3,204 loops=1)

5. 3.082 31,498.966 ↓ 3,191.0 3,191 1

Nested Loop Left Join (cost=2,177,331.16..2,688,656.89 rows=1 width=339) (actual time=23,911.019..31,498.966 rows=3,191 loops=1)

6. 6.502 31,492.693 ↓ 3,191.0 3,191 1

Nested Loop Left Join (cost=2,177,331.02..2,688,654.71 rows=1 width=322) (actual time=23,911.013..31,492.693 rows=3,191 loops=1)

  • Join Filter: (custodial_asset_classes.id = custodial_assets.custodial_asset_class_id)
  • Rows Removed by Join Filter: 28719
7. 1.600 31,483.000 ↓ 3,191.0 3,191 1

Nested Loop Left Join (cost=2,177,331.02..2,688,653.49 rows=1 width=311) (actual time=23,911.002..31,483.000 rows=3,191 loops=1)

8. 1.848 31,475.018 ↓ 3,191.0 3,191 1

Nested Loop Left Join (cost=2,177,330.74..2,688,646.75 rows=1 width=290) (actual time=23,910.993..31,475.018 rows=3,191 loops=1)

  • Join Filter: (custodial_securities.id = custodial_cost_basis_summaries.custodial_security_id)
9. 2.060 31,469.979 ↓ 3,191.0 3,191 1

Hash Join (cost=2,177,330.45..2,688,639.96 rows=1 width=284) (actual time=23,910.984..31,469.979 rows=3,191 loops=1)

  • Hash Cond: (owners_contacts_join.workgroup_id = workgroups.id)
10. 2.133 31,467.861 ↓ 28.7 3,191 1

Hash Left Join (cost=2,177,313.84..2,688,622.93 rows=111 width=258) (actual time=23,910.916..31,467.861 rows=3,191 loops=1)

  • Hash Cond: (securities_custodial_latest_positions_mvw.custodial_asset_id = custodial_assets.id)
11. 2.675 31,465.557 ↓ 28.7 3,191 1

Nested Loop Left Join (cost=2,177,295.16..2,688,602.73 rows=111 width=250) (actual time=23,910.718..31,465.557 rows=3,191 loops=1)

12. 4.351 31,437.354 ↓ 28.7 3,191 1

Nested Loop (cost=2,177,294.74..2,687,780.54 rows=111 width=250) (actual time=23,910.703..31,437.354 rows=3,191 loops=1)

13. 119.067 31,423.430 ↓ 16.6 3,191 1

Hash Join (cost=2,177,294.31..2,686,258.95 rows=192 width=235) (actual time=23,909.982..31,423.430 rows=3,191 loops=1)

  • Hash Cond: (contacts.rep_code_mapping_id = rep_code_mappings_contacts_join.id)
14. 611.774 31,304.285 ↑ 1.3 1,688,546 1

Hash Join (cost=2,177,238.70..2,678,245.45 rows=2,121,685 width=218) (actual time=23,382.589..31,304.285 rows=1,688,546 loops=1)

  • Hash Cond: (contacts.owner_id = owners_contacts_join.id)
15. 1,274.042 30,617.965 ↑ 1.3 1,688,546 1

Hash Join (cost=2,175,081.79..2,633,619.42 rows=2,125,227 width=210) (actual time=23,308.030..30,617.965 rows=1,688,546 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_security_id = custodial_securities.id)
16. 2,527.032 29,027.399 ↑ 1.3 1,694,145 1

Hash Join (cost=2,144,435.85..2,563,080.11 rows=2,130,411 width=152) (actual time=22,991.330..29,027.399 rows=1,694,145 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_account_id = custodial_accounts.id)
17. 3,527.647 3,527.647 ↑ 1.0 2,599,537 1

Seq Scan on custodial_latest_positions_mvw (cost=0.00..326,857.10 rows=2,599,920 width=41) (actual time=0.039..3,527.647 rows=2,599,537 loops=1)

  • Filter: ((valued_on >= '2019-12-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-11-30'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-10-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-09-30'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-08-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-07-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-12-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-11-30'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-10-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-09-30'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-08-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-07-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-12-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-11-30'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-10-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-09-30'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-08-31'::date) AND (valued_on <= '2020-01-31'::date) AND (valued_on >= '2019-07-31'::date) AND (valued_on <= '2020-01-31'::date))
  • Rows Removed by Filter: 304054
18. 897.924 22,972.720 ↓ 1.2 1,418,452 1

Hash (cost=2,109,902.35..2,109,902.35 rows=1,148,120 width=119) (actual time=22,972.720..22,972.720 rows=1,418,452 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 56038kB
19. 2,989.072 22,074.796 ↓ 1.2 1,418,452 1

Hash Join (cost=1,861,300.45..2,109,902.35 rows=1,148,120 width=119) (actual time=13,935.442..22,074.796 rows=1,418,452 loops=1)

  • Hash Cond: (accounts_custodial_accounts_mvw.custodial_account_id = custodial_accounts.id)
20. 2,677.958 17,079.339 ↓ 1.2 1,418,452 1

Hash Join (cost=1,647,667.98..1,808,051.04 rows=1,148,120 width=96) (actual time=11,926.220..17,079.339 rows=1,418,452 loops=1)

  • Hash Cond: (accounts.account_forms_collection_id = account_forms_collections.id)
21. 2,140.540 5,332.522 ↑ 1.0 1,418,625 1

Hash Join (cost=330,139.71..425,494.75 rows=1,418,625 width=37) (actual time=2,854.853..5,332.522 rows=1,418,625 loops=1)

  • Hash Cond: (accounts_custodial_accounts_mvw.account_id = accounts.id)
22. 337.915 337.915 ↑ 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.013..337.915 rows=1,418,625 loops=1)

  • Filter: ((number)::text <> ''::text)
  • Rows Removed by Filter: 1
23. 608.385 2,854.067 ↓ 1.0 2,831,153 1

Hash (cost=272,727.87..272,727.87 rows=2,826,387 width=37) (actual time=2,854.067..2,854.067 rows=2,831,153 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 44266kB
24. 2,245.682 2,245.682 ↓ 1.0 2,831,153 1

Seq Scan on accounts (cost=0.00..272,727.87 rows=2,826,387 width=37) (actual time=0.021..2,245.682 rows=2,831,153 loops=1)

25. 592.432 9,068.859 ↓ 1.0 1,076,079 1

Hash (cost=1,290,446.45..1,290,446.45 rows=1,074,866 width=75) (actual time=9,068.859..9,068.859 rows=1,076,079 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 59128kB
26. 3,864.424 8,476.427 ↓ 1.0 1,076,079 1

Hash Join (cost=51,136.36..1,290,446.45 rows=1,074,866 width=75) (actual time=499.747..8,476.427 rows=1,076,079 loops=1)

  • Hash Cond: (contacts.id = account_forms_collections.contact_id)
27. 4,112.433 4,112.433 ↓ 1.0 1,725,536 1

Seq Scan on contacts (cost=0.00..1,202,960.37 rows=1,706,737 width=59) (actual time=0.015..4,112.433 rows=1,725,536 loops=1)

28. 215.689 499.570 ↑ 1.2 1,076,079 1

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

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

30. 1,077.253 2,006.385 ↓ 1.0 4,865,594 1

Hash (cost=128,052.54..128,052.54 rows=4,661,354 width=23) (actual time=2,006.385..2,006.385 rows=4,865,594 loops=1)

  • Buckets: 131072 Batches: 8 (originally 4) Memory Usage: 65537kB
31. 929.132 929.132 ↓ 1.0 4,865,594 1

Seq Scan on custodial_accounts (cost=0.00..128,052.54 rows=4,661,354 width=23) (actual time=0.004..929.132 rows=4,865,594 loops=1)

32. 132.185 316.524 ↓ 1.0 657,584 1

Hash (cost=22,426.53..22,426.53 rows=657,553 width=58) (actual time=316.524..316.524 rows=657,584 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 46743kB
33. 184.339 184.339 ↓ 1.0 657,584 1

Seq Scan on custodial_securities (cost=0.00..22,426.53 rows=657,553 width=58) (actual time=0.005..184.339 rows=657,584 loops=1)

34. 3.536 74.546 ↓ 1.0 25,102 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 1209kB
35. 71.010 71.010 ↓ 1.0 25,102 1

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

36. 0.000 0.078 ↑ 1.0 2 1

Hash (cost=55.58..55.58 rows=2 width=33) (actual time=0.078..0.078 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
37. 0.003 0.078 ↑ 1.0 2 1

Nested Loop Left Join (cost=0.98..55.58 rows=2 width=33) (actual time=0.073..0.078 rows=2 loops=1)

38. 0.002 0.063 ↑ 1.0 2 1

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

39. 0.051 0.051 ↑ 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.028..0.051 rows=2 loops=1)

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

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

  • Index Cond: (id = rep_codes.rep_id)
42. 9.573 9.573 ↑ 1.0 1 3,191

Index Scan using contacts_pkey on contacts contacts_classic_users (cost=0.43..7.91 rows=1 width=23) (actual time=0.003..0.003 rows=1 loops=3,191)

  • Index Cond: (id = owners_contacts_join.contact_id)
43. 25.528 25.528 ↑ 1.0 1 3,191

Index Scan using custodial_securities_pkey on custodial_securities securities_custodial_latest_positions_mvw (cost=0.42..7.40 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=3,191)

  • Index Cond: (id = custodial_latest_positions_mvw.custodial_security_id)
44. 0.074 0.171 ↓ 1.0 521 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
45. 0.097 0.097 ↓ 1.0 521 1

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

46. 0.001 0.058 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
47. 0.004 0.057 ↑ 1.0 1 1

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

48. 0.047 0.047 ↑ 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.047..0.047 rows=1 loops=1)

  • Index Cond: ((classic_urn)::text = 'Workgroup:cfs:RCF'::text)
49. 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)
50. 0.000 3.191 ↓ 0.0 0 3,191

Nested Loop (cost=0.29..6.77 rows=1 width=38) (actual time=0.001..0.001 rows=0 loops=3,191)

51. 3.191 3.191 ↓ 0.0 0 3,191

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 time=0.001..0.001 rows=0 loops=3,191)

  • Index Cond: (custodial_accounts.id = custodial_account_id)
52. 0.000 0.000 ↓ 0.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) (never executed)

  • Index Cond: (custodial_cost_basis_summary_id = custodial_cost_basis_summaries.id)
53. 6.382 6.382 ↑ 1.0 1 3,191

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

  • Index Cond: (id = accounts.held_where_id)
54. 3.191 3.191 ↑ 1.0 10 3,191

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

55. 3.191 3.191 ↑ 1.0 1 3,191

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

  • Index Cond: (id = custodial_assets.custodial_asset_product_subgroup_id)
56. 19.146 19.146 ↑ 1.0 1 3,191

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

  • Index Cond: (custodial_account_id = accounts_custodial_accounts_mvw.custodial_account_id)
57. 25.632 25.632 ↑ 1.0 1 3,204

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

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