explain.depesz.com

PostgreSQL's explain analyze made readable

Result: btRxk : pos30_uniq_dates

Settings
# exclusive inclusive rows x rows loops node
1. 5.503 40,088.675 ↓ 3,265.0 3,265 1

WindowAgg (cost=2,543,807.56..2,543,807.63 rows=1 width=443) (actual time=40,079.905..40,088.675 rows=3,265 loops=1)

2. 20.967 40,083.172 ↓ 3,265.0 3,265 1

HashAggregate (cost=2,543,807.56..2,543,807.60 rows=1 width=443) (actual time=40,079.880..40,083.172 rows=3,265 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. 5.085 40,062.205 ↓ 3,273.0 3,273 1

Nested Loop Semi Join (cost=2,178,086.84..2,543,807.47 rows=1 width=443) (actual time=32,403.622..40,062.205 rows=3,273 loops=1)

  • Join Filter: (account_forms_collections.contact_id = contacts_1.id)
4. 6.444 40,014.571 ↓ 3,273.0 3,273 1

Nested Loop Left Join (cost=2,178,086.41..2,543,800.02 rows=1 width=459) (actual time=32,403.587..40,014.571 rows=3,273 loops=1)

5. 5.443 39,969.007 ↓ 3,260.0 3,260 1

Nested Loop Left Join (cost=2,177,961.54..2,531,458.60 rows=1 width=339) (actual time=32,403.532..39,969.007 rows=3,260 loops=1)

6. 7.412 39,940.744 ↓ 3,260.0 3,260 1

Nested Loop Left Join (cost=2,177,961.40..2,531,456.42 rows=1 width=322) (actual time=32,403.525..39,940.744 rows=3,260 loops=1)

  • Join Filter: (custodial_asset_classes.id = custodial_assets.custodial_asset_class_id)
  • Rows Removed by Join Filter: 29340
7. 3.243 39,923.552 ↓ 3,260.0 3,260 1

Nested Loop Left Join (cost=2,177,961.40..2,531,455.20 rows=1 width=311) (actual time=32,403.515..39,923.552 rows=3,260 loops=1)

8. 2.459 39,874.669 ↓ 3,260.0 3,260 1

Nested Loop Left Join (cost=2,177,961.12..2,531,448.46 rows=1 width=290) (actual time=32,403.498..39,874.669 rows=3,260 loops=1)

  • Join Filter: (custodial_securities.id = custodial_cost_basis_summaries.custodial_security_id)
9. 2.407 39,865.690 ↓ 3,260.0 3,260 1

Hash Join (cost=2,177,960.83..2,531,441.67 rows=1 width=284) (actual time=32,403.490..39,865.690 rows=3,260 loops=1)

  • Hash Cond: (owners_contacts_join.workgroup_id = workgroups.id)
10. 3.118 39,863.217 ↓ 28.8 3,260 1

Hash Left Join (cost=2,177,944.22..2,531,424.63 rows=113 width=258) (actual time=32,403.415..39,863.217 rows=3,260 loops=1)

  • Hash Cond: (securities_custodial_latest_positions_mvw.custodial_asset_id = custodial_assets.id)
11. 3.860 39,859.940 ↓ 28.8 3,260 1

Nested Loop Left Join (cost=2,177,925.54..2,531,404.40 rows=113 width=250) (actual time=32,403.231..39,859.940 rows=3,260 loops=1)

12. 3.873 39,784.360 ↓ 28.8 3,260 1

Nested Loop (cost=2,177,925.12..2,530,567.40 rows=113 width=250) (actual time=32,403.194..39,784.360 rows=3,260 loops=1)

13. 132.624 39,731.587 ↓ 16.7 3,260 1

Hash Join (cost=2,177,924.69..2,529,022.04 rows=195 width=235) (actual time=32,403.166..39,731.587 rows=3,260 loops=1)

  • Hash Cond: (contacts.rep_code_mapping_id = rep_code_mappings_contacts_join.id)
14. 704.455 39,598.830 ↑ 1.3 1,702,782 1

Hash Join (cost=2,177,869.08..2,520,870.85 rows=2,158,393 width=218) (actual time=31,982.425..39,598.830 rows=1,702,782 loops=1)

  • Hash Cond: (contacts.owner_id = owners_contacts_join.id)
15. 1,624.372 38,872.153 ↑ 1.3 1,702,782 1

Hash Join (cost=2,175,712.18..2,475,519.43 rows=2,161,058 width=210) (actual time=31,960.188..38,872.153 rows=1,702,782 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_security_id = custodial_securities.id)
16. 2,855.411 36,895.934 ↑ 1.3 1,708,380 1

Hash Join (cost=2,145,066.24..2,404,311.98 rows=2,165,822 width=152) (actual time=31,608.126..36,895.934 rows=1,708,380 loops=1)

  • Hash Cond: (custodial_latest_positions_mvw.custodial_account_id = custodial_accounts.id)
17. 2,438.858 2,438.858 ↓ 1.0 2,617,245 1

Seq Scan on custodial_latest_positions_mvw (cost=0.00..167,046.89 rows=2,614,900 width=41) (actual time=0.026..2,438.858 rows=2,617,245 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))
  • Rows Removed by Filter: 280836
18. 675.890 31,601.665 ↓ 1.2 1,418,330 1

Hash (cost=2,110,534.97..2,110,534.97 rows=1,148,021 width=119) (actual time=31,601.665..31,601.665 rows=1,418,330 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 56031kB
19. 3,344.088 30,925.775 ↓ 1.2 1,418,330 1

Hash Join (cost=1,861,947.75..2,110,534.97 rows=1,148,021 width=119) (actual time=21,361.364..30,925.775 rows=1,418,330 loops=1)

  • Hash Cond: (accounts_custodial_accounts_mvw.custodial_account_id = custodial_accounts.id)
20. 3,005.903 24,332.771 ↓ 1.2 1,418,330 1

Hash Join (cost=1,648,315.28..1,808,690.01 rows=1,148,021 width=96) (actual time=18,107.652..24,332.771 rows=1,418,330 loops=1)

  • Hash Cond: (accounts.account_forms_collection_id = account_forms_collections.id)
21. 2,615.643 6,338.385 ↑ 1.0 1,418,503 1

Hash Join (cost=330,139.71..425,490.94 rows=1,418,503 width=37) (actual time=3,116.998..6,338.385 rows=1,418,503 loops=1)

  • Hash Cond: (accounts_custodial_accounts_mvw.account_id = accounts.id)
22. 607.213 607.213 ↑ 1.0 1,418,503 1

Seq Scan on accounts_custodial_accounts_mvw (cost=0.00..32,818.30 rows=1,418,503 width=16) (actual time=0.026..607.213 rows=1,418,503 loops=1)

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

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

  • Buckets: 131072 Batches: 4 Memory Usage: 44266kB
24. 2,485.443 2,485.443 ↓ 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.019..2,485.443 rows=2,831,153 loops=1)

25. 826.407 14,988.483 ↓ 1.0 1,076,079 1

Hash (cost=1,291,093.75..1,291,093.75 rows=1,074,866 width=75) (actual time=14,988.483..14,988.483 rows=1,076,079 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 59128kB
26. 4,690.306 14,162.076 ↓ 1.0 1,076,079 1

Hash Join (cost=51,136.36..1,291,093.75 rows=1,074,866 width=75) (actual time=655.452..14,162.076 rows=1,076,079 loops=1)

  • Hash Cond: (contacts.id = account_forms_collections.contact_id)
27. 8,816.887 8,816.887 ↑ 1.0 1,725,536 1

Seq Scan on contacts (cost=0.00..1,203,219.29 rows=1,732,629 width=59) (actual time=0.024..8,816.887 rows=1,725,536 loops=1)

28. 367.463 654.883 ↑ 1.2 1,076,079 1

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

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

30. 1,357.754 3,248.916 ↓ 1.0 4,865,583 1

Hash (cost=128,052.54..128,052.54 rows=4,661,354 width=23) (actual time=3,248.916..3,248.916 rows=4,865,583 loops=1)

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

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

32. 162.931 351.847 ↓ 1.0 657,556 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 46740kB
33. 188.916 188.916 ↓ 1.0 657,556 1

Seq Scan on custodial_securities (cost=0.00..22,426.53 rows=657,553 width=58) (actual time=0.007..188.916 rows=657,556 loops=1)

34. 3.721 22.222 ↓ 1.0 25,102 1

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

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

36. 0.001 0.133 ↑ 1.0 2 1

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

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

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

38. 0.004 0.115 ↑ 1.0 2 1

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

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

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

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

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

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

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

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

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.022..0.022 rows=1 loops=3,260)

  • Index Cond: (id = custodial_latest_positions_mvw.custodial_security_id)
44. 0.067 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.092 0.092 ↓ 1.0 521 1

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

46. 0.002 0.066 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
47. 0.006 0.064 ↑ 1.0 1 1

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

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

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

  • Index Cond: (workgroup_id = workgroups.id)
50. 3.260 6.520 ↓ 0.0 0 3,260

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

51. 3.260 3.260 ↓ 0.0 0 3,260

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

  • 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. 45.640 45.640 ↑ 1.0 1 3,260

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

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

Seq Scan on custodial_asset_classes (cost=0.00..1.10 rows=10 width=19) (actual time=0.002..0.003 rows=10 loops=3,260)

55. 22.820 22.820 ↑ 1.0 1 3,260

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.007..0.007 rows=1 loops=3,260)

  • Index Cond: (id = custodial_assets.custodial_asset_product_subgroup_id)
56. 19.560 39.120 ↑ 7,083.0 1 3,260

Bitmap Heap Scan on contacts_mvbalances_mvw (cost=124.87..12,270.59 rows=7,083 width=136) (actual time=0.012..0.012 rows=1 loops=3,260)

  • Recheck Cond: (custodial_account_id = accounts_custodial_accounts_mvw.custodial_account_id)
  • Heap Blocks: exact=3273
57. 19.560 19.560 ↑ 7,083.0 1 3,260

Bitmap Index Scan on custodial_account_id_contacts_mvbalances_mvw (cost=0.00..123.09 rows=7,083 width=0) (actual time=0.006..0.006 rows=1 loops=3,260)

  • Index Cond: (custodial_account_id = accounts_custodial_accounts_mvw.custodial_account_id)
58. 42.549 42.549 ↑ 1.0 1 3,273

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

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