explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I11v : Optimization for: plan #m6Ca

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 101.659 2,937.656 ↓ 477.9 109,928 1

Unique (cost=59,727.99..59,744.66 rows=230 width=335) (actual time=2,812.159..2,937.656 rows=109,928 loops=1)

2. 1,662.663 2,835.997 ↓ 477.9 109,928 1

Sort (cost=59,727.99..59,728.56 rows=230 width=335) (actual time=2,812.156..2,835.997 rows=109,928 loops=1)

  • Sort Key: b.account_id, ac_1.contact_id, b.tp_account_id, b.is_approved, b.master_denetwork_ts, b.is_default_master, b.sproutkey, a_1.company_name, ac_1.user_id, ac_1.full_name, ac_1.first_name, ac_1.middle_name, ac_1.last_name, ac_1.affiliation, aa_1.address1, aa_1.address2, aa_1.city, aa_1.region_code, aa_1.postal_code, aa_1.country_code, ae_1.email_address, ap_1.phone_number, a_1.is_active, c.acct_group_id, xa2.last_login_ts, b.order_requires_approval, cc.currency_symbol, cc.currency_code
  • Sort Method: quicksort Memory: 59,754kB
3. 0.000 1,173.334 ↓ 477.9 109,928 1

Gather (cost=49,927.64..59,718.96 rows=230 width=335) (actual time=790.310..1,173.334 rows=109,928 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
4. 22.861 1,194.182 ↓ 407.1 54,964 2 / 2

Nested Loop Left Join (cost=48,927.64..58,695.96 rows=135 width=335) (actual time=891.083..1,194.182 rows=54,964 loops=2)

5. 6.695 1,109.587 ↓ 139.0 5,144 2 / 2

Hash Join (cost=48,927.22..58,486.44 rows=37 width=287) (actual time=890.997..1,109.587 rows=5,144 loops=2)

  • Hash Cond: ((aa.currency_code)::text = (cc.currency_code)::text)
6. 6.282 1,102.851 ↓ 6.9 5,144 2 / 2

Nested Loop (cost=48,925.99..58,480.18 rows=746 width=284) (actual time=890.894..1,102.851 rows=5,144 loops=2)

7. 6.839 1,086.280 ↓ 6.9 5,144 2 / 2

Nested Loop (cost=48,925.57..58,067.12 rows=746 width=331) (actual time=890.869..1,086.280 rows=5,144 loops=2)

8. 7.149 1,064.007 ↓ 6.8 5,144 2 / 2

Nested Loop (cost=48,925.15..57,684.79 rows=754 width=352) (actual time=890.825..1,064.007 rows=5,144 loops=2)

9. 5.646 1,041.425 ↓ 6.7 5,144 2 / 2

Nested Loop (cost=48,924.72..57,296.63 rows=764 width=307) (actual time=890.787..1,041.425 rows=5,144 loops=2)

10. 2.919 1,020.345 ↓ 6.6 5,144 2 / 2

Nested Loop (cost=48,924.30..56,815.18 rows=774 width=238) (actual time=890.747..1,020.345 rows=5,144 loops=2)

11. 4.809 929.969 ↓ 6.6 5,144 2 / 2

Nested Loop (cost=48,923.87..55,821.23 rows=783 width=132) (actual time=890.705..929.969 rows=5,144 loops=2)

  • Join Filter: ((b.account_id)::bpchar = (a_1.account_id)::bpchar)
12. 7.563 904.582 ↓ 6.4 5,144 2 / 2

Hash Join (cost=48,923.45..54,875.88 rows=804 width=90) (actual time=890.633..904.582 rows=5,144 loops=2)

  • Hash Cond: ((b.account_id)::bpchar = (xa2.account_id)::bpchar)
13. 6.841 6.841 ↓ 1.1 5,144 2 / 2

Parallel Index Scan using ix_rel_masters_accounts_master_account_id on rel_masters_accounts b (cost=0.42..5,940.00 rows=4,894 width=65) (actual time=0.029..6.841 rows=5,144 loops=2)

  • Index Cond: ((master_account_id)::bpchar = 'AC19110008004646'::bpchar)
  • Filter: is_active
14. 68.615 890.178 ↓ 6.2 218,113 2 / 2

Hash (cost=48,485.91..48,485.91 rows=34,970 width=25) (actual time=890.178..890.178 rows=218,113 loops=2)

  • Buckets: 262,144 (originally 65536) Batches: 1 (originally 1) Memory Usage: 13,153kB
15. 41.018 821.563 ↓ 6.2 218,113 2 / 2

Subquery Scan on xa2 (cost=47,786.51..48,485.91 rows=34,970 width=25) (actual time=701.840..821.563 rows=218,113 loops=2)

16. 594.818 780.545 ↓ 6.2 218,113 2 / 2

HashAggregate (cost=47,786.51..48,136.21 rows=34,970 width=25) (actual time=701.838..780.545 rows=218,113 loops=2)

  • Group Key: a.account_id
17. 185.727 185.727 ↑ 1.0 664,575 2 / 2

Seq Scan on acct_contacts a (cost=0.00..44,463.34 rows=664,634 width=25) (actual time=0.022..185.727 rows=664,575 loops=2)

18. 20.578 20.578 ↑ 1.0 1 10,289 / 2

Index Scan using pk_acct_accounts on acct_accounts a_1 (cost=0.42..1.16 rows=1 width=42) (actual time=0.004..0.004 rows=1 loops=10,289)

  • Index Cond: ((account_id)::bpchar = (xa2.account_id)::bpchar)
  • Filter: ((role_name)::text = 'members'::text)
19. 87.457 87.457 ↑ 6.0 1 10,289 / 2

Index Scan using ix_acct_contacts_account_id on acct_contacts ac_1 (cost=0.42..1.21 rows=6 width=106) (actual time=0.016..0.017 rows=1 loops=10,289)

  • Index Cond: ((account_id)::bpchar = (a_1.account_id)::bpchar)
  • Filter: ((contact_type)::text = 'Primary'::text)
  • Rows Removed by Filter: 29
20. 15.434 15.434 ↑ 1.0 1 10,289 / 2

Index Scan using ix_acct_addresses_contact_id on acct_addresses aa_1 (cost=0.42..0.62 rows=1 width=69) (actual time=0.003..0.003 rows=1 loops=10,289)

  • Index Cond: ((contact_id)::bpchar = (ac_1.contact_id)::bpchar)
  • Filter: ((address_type)::text = 'Primary'::text)
21. 15.434 15.434 ↑ 1.0 1 10,289 / 2

Index Scan using ix_acct_e_info_contact_id on acct_e_info ae_1 (cost=0.42..0.51 rows=1 width=45) (actual time=0.003..0.003 rows=1 loops=10,289)

  • Index Cond: ((contact_id)::bpchar = (aa_1.contact_id)::bpchar)
  • Filter: ((e_info_type)::text = 'Primary'::text)
22. 15.434 15.434 ↑ 1.0 1 10,289 / 2

Index Scan using pk_acct_phones on acct_phones ap_1 (cost=0.42..0.51 rows=1 width=30) (actual time=0.003..0.003 rows=1 loops=10,289)

  • Index Cond: (((contact_id)::bpchar = (aa_1.contact_id)::bpchar) AND ((phone_type)::text = 'Primary'::text))
23. 10.289 10.289 ↑ 1.0 1 10,289 / 2

Index Scan using pk_acct_accounts on acct_accounts aa (cost=0.42..0.55 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=10,289)

  • Index Cond: ((account_id)::bpchar = (a_1.account_id)::bpchar)
24. 0.012 0.041 ↑ 1.0 10 2 / 2

Hash (cost=1.10..1.10 rows=10 width=7) (actual time=0.041..0.041 rows=10 loops=2)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
25. 0.029 0.029 ↑ 1.0 10 2 / 2

Seq Scan on lookup_currencies cc (cost=0.00..1.10 rows=10 width=7) (actual time=0.025..0.029 rows=10 loops=2)

26. 61.734 61.734 ↓ 1.6 11 10,289 / 2

Index Scan using ix_rel_acct_groups_accts_account_id on rel_acct_groups_accts c (cost=0.42..5.59 rows=7 width=34) (actual time=0.004..0.012 rows=11 loops=10,289)

  • Index Cond: ((b.account_id)::bpchar = (account_id)::bpchar)