explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QCLA

Settings
# exclusive inclusive rows x rows loops node
1. 3.770 25,011.618 ↓ 3,286.0 3,286 1

WindowAgg (cost=2,434,333.23..2,434,333.28 rows=1 width=315) (actual time=25,005.946..25,011.618 rows=3,286 loops=1)

2. 9.080 25,007.848 ↓ 3,286.0 3,286 1

HashAggregate (cost=2,434,333.23..2,434,333.25 rows=1 width=315) (actual time=25,005.928..25,007.848 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.152 24,998.768 ↓ 3,294.0 3,294 1

Nested Loop Semi Join (cost=2,179,041.19..2,434,333.16 rows=1 width=315) (actual time=20,537.534..24,998.768 rows=3,294 loops=1)

  • Join Filter: (account_forms_collections.contact_id = contacts_1.id)
4. 0.913 24,986.734 ↓ 3,294.0 3,294 1

Nested Loop Left Join (cost=2,179,040.76..2,434,325.71 rows=1 width=331) (actual time=20,537.514..24,986.734 rows=3,294 loops=1)

5. 2.046 24,975.978 ↓ 3,281.0 3,281 1

Nested Loop Left Join (cost=2,179,040.33..2,434,318.30 rows=1 width=312) (actual time=20,537.503..24,975.978 rows=3,281 loops=1)

6. 5.589 24,970.651 ↓ 3,281.0 3,281 1

Nested Loop Left Join (cost=2,179,040.19..2,434,316.12 rows=1 width=295) (actual time=20,537.496..24,970.651 rows=3,281 loops=1)

  • Join Filter: (custodial_asset_classes.id = custodial_assets.custodial_asset_class_id)
  • Rows Removed by Join Filter: 29529
7. 0.127 24,961.781 ↓ 3,281.0 3,281 1

Nested Loop Left Join (cost=2,179,040.19..2,434,314.89 rows=1 width=284) (actual time=20,537.487..24,961.781 rows=3,281 loops=1)

8. 0.543 24,955.092 ↓ 3,281.0 3,281 1

Nested Loop Left Join (cost=2,179,039.91..2,434,308.15 rows=1 width=263) (actual time=20,537.478..24,955.092 rows=3,281 loops=1)

  • Join Filter: (custodial_securities.id = custodial_cost_basis_summaries.custodial_security_id)
9. 1.535 24,951.268 ↓ 3,281.0 3,281 1

Hash Join (cost=2,179,039.62..2,434,301.37 rows=1 width=257) (actual time=20,537.471..24,951.268 rows=3,281 loops=1)

  • Hash Cond: (owners_contacts_join.workgroup_id = workgroups.id)
10. 1.523 24,949.707 ↓ 27.3 3,281 1

Hash Left Join (cost=2,179,023.01..2,434,284.30 rows=120 width=231) (actual time=20,537.436..24,949.707 rows=3,281 loops=1)

  • Hash Cond: (securities_custodial_latest_positions_mvw.custodial_asset_id = custodial_assets.id)
11. 2.566 24,948.042 ↓ 27.3 3,281 1

Nested Loop Left Join (cost=2,179,004.33..2,434,263.97 rows=120 width=223) (actual time=20,537.280..24,948.042 rows=3,281 loops=1)

12. 0.624 24,932.352 ↓ 27.3 3,281 1

Nested Loop (cost=2,179,003.91..2,433,375.12 rows=120 width=223) (actual time=20,537.267..24,932.352 rows=3,281 loops=1)

13. 115.202 24,921.885 ↓ 15.9 3,281 1

Hash Join (cost=2,179,003.48..2,431,734.66 rows=207 width=208) (actual time=20,537.246..24,921.885 rows=3,281 loops=1)

  • Hash Cond: (contacts.rep_code_mapping_id = rep_code_mappings_contacts_join.id)
14. 543.993 24,806.623 ↑ 1.3 1,801,671 1

Hash Join (cost=2,178,947.87..2,423,096.01 rows=2,288,359 width=191) (actual time=20,432.575..24,806.623 rows=1,801,671 loops=1)

  • Hash Cond: (contacts.owner_id = owners_contacts_join.id)
15. 911.582 24,251.285 ↑ 1.3 1,801,671 1

Hash Join (cost=2,176,790.96..2,375,132.19 rows=2,292,332 width=183) (actual time=20,421.217..24,251.285 rows=1,801,671 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_security_id = custodial_securities.id)
16. 2,176.802 23,053.373 ↑ 1.3 1,807,324 1

Hash Join (cost=2,146,141.78..2,301,452.87 rows=2,297,923 width=126) (actual time=20,134.729..23,053.373 rows=1,807,324 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_account_id = custodial_accounts.id)
17. 742.352 742.352 ↑ 1.0 2,793,495 1

Seq Scan on custodial_latest_positions_mvw (cost=0.00..80,119.86 rows=2,799,041 width=16) (actual time=0.011..742.352 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
18. 843.569 20,134.219 ↓ 1.2 1,418,395 1

Hash (cost=2,111,542.07..2,111,542.07 rows=1,150,297 width=118) (actual time=20,134.219..20,134.219 rows=1,418,395 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 56035kB
19. 2,299.332 19,290.650 ↓ 1.2 1,418,395 1

Hash Join (cost=1,867,343.18..2,111,542.07 rows=1,150,297 width=118) (actual time=12,717.827..19,290.650 rows=1,418,395 loops=1)

  • Hash Cond: (accounts_custodial_accounts_mvw.custodial_account_id = custodial_accounts.id)
20. 2,132.137 15,266.438 ↓ 1.2 1,418,395 1

Hash Join (cost=1,648,419.26..1,808,942.08 rows=1,150,297 width=95) (actual time=10,992.657..15,266.438 rows=1,418,395 loops=1)

  • Hash Cond: (accounts.account_forms_collection_id = account_forms_collections.id)
21. 1,836.717 4,869.353 ↑ 1.0 1,418,568 1

Hash Join (cost=330,597.46..426,070.50 rows=1,418,625 width=36) (actual time=2,727.508..4,869.353 rows=1,418,568 loops=1)

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

  • Filter: ((number)::text <> ''::text)
  • Rows Removed by Filter: 1
23. 578.250 2,727.280 ↑ 1.0 2,831,206 1

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

  • Buckets: 131072 Batches: 4 Memory Usage: 44266kB
24. 2,149.030 2,149.030 ↑ 1.0 2,831,206 1

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

25. 408.664 8,264.948 ↓ 1.0 1,076,079 1

Hash (cost=1,290,739.97..1,290,739.97 rows=1,074,866 width=75) (actual time=8,264.948..8,264.948 rows=1,076,079 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 59128kB
26. 3,307.691 7,856.284 ↓ 1.0 1,076,079 1

Hash Join (cost=51,136.36..1,290,739.97 rows=1,074,866 width=75) (actual time=663.619..7,856.284 rows=1,076,079 loops=1)

  • Hash Cond: (contacts.id = account_forms_collections.contact_id)
27. 3,885.537 3,885.537 ↓ 1.0 1,725,538 1

Seq Scan on contacts (cost=0.00..1,203,077.78 rows=1,718,478 width=59) (actual time=0.040..3,885.537 rows=1,725,538 loops=1)

28. 496.439 663.056 ↑ 1.2 1,076,079 1

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

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

30. 824.794 1,724.880 ↓ 1.0 4,867,218 1

Hash (cost=129,918.52..129,918.52 rows=4,847,952 width=23) (actual time=1,724.880..1,724.880 rows=4,867,218 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 33865kB
31. 900.086 900.086 ↓ 1.0 4,867,218 1

Seq Scan on custodial_accounts (cost=0.00..129,918.52 rows=4,847,952 width=23) (actual time=0.003..900.086 rows=4,867,218 loops=1)

32. 138.112 286.330 ↓ 1.0 657,713 1

Hash (cost=22,427.97..22,427.97 rows=657,697 width=57) (actual time=286.330..286.330 rows=657,713 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 46754kB
33. 148.218 148.218 ↓ 1.0 657,713 1

Seq Scan on custodial_securities (cost=0.00..22,427.97 rows=657,697 width=57) (actual time=0.005..148.218 rows=657,713 loops=1)

34. 3.305 11.345 ↓ 1.0 25,104 1

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

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

36. 0.001 0.060 ↑ 1.0 2 1

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

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

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

38. 0.003 0.052 ↑ 1.0 2 1

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

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

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

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

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

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

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

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

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.004..0.004 rows=1 loops=3,281)

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

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

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

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

46. 0.002 0.026 ↑ 1.0 1 1

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

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

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

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

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

  • Index Cond: (workgroup_id = workgroups.id)
50. 3.281 3.281 ↓ 0.0 0 3,281

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

51. 0.000 0.000 ↓ 0.0 0 3,281

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.000..0.000 rows=0 loops=3,281)

  • 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.562 6.562 ↑ 1.0 1 3,281

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

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

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

55. 3.281 3.281 ↑ 1.0 1 3,281

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

  • Index Cond: (id = custodial_assets.custodial_asset_product_subgroup_id)
56. 9.843 9.843 ↑ 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.002..0.003 rows=1 loops=3,281)

  • Index Cond: (custodial_account_id = accounts_custodial_accounts_mvw.custodial_account_id)
57. 9.882 9.882 ↑ 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.003..0.003 rows=1 loops=3,294)

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