explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3NlZc : Optimization for: Test_Query; plan #8IRJ

Settings

Optimization path:

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

Hash Semi Join (cost=18,606,433.38..40,433,282.48 rows=124,042,204 width=158) (actual rows= loops=)

  • Hash Cond: (contacts.id = contacts_1.id)
2. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=18,140,701.07..36,864,238.92 rows=124,736,404 width=174) (actual rows= loops=)

  • Hash Cond: (account_forms_collections_accounts_join.contact_id = contacts_accounts.id)
3. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=17,155,395.21..33,592,216.14 rows=59,624,439 width=182) (actual rows= loops=)

  • Hash Cond: (positions.positions_collection_id = positions_collections.id)
4. 0.000 0.000 ↓ 0.0

Seq Scan on positions (cost=0.00..7,741,933.55 rows=253,501,555 width=8) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Hash (cost=16,544,574.51..16,544,574.51 rows=15,715,576 width=190) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=12,938,030.27..16,544,574.51 rows=15,715,576 width=190) (actual rows= loops=)

  • Hash Cond: ((canonical_accounts_custodial_accounts.account_number)::text = (positions_collections.account_number)::text)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,370,693.99..11,307,647.51 rows=133,321 width=192) (actual rows= loops=)

  • Hash Cond: (canonical_accounts_custodial_accounts.account_forms_collection_id = account_forms_collections_accounts_join.id)
8. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,332,406.75..11,266,527.20 rows=133,321 width=192) (actual rows= loops=)

  • Hash Cond: (canonical_accounts_custodial_accounts.held_where_id = held_wheres.id)
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,332,288.44..11,264,678.25 rows=133,321 width=200) (actual rows= loops=)

  • Hash Cond: (accounts_custodial_accounts_custodial_accounts_join.account_id = canonical_accounts_custodial_accounts.id)
10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,121,751.61..11,051,141.70 rows=133,321 width=182) (actual rows= loops=)

  • Hash Cond: (accounts_custodial_positions.id = accounts_custodial_accounts_custodial_accounts_join.custodial_account_id)
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,079,062.89..11,006,626.28 rows=133,321 width=182) (actual rows= loops=)

  • Hash Cond: (custodial_transactions.custodial_security_id = custodial_securities.id)
12. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=9,065,910.49..10,990,974.15 rows=133,321 width=178) (actual rows= loops=)

  • Hash Cond: (custodial_transactions.custodial_account_id = accounts_custodial_positions.id)
13. 0.000 0.000 ↓ 0.0

Seq Scan on custodial_transactions (cost=0.00..1,687,327.87 rows=63,040,687 width=16) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash (cost=9,065,801.24..9,065,801.24 rows=8,740 width=170) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,019,304.42..9,065,801.24 rows=8,740 width=170) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,019,303.99..9,005,265.11 rows=8,740 width=162) (actual rows= loops=)

  • Hash Cond: (rep_codes.rep_id = reps.id)
17. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,019,295.68..8,759,726.52 rows=65,451,433 width=152) (actual rows= loops=)

  • Hash Cond: (custodial_positions.custodial_account_id = custodial_accounts.id)
18. 0.000 0.000 ↓ 0.0

Seq Scan on custodial_positions (cost=0.00..5,345,698.41 rows=198,882,053 width=94) (actual rows= loops=)

  • Filter: ((market_value IS NOT NULL) AND (quantity IS NOT NULL) AND (quantity <> 0.0))
19. 0.000 0.000 ↓ 0.0

Hash (cost=1,002,294.94..1,002,294.94 rows=1,360,059 width=74) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash Join (cost=752,957.10..1,002,294.94 rows=1,360,059 width=74) (actual rows= loops=)

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

Hash Join (cost=222,339.46..442,779.08 rows=1,529,765 width=24) (actual rows= loops=)

  • Hash Cond: (accounts.id = accounts_custodial_accounts.account_id)
22. 0.000 0.000 ↓ 0.0

Seq Scan on accounts (cost=0.00..183,562.48 rows=2,157,948 width=16) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=203,217.40..203,217.40 rows=1,529,765 width=24) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash Join (cost=42,688.71..203,217.40 rows=1,529,765 width=24) (actual rows= loops=)

  • Hash Cond: (custodial_accounts.id = accounts_custodial_accounts.custodial_account_id)
25. 0.000 0.000 ↓ 0.0

Seq Scan on custodial_accounts (cost=0.00..103,904.02 rows=4,132,702 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash (cost=23,566.65..23,566.65 rows=1,529,765 width=16) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on accounts_custodial_accounts (cost=0.00..23,566.65 rows=1,529,765 width=16) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash (cost=520,423.91..520,423.91 rows=815,498 width=66) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash Join (cost=40,173.75..520,423.91 rows=815,498 width=66) (actual rows= loops=)

  • Hash Cond: (contacts.rep_code_mapping_id = rep_code_mappings.id)
30. 0.000 0.000 ↓ 0.0

Hash Join (cost=38,287.24..497,811.48 rows=914,259 width=66) (actual rows= loops=)

  • Hash Cond: (contacts.id = account_forms_collections.contact_id)
31. 0.000 0.000 ↓ 0.0

Seq Scan on contacts (cost=0.00..432,991.19 rows=1,545,819 width=50) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash (cost=26,821.55..26,821.55 rows=917,255 width=16) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on account_forms_collections (cost=0.00..26,821.55 rows=917,255 width=16) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash (cost=1,665.21..1,665.21 rows=17,704 width=16) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Hash Join (cost=664.88..1,665.21 rows=17,704 width=16) (actual rows= loops=)

  • Hash Cond: (rep_code_mappings.rep_code_id = rep_codes.id)
36. 0.000 0.000 ↓ 0.0

Seq Scan on rep_code_mappings (cost=0.00..466.43 rows=17,843 width=16) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash (cost=494.39..494.39 rows=13,639 width=16) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on rep_codes (cost=0.00..494.39 rows=13,639 width=16) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=8.30..8.30 rows=1 width=26) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Index Scan using index_reps_on_classic_urn on reps (cost=0.28..8.30 rows=1 width=26) (actual rows= loops=)

  • Index Cond: ((classic_urn)::text = 'Rep:cfs-registration:1448'::text)
41. 0.000 0.000 ↓ 0.0

Index Only Scan using custodial_accounts_pkey on custodial_accounts accounts_custodial_positions (cost=0.43..6.92 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = custodial_positions.custodial_account_id)
42. 0.000 0.000 ↓ 0.0

Hash (cost=8,943.29..8,943.29 rows=336,729 width=28) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on custodial_securities (cost=0.00..8,943.29 rows=336,729 width=28) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash (cost=23,566.65..23,566.65 rows=1,529,765 width=16) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on accounts_custodial_accounts accounts_custodial_accounts_custodial_accounts_join (cost=0.00..23,566.65 rows=1,529,765 width=16) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Hash (cost=183,562.48..183,562.48 rows=2,157,948 width=38) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Seq Scan on accounts canonical_accounts_custodial_accounts (cost=0.00..183,562.48 rows=2,157,948 width=38) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Hash (cost=96.14..96.14 rows=1,774 width=8) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=63.92..96.14 rows=1,774 width=8) (actual rows= loops=)

  • Hash Cond: (data_sources.held_where_id = held_wheres.id)
50. 0.000 0.000 ↓ 0.0

Seq Scan on data_sources (cost=0.00..22.11 rows=711 width=8) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Hash (cost=41.74..41.74 rows=1,774 width=8) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Seq Scan on held_wheres (cost=0.00..41.74 rows=1,774 width=8) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Hash (cost=26,821.55..26,821.55 rows=917,255 width=16) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Seq Scan on account_forms_collections account_forms_collections_accounts_join (cost=0.00..26,821.55 rows=917,255 width=16) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Hash (cost=2,340,618.46..2,340,618.46 rows=66,816,946 width=21) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Seq Scan on positions_collections (cost=0.00..2,340,618.46 rows=66,816,946 width=21) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Hash (cost=944,882.02..944,882.02 rows=3,233,907 width=8) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=797,440.47..944,882.02 rows=3,233,907 width=8) (actual rows= loops=)

  • Hash Cond: (phones.owner_id = contacts_accounts.id)
59. 0.000 0.000 ↓ 0.0

Seq Scan on phones (cost=0.00..82,435.58 rows=2,903,725 width=8) (actual rows= loops=)

  • Filter: ((owner_type)::text = 'Contacts::Contact'::text)
60. 0.000 0.000 ↓ 0.0

Hash (cost=775,913.37..775,913.37 rows=1,722,168 width=8) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=452,313.93..775,913.37 rows=1,722,168 width=8) (actual rows= loops=)

  • Hash Cond: (addresses.owner_id = contacts_accounts.id)
62. 0.000 0.000 ↓ 0.0

Seq Scan on addresses (cost=0.00..289,104.26 rows=1,727,350 width=8) (actual rows= loops=)

  • Filter: (((owner_type)::text = 'Contacts::Contact'::text) AND ((type)::text = ANY ('{Contacts::Address,Org::ProgramAddress,Org::BranchAddress,Org::RepAddress}'::text[])))
63. 0.000 0.000 ↓ 0.0

Hash (cost=432,991.19..432,991.19 rows=1,545,819 width=24) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Seq Scan on contacts contacts_accounts (cost=0.00..432,991.19 rows=1,545,819 width=24) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Hash (cost=446,517.11..446,517.11 rows=1,537,216 width=8) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Seq Scan on contacts contacts_1 (cost=0.00..446,517.11 rows=1,537,216 width=8) (actual rows= loops=)

  • Filter: ((realm_id = ANY ('{7,4,3,2,1}'::integer[])) OR (owner_id = 22489))