explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J9Bg

Settings
# exclusive inclusive rows x rows loops node
1. 0.081 7,199.014 ↓ 46.0 46 1

WindowAgg (cost=1,244,216.02..1,244,216.06 rows=1 width=317) (actual time=7,198.934..7,199.014 rows=46 loops=1)

2. 0.191 7,198.933 ↓ 46.0 46 1

HashAggregate (cost=1,244,216.02..1,244,216.04 rows=1 width=317) (actual time=7,198.908..7,198.933 rows=46 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. 0.078 7,198.742 ↓ 46.0 46 1

Nested Loop Semi Join (cost=403,596.96..1,244,215.95 rows=1 width=317) (actual time=4,377.191..7,198.742 rows=46 loops=1)

  • Join Filter: (account_forms_collections.contact_id = contacts_1.id)
4. 0.061 7,198.572 ↓ 46.0 46 1

Nested Loop Left Join (cost=403,596.53..1,244,208.50 rows=1 width=333) (actual time=4,377.180..7,198.572 rows=46 loops=1)

5. 0.038 7,198.281 ↓ 46.0 46 1

Nested Loop Left Join (cost=403,596.10..1,244,201.09 rows=1 width=314) (actual time=4,377.162..7,198.281 rows=46 loops=1)

6. 0.143 7,198.151 ↓ 46.0 46 1

Nested Loop Left Join (cost=403,595.96..1,244,198.91 rows=1 width=297) (actual time=4,377.155..7,198.151 rows=46 loops=1)

  • Join Filter: (custodial_asset_classes.id = custodial_assets.custodial_asset_class_id)
  • Rows Removed by Join Filter: 414
7. 0.045 7,197.962 ↓ 46.0 46 1

Nested Loop Left Join (cost=403,595.96..1,244,197.68 rows=1 width=286) (actual time=4,377.147..7,197.962 rows=46 loops=1)

8. 0.072 7,197.779 ↓ 46.0 46 1

Nested Loop Left Join (cost=403,595.68..1,244,190.95 rows=1 width=265) (actual time=4,377.131..7,197.779 rows=46 loops=1)

  • Join Filter: (custodial_securities.id = custodial_cost_basis_summaries.custodial_security_id)
9. 0.052 7,197.661 ↓ 46.0 46 1

Hash Join (cost=403,595.39..1,244,184.16 rows=1 width=259) (actual time=4,377.124..7,197.661 rows=46 loops=1)

  • Hash Cond: (owners_contacts_join.workgroup_id = workgroups.id)
10. 0.061 7,197.567 ↓ 15.3 46 1

Hash Left Join (cost=403,578.78..1,244,167.53 rows=3 width=233) (actual time=4,377.073..7,197.567 rows=46 loops=1)

  • Hash Cond: (securities_custodial_latest_positions_mvw.custodial_asset_id = custodial_assets.id)
11. 0.062 7,197.347 ↓ 15.3 46 1

Nested Loop Left Join (cost=403,560.10..1,244,148.81 rows=3 width=225) (actual time=4,376.901..7,197.347 rows=46 loops=1)

12. 0.072 7,196.963 ↓ 15.3 46 1

Nested Loop (cost=403,559.68..1,244,126.56 rows=3 width=225) (actual time=4,376.888..7,196.963 rows=46 loops=1)

13. 3.031 7,196.753 ↓ 9.2 46 1

Hash Join (cost=403,559.25..1,244,086.93 rows=5 width=210) (actual time=4,376.878..7,196.753 rows=46 loops=1)

  • Hash Cond: (contacts.rep_code_mapping_id = rep_code_mappings_contacts_join.id)
14. 20.432 7,193.636 ↑ 2.1 25,136 1

Hash Join (cost=403,503.64..1,243,830.10 rows=53,648 width=193) (actual time=4,180.602..7,193.636 rows=25,136 loops=1)

  • Hash Cond: (contacts.owner_id = owners_contacts_join.id)
15. 32.621 7,160.901 ↑ 2.1 25,136 1

Nested Loop (cost=401,346.74..1,240,599.34 rows=53,738 width=185) (actual time=4,168.276..7,160.901 rows=25,136 loops=1)

16. 30.994 6,977.464 ↑ 2.6 25,136 1

Hash Join (cost=401,346.31..753,698.10 rows=65,359 width=126) (actual time=4,168.234..6,977.464 rows=25,136 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_security_id = custodial_securities.id)
17. 772.582 6,625.781 ↑ 2.6 25,142 1

Hash Join (cost=370,700.37..721,825.28 rows=65,518 width=68) (actual time=3,847.354..6,625.781 rows=25,142 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_account_id = custodial_accounts.id)
18. 46.137 3,289.999 ↑ 2.6 25,142 1

Hash Join (cost=157,067.90..478,043.76 rows=65,518 width=53) (actual time=1,281.494..3,289.999 rows=25,142 loops=1)

  • Hash Cond: (accounts.account_forms_collection_id = account_forms_collections.id)
19. 692.816 2,546.010 ↑ 2.6 25,142 1

Hash Join (cost=105,931.54..425,253.81 rows=66,561 width=53) (actual time=583.225..2,546.010 rows=25,142 loops=1)

  • Hash Cond: (accounts.id = accounts_custodial_accounts_mvw.account_id)
20. 1,270.718 1,270.718 ↓ 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.009..1,270.718 rows=2,831,153 loops=1)

21. 5.506 582.476 ↑ 2.6 25,142 1

Hash (cost=105,099.53..105,099.53 rows=66,561 width=32) (actual time=582.476..582.476 rows=25,142 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1572kB
22. 81.001 576.970 ↑ 2.6 25,142 1

Merge Join (cost=45,081.41..105,099.53 rows=66,561 width=32) (actual time=53.157..576.970 rows=25,142 loops=1)

  • Merge Cond: (accounts_custodial_accounts_mvw.custodial_account_id = custodial_latest_positions_mvw.custodial_account_id)
23. 439.330 439.330 ↑ 1.0 1,374,716 1

Index Scan using index_custodial_account_id_on_acamvw on accounts_custodial_accounts_mvw (cost=0.43..55,488.38 rows=1,418,625 width=16) (actual time=0.026..439.330 rows=1,374,716 loops=1)

  • Filter: ((number)::text <> ''::text)
  • Rows Removed by Filter: 1
24. 11.911 56.639 ↑ 1.0 65,285 1

Sort (cost=45,080.90..45,245.25 rows=65,741 width=16) (actual time=53.062..56.639 rows=65,285 loops=1)

  • Sort Key: custodial_latest_positions_mvw.custodial_account_id
  • Sort Method: quicksort Memory: 4592kB
25. 36.595 44.728 ↑ 1.0 65,177 1

Bitmap Heap Scan on custodial_latest_positions_mvw (cost=1,398.28..39,820.14 rows=65,741 width=16) (actual time=8.831..44.728 rows=65,177 loops=1)

  • Recheck Cond: ((valued_on >= '2019-07-31'::date) AND (valued_on <= '2019-08-31'::date))
  • Heap Blocks: exact=5209
26. 8.133 8.133 ↑ 1.0 65,177 1

Bitmap Index Scan on valued_on_custodial_latest_positions_mvw (cost=0.00..1,381.84 rows=65,741 width=0) (actual time=8.133..8.133 rows=65,177 loops=1)

  • Index Cond: ((valued_on >= '2019-07-31'::date) AND (valued_on <= '2019-08-31'::date))
27. 412.285 697.852 ↓ 1.0 1,307,579 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 59485kB
28. 285.567 285.567 ↓ 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.007..285.567 rows=1,307,579 loops=1)

29. 1,172.260 2,563.200 ↓ 1.0 4,865,594 1

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

  • Buckets: 131072 Batches: 8 (originally 4) Memory Usage: 65537kB
30. 1,390.940 1,390.940 ↓ 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.010..1,390.940 rows=4,865,594 loops=1)

31. 155.410 320.689 ↓ 1.0 657,584 1

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

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

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

33. 150.816 150.816 ↑ 1.0 1 25,136

Index Scan using contacts_pkey on contacts (cost=0.43..7.44 rows=1 width=59) (actual time=0.006..0.006 rows=1 loops=25,136)

  • Index Cond: (id = account_forms_collections.contact_id)
34. 3.400 12.303 ↓ 1.0 25,102 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 1209kB
35. 8.903 8.903 ↓ 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.004..8.903 rows=25,102 loops=1)

36. 0.001 0.086 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
37. 0.002 0.085 ↑ 1.0 2 1

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

38. 0.004 0.075 ↑ 1.0 2 1

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

39. 0.061 0.061 ↑ 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.035..0.061 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.005..0.005 rows=1 loops=2)

  • Index Cond: (rep_code_id = rep_codes.id)
41. 0.008 0.008 ↑ 1.0 1 2

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

  • Index Cond: (id = rep_codes.rep_id)
42. 0.138 0.138 ↑ 1.0 1 46

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

  • Index Cond: (id = owners_contacts_join.contact_id)
43. 0.322 0.322 ↑ 1.0 1 46

Index Scan using custodial_securities_pkey on custodial_securities securities_custodial_latest_positions_mvw (cost=0.42..7.41 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=46)

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

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

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

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

46. 0.002 0.042 ↑ 1.0 1 1

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

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

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

48. 0.030 0.030 ↑ 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.030..0.030 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.006..0.006 rows=1 loops=1)

  • Index Cond: (workgroup_id = workgroups.id)
50. 0.000 0.046 ↓ 0.0 0 46

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

51. 0.046 0.046 ↓ 0.0 0 46

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=46)

  • 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. 0.138 0.138 ↑ 1.0 1 46

Index Scan using held_wheres_pkey on held_wheres (cost=0.28..6.73 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=46)

  • Index Cond: (id = accounts.held_where_id)
54. 0.046 0.046 ↑ 1.0 10 46

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

55. 0.092 0.092 ↑ 1.0 1 46

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.002..0.002 rows=1 loops=46)

  • Index Cond: (id = custodial_assets.custodial_asset_product_subgroup_id)
56. 0.230 0.230 ↑ 1.0 1 46

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=46)

  • Index Cond: (custodial_account_id = accounts_custodial_accounts_mvw.custodial_account_id)
57. 0.092 0.092 ↑ 1.0 1 46

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

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