explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m6Ca

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 81.414 2,955.943 ↓ 4.0 109,928 1

Unique (cost=165,580.10..167,572.54 rows=27,482 width=335) (actual time=2,855.451..2,955.943 rows=109,928 loops=1)

2. 1,673.792 2,874.529 ↓ 4.0 109,928 1

Sort (cost=165,580.10..165,648.80 rows=27,482 width=335) (actual time=2,855.448..2,874.529 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, (max(a.last_login_ts)), b.order_requires_approval, cc.currency_symbol, cc.currency_code
  • Sort Method: quicksort Memory: 59,754kB
3. 55.578 1,200.737 ↓ 4.0 109,928 1

Hash Left Join (cost=37,961.18..163,553.82 rows=27,482 width=335) (actual time=799.335..1,200.737 rows=109,928 loops=1)

  • Hash Cond: ((b.account_id)::bpchar = (c.account_id)::bpchar)
4. 10.457 350.251 ↓ 1.4 10,289 1

Hash Join (cost=1,004.61..124,838.02 rows=7,516 width=287) (actual time=2.319..350.251 rows=10,289 loops=1)

  • Hash Cond: ((aa.currency_code)::text = (cc.currency_code)::text)
5. 7.228 339.763 ↑ 14.6 10,289 1

Nested Loop (cost=1,003.38..123,822.08 rows=150,328 width=284) (actual time=2.262..339.763 rows=10,289 loops=1)

6. 0.000 23.865 ↓ 1.3 10,289 1

Gather (cost=1,002.96..35,522.31 rows=7,912 width=276) (actual time=2.243..23.865 rows=10,289 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
7. 6.132 253.810 ↓ 1.1 5,144 2 / 2

Nested Loop (cost=2.96..33,731.11 rows=4,654 width=276) (actual time=0.637..253.810 rows=5,144 loops=2)

8. 7.259 237.389 ↓ 1.1 5,144 2 / 2

Nested Loop (cost=2.54..31,154.16 rows=4,654 width=306) (actual time=0.625..237.389 rows=5,144 loops=2)

9. 6.404 214.697 ↓ 1.1 5,144 2 / 2

Nested Loop (cost=2.11..28,768.92 rows=4,704 width=327) (actual time=0.341..214.697 rows=5,144 loops=2)

10. 4.802 192.859 ↓ 1.1 5,144 2 / 2

Nested Loop (cost=1.69..26,349.50 rows=4,762 width=282) (actual time=0.279..192.859 rows=5,144 loops=2)

11. 4.648 172.624 ↓ 1.1 5,144 2 / 2

Nested Loop (cost=1.26..23,348.24 rows=4,825 width=213) (actual time=0.208..172.624 rows=5,144 loops=2)

12. 6.293 29.075 ↓ 1.1 5,144 2 / 2

Nested Loop (cost=0.84..17,148.44 rows=4,884 width=107) (actual time=0.123..29.075 rows=5,144 loops=2)

13. 7.348 7.348 ↓ 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.045..7.348 rows=5,144 loops=2)

  • Index Cond: ((master_account_id)::bpchar = 'AC19110008004646'::bpchar)
  • Filter: is_active
14. 15.434 15.434 ↑ 1.0 1 10,289 / 2

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

  • Index Cond: ((account_id)::bpchar = (b.account_id)::bpchar)
  • Filter: ((role_name)::text = 'members'::text)
15. 138.901 138.901 ↑ 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.027..0.027 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
16. 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)
17. 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)
18. 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))
19. 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)
20. 144.046 308.670 ↑ 19.0 1 10,289

GroupAggregate (cost=0.42..10.78 rows=19 width=25) (actual time=0.030..0.030 rows=1 loops=10,289)

  • Group Key: a.account_id
21. 164.624 164.624 ↓ 1.6 30 10,289

Index Scan using ix_acct_contacts_account_id on acct_contacts a (cost=0.42..10.50 rows=19 width=25) (actual time=0.004..0.016 rows=30 loops=10,289)

  • Index Cond: ((account_id)::bpchar = (b.account_id)::bpchar)
22. 0.009 0.031 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=7) (actual time=0.031..0.031 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.022 0.022 ↑ 1.0 10 1

Seq Scan on lookup_currencies cc (cost=0.00..1.10 rows=10 width=7) (actual time=0.018..0.022 rows=10 loops=1)

24. 503.521 794.908 ↑ 1.0 778,067 1

Hash (cost=27,230.70..27,230.70 rows=778,070 width=34) (actual time=794.908..794.908 rows=778,067 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 58,341kB
25. 291.387 291.387 ↑ 1.0 778,067 1

Seq Scan on rel_acct_groups_accts c (cost=0.00..27,230.70 rows=778,070 width=34) (actual time=0.023..291.387 rows=778,067 loops=1)