explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SoM1

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 10.550 ↑ 1.0 1 1

Aggregate (cost=278.44..278.45 rows=1 width=8) (actual time=10.550..10.550 rows=1 loops=1)

2. 0.006 10.542 ↑ 9.0 1 1

Unique (cost=277.65..278.33 rows=9 width=457) (actual time=10.541..10.542 rows=1 loops=1)

3. 0.100 10.536 ↑ 9.0 1 1

Sort (cost=277.65..277.67 rows=9 width=457) (actual time=10.536..10.536 rows=1 loops=1)

  • Sort Key: accounts_account.id, accounts_account.uid, accounts_account.created, accounts_account.updated, accounts_account.deleted, accounts_account.referral, accounts_account.source, accounts_account.expected_monthly_credits, accounts_account.total_truckers, accounts_account.name, accounts_account.phone_number, accounts_account.primary_address_id, accounts_account.email, accounts_account.email_status, accounts_account.vat_number, accounts_account.trade_number, accounts_account.remote_id, accounts_account.siren, accounts_account.comments, accounts_account.pipedrive_id, accounts_account.is_qualified, accounts_account.intercom_id, accounts_account.subscription_id, accounts_account.settings_id, ((alternatives: SubPlan 1 or hashed SubPlan 2)), (string_agg((t7.city)::text, ' '::text)), (string_agg((t7.address)::text, ' '::text)), (string_agg((t7.postcode)::text, ' '::text)), (to_tsvector(((((((((COALESCE(accounts_companyview.name, ''::character varying)))::text || ' '::text) || COALESCE(string_agg((t7.city)::text, ' '::text), ''::text)) || ' '::text) || COALESCE(string_agg((t7.address)::text, ' '::text), ''::text)) || ' '::text) || COALESCE(string_agg((t7.postcode)::text, ' '::text), ''::text))))
  • Sort Method: quicksort Memory: 33kB
4. 7.658 10.436 ↑ 9.0 1 1

GroupAggregate (cost=179.57..277.51 rows=9 width=457) (actual time=9.893..10.436 rows=1 loops=1)

  • Group Key: accounts_account.id, ((alternatives: SubPlan 1 or hashed SubPlan 2)), (COALESCE(accounts_companyview.name, ''::character varying))
  • Filter: (to_tsvector(((((((((COALESCE(accounts_companyview.name, ''::character varying)))::text || ' '::text) || COALESCE(string_agg((t7.city)::text, ' '::text), ''::text)) || ' '::text) || COALESCE(string_agg((t7.address)::text, ' '::text), ''::text)) || ' '::text) || COALESCE(string_agg((t7.postcode)::text, ' '::text), ''::text))) @@ to_tsquery('colin:*'::text))
  • Rows Removed by Filter: 8
5. 0.785 2.778 ↓ 107.7 969 1

Sort (cost=179.57..179.60 rows=9 width=380) (actual time=2.683..2.778 rows=969 loops=1)

  • Sort Key: accounts_account.id, ((alternatives: SubPlan 1 or hashed SubPlan 2)), (COALESCE(accounts_companyview.name, ''::character varying))
  • Sort Method: quicksort Memory: 288kB
6. 0.252 1.993 ↓ 107.7 969 1

Nested Loop Left Join (cost=19.02..179.43 rows=9 width=380) (actual time=0.466..1.993 rows=969 loops=1)

7. 0.015 1.098 ↓ 12.0 24 1

Nested Loop (cost=18.74..73.42 rows=2 width=317) (actual time=0.333..1.098 rows=24 loops=1)

8. 0.017 0.953 ↓ 6.5 13 1

Nested Loop (cost=18.46..62.45 rows=2 width=321) (actual time=0.329..0.953 rows=13 loops=1)

9. 0.022 0.376 ↓ 6.7 40 1

Nested Loop (cost=18.19..29.54 rows=6 width=325) (actual time=0.200..0.376 rows=40 loops=1)

10. 0.039 0.274 ↓ 2.4 40 1

Hash Join (cost=18.04..23.46 rows=17 width=329) (actual time=0.196..0.274 rows=40 loops=1)

  • Hash Cond: (accounts_account.id = accounts_companyview.account_id)
11. 0.071 0.071 ↓ 1.0 156 1

Seq Scan on accounts_account (cost=0.00..4.50 rows=150 width=296) (actual time=0.017..0.071 rows=156 loops=1)

  • Filter: (NOT deleted)
12. 0.018 0.164 ↓ 2.1 40 1

Hash (cost=17.80..17.80 rows=19 width=33) (actual time=0.164..0.164 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
13. 0.017 0.146 ↓ 2.1 40 1

Hash Join (cost=8.84..17.80 rows=19 width=33) (actual time=0.101..0.146 rows=40 loops=1)

  • Hash Cond: (accounts_companyview.account_id = t4.account_id)
14. 0.050 0.050 ↑ 1.3 40 1

Seq Scan on accounts_companyview (cost=0.00..8.20 rows=51 width=25) (actual time=0.013..0.050 rows=40 loops=1)

  • Filter: (viewer_id = 17)
  • Rows Removed by Filter: 263
15. 0.017 0.079 ↑ 1.3 40 1

Hash (cost=8.20..8.20 rows=51 width=8) (actual time=0.079..0.079 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
16. 0.062 0.062 ↑ 1.3 40 1

Seq Scan on accounts_companyview t4 (cost=0.00..8.20 rows=51 width=8) (actual time=0.004..0.062 rows=40 loops=1)

  • Filter: (viewer_id = 17)
  • Rows Removed by Filter: 263
17. 0.080 0.080 ↑ 1.0 1 40

Index Scan using accounts_companyview_account_id_00c10ef6 on accounts_companyview t8 (cost=0.15..0.35 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=40)

  • Index Cond: (account_id = accounts_account.id)
  • Filter: (viewer_id = 17)
  • Rows Removed by Filter: 1
18. 0.560 0.560 ↓ 0.0 0 40

Index Scan using shipments_address_company_view_id_1119f23e on shipments_address (cost=0.28..5.47 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=40)

  • Index Cond: (company_view_id = t4.id)
  • Filter: (is_shipper AND (deleted IS NULL) AND (NOT is_copy))
  • Rows Removed by Filter: 18
19. 0.130 0.130 ↓ 2.0 2 13

Index Scan using shipments_address_company_view_id_1119f23e on shipments_address t9 (cost=0.28..5.47 rows=1 width=4) (actual time=0.008..0.010 rows=2 loops=13)

  • Index Cond: (company_view_id = t8.id)
  • Filter: ((deleted IS NULL) AND (NOT is_copy))
  • Rows Removed by Filter: 36
20. 0.528 0.528 ↓ 8.0 40 24

Index Scan using shipments_address_company_view_id_1119f23e on shipments_address t7 (cost=0.28..5.47 rows=5 width=38) (actual time=0.002..0.022 rows=40 loops=24)

  • Index Cond: (accounts_companyview.id = company_view_id)
21.          

SubPlan (forNested Loop Left Join)

22. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.21..16.90 rows=2 width=0) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..2.48 rows=1 width=8) (never executed)

  • Join Filter: (u1.id = u3.contact_id)
24. 0.000 0.000 ↓ 0.0 0

Seq Scan on accounts_contact u1 (cost=0.00..1.36 rows=1 width=4) (never executed)

  • Filter: (company_id = accounts_account.id)
25. 0.000 0.000 ↓ 0.0 0

Seq Scan on accounts_companycontactrelationship u3 (cost=0.00..1.10 rows=1 width=4) (never executed)

  • Filter: (company_id = 17)
26. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on accounts_contactinvitation u0 (cost=4.21..14.35 rows=7 width=4) (never executed)

  • Recheck Cond: (contact_id = u1.id)
27. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on accounts_contactinvitation_contact_id_262fc1ba (cost=0.00..4.21 rows=7 width=0) (never executed)

  • Index Cond: (contact_id = u1.id)
28. 0.008 0.115 ↑ 47.0 1 1

Nested Loop (cost=1.27..4.52 rows=47 width=4) (actual time=0.104..0.115 rows=1 loops=1)

29. 0.016 0.059 ↓ 24.0 24 1

Hash Join (cost=1.11..2.52 rows=1 width=12) (actual time=0.050..0.059 rows=24 loops=1)

  • Hash Cond: (u1_1.id = u3_1.contact_id)
30. 0.014 0.014 ↓ 1.2 36 1

Seq Scan on accounts_contact u1_1 (cost=0.00..1.29 rows=29 width=8) (actual time=0.010..0.014 rows=36 loops=1)

31. 0.008 0.029 ↓ 24.0 24 1

Hash (cost=1.10..1.10 rows=1 width=4) (actual time=0.029..0.029 rows=24 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.021 0.021 ↓ 24.0 24 1

Seq Scan on accounts_companycontactrelationship u3_1 (cost=0.00..1.10 rows=1 width=4) (actual time=0.014..0.021 rows=24 loops=1)

  • Filter: (company_id = 17)
  • Rows Removed by Filter: 13
33. 0.048 0.048 ↓ 0.0 0 24

Index Only Scan using accounts_contactinvitation_contact_id_262fc1ba on accounts_contactinvitation u0_1 (cost=0.15..1.93 rows=7 width=4) (actual time=0.002..0.002 rows=0 loops=24)

  • Index Cond: (contact_id = u1_1.id)
  • Heap Fetches: 1