explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vjep

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 0.342 ↑ 1.0 1 1

Nested Loop (cost=38.70..38.78 rows=1 width=1,808) (actual time=0.310..0.342 rows=1 loops=1)

  • Join Filter: (s.id = notifications.supplier_id)
2.          

CTE w_supplier

3. 0.028 0.078 ↑ 1.0 1 1

Hash Right Join (cost=4.06..6.54 rows=1 width=444) (actual time=0.053..0.078 rows=1 loops=1)

  • Hash Cond: (da.id = s_1.debit_account_id)
4. 0.018 0.018 ↓ 1.2 164 1

Seq Scan on debit_accounts da (cost=0.00..2.41 rows=135 width=41) (actual time=0.006..0.018 rows=164 loops=1)

5. 0.006 0.032 ↑ 1.0 1 1

Hash (cost=4.06..4.06 rows=1 width=407) (actual time=0.032..0.032 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
6. 0.026 0.026 ↑ 1.0 1 1

Index Scan using suppliers_shopify_id_key on suppliers s_1 (cost=0.05..4.06 rows=1 width=407) (actual time=0.025..0.026 rows=1 loops=1)

  • Index Cond: (shopify_id = 'gid://shopify/Customer/2690848882751'::text)
7.          

CTE w_notifications

8. 0.025 0.075 ↑ 1.0 1 1

GroupAggregate (cost=13.86..13.87 rows=1 width=36) (actual time=0.075..0.075 rows=1 loops=1)

  • Group Key: ws.id
9. 0.008 0.050 ↑ 5.0 1 1

Sort (cost=13.86..13.86 rows=5 width=84) (actual time=0.049..0.050 rows=1 loops=1)

  • Sort Key: ws.id
  • Sort Method: quicksort Memory: 25kB
10. 0.020 0.042 ↑ 5.0 1 1

Hash Right Join (cost=0.01..13.85 rows=5 width=84) (actual time=0.039..0.042 rows=1 loops=1)

  • Hash Cond: (n.supplier_id = ws.id)
11. 0.019 0.019 ↑ 24.9 41 1

Seq Scan on supplier_notifications n (cost=0.00..13.06 rows=1,020 width=84) (actual time=0.008..0.019 rows=41 loops=1)

12. 0.002 0.003 ↑ 1.0 1 1

Hash (cost=0.01..0.01 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on w_supplier ws (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

14.          

CTE w_contacts

15. 0.005 0.031 ↑ 1.0 4 1

Nested Loop (cost=8.23..14.33 rows=4 width=94) (actual time=0.029..0.031 rows=4 loops=1)

16. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on w_supplier s_2 (cost=0.00..0.01 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=1)

17. 0.016 0.026 ↑ 1.0 4 1

Bitmap Heap Scan on contacts c (cost=8.23..14.31 rows=4 width=94) (actual time=0.025..0.026 rows=4 loops=1)

  • Recheck Cond: ((s_2.ordering_contact_id = id) OR (s_2.logistics_contact_id = id) OR (s_2.authorized_person_contact_id = id) OR (s_2.accounts_contact_id = id))
  • Heap Blocks: exact=1
18. 0.002 0.010 ↓ 0.0 0 1

BitmapOr (cost=8.23..8.23 rows=4 width=0) (actual time=0.010..0.010 rows=0 loops=1)

19. 0.005 0.005 ↑ 1.0 1 1

Bitmap Index Scan on contacts_pkey (cost=0.00..2.06 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (s_2.ordering_contact_id = id)
20. 0.001 0.001 ↑ 1.0 1 1

Bitmap Index Scan on contacts_pkey (cost=0.00..2.06 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

  • Index Cond: (s_2.logistics_contact_id = id)
21. 0.001 0.001 ↑ 1.0 1 1

Bitmap Index Scan on contacts_pkey (cost=0.00..2.06 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

  • Index Cond: (s_2.authorized_person_contact_id = id)
22. 0.001 0.001 ↑ 1.0 1 1

Bitmap Index Scan on contacts_pkey (cost=0.00..2.06 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

  • Index Cond: (s_2.accounts_contact_id = id)
23.          

CTE w_addresses

24. 0.003 0.063 ↑ 1.0 2 1

Nested Loop (cost=0.06..3.85 rows=2 width=54) (actual time=0.033..0.063 rows=2 loops=1)

25. 0.023 0.048 ↑ 1.0 2 1

Nested Loop (cost=0.00..3.33 rows=2 width=8) (actual time=0.020..0.048 rows=2 loops=1)

  • Join Filter: ((s_3.billing_address_id = sa.id) OR (s_3.company_address_id = sa.id))
  • Rows Removed by Join Filter: 260
26. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on w_supplier s_3 (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)

27. 0.025 0.025 ↓ 1.4 262 1

Seq Scan on supplier_addresses sa (cost=0.00..2.57 rows=189 width=8) (actual time=0.007..0.025 rows=262 loops=1)

28. 0.012 0.012 ↑ 1.0 1 2

Index Scan using addresses_pkey on addresses a (cost=0.06..0.26 rows=1 width=50) (actual time=0.006..0.006 rows=1 loops=2)

  • Index Cond: (id = sa.address_id)
29. 0.002 0.264 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.11..0.18 rows=1 width=1,776) (actual time=0.232..0.264 rows=1 loops=1)

  • Join Filter: (s.company_address_id = company_address.supplier_address_id)
  • Rows Removed by Join Filter: 1
30. 0.003 0.234 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.11..0.16 rows=1 width=1,616) (actual time=0.231..0.234 rows=1 loops=1)

  • Join Filter: (s.billing_address_id = billing_address.supplier_address_id)
  • Rows Removed by Join Filter: 1
31. 0.016 0.193 ↑ 1.0 1 1

Hash Right Join (cost=0.11..0.14 rows=1 width=1,456) (actual time=0.191..0.193 rows=1 loops=1)

  • Hash Cond: (accounts_contact.id = s.accounts_contact_id)
32. 0.000 0.000 ↑ 1.0 4 1

CTE Scan on w_contacts accounts_contact (cost=0.00..0.02 rows=4 width=164) (actual time=0.000..0.000 rows=4 loops=1)

33. 0.003 0.177 ↑ 1.0 1 1

Hash (cost=0.11..0.11 rows=1 width=1,296) (actual time=0.177..0.177 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.015 0.174 ↑ 1.0 1 1

Hash Right Join (cost=0.08..0.11 rows=1 width=1,296) (actual time=0.168..0.174 rows=1 loops=1)

  • Hash Cond: (authorized_person_contact.id = s.authorized_person_contact_id)
35. 0.001 0.001 ↑ 1.0 4 1

CTE Scan on w_contacts authorized_person_contact (cost=0.00..0.02 rows=4 width=164) (actual time=0.000..0.001 rows=4 loops=1)

36. 0.004 0.158 ↑ 1.0 1 1

Hash (cost=0.07..0.07 rows=1 width=1,136) (actual time=0.157..0.158 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.013 0.154 ↑ 1.0 1 1

Hash Right Join (cost=0.04..0.07 rows=1 width=1,136) (actual time=0.151..0.154 rows=1 loops=1)

  • Hash Cond: (logistics_contact.id = s.logistics_contact_id)
38. 0.001 0.001 ↑ 1.0 4 1

CTE Scan on w_contacts logistics_contact (cost=0.00..0.02 rows=4 width=164) (actual time=0.000..0.001 rows=4 loops=1)

39. 0.003 0.140 ↑ 1.0 1 1

Hash (cost=0.04..0.04 rows=1 width=976) (actual time=0.140..0.140 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.014 0.137 ↑ 1.0 1 1

Hash Right Join (cost=0.01..0.04 rows=1 width=976) (actual time=0.128..0.137 rows=1 loops=1)

  • Hash Cond: (ordering_contact.id = s.ordering_contact_id)
41. 0.036 0.036 ↑ 1.0 4 1

CTE Scan on w_contacts ordering_contact (cost=0.00..0.02 rows=4 width=164) (actual time=0.030..0.036 rows=4 loops=1)

42. 0.006 0.087 ↑ 1.0 1 1

Hash (cost=0.01..0.01 rows=1 width=816) (actual time=0.087..0.087 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.081 0.081 ↑ 1.0 1 1

CTE Scan on w_supplier s (cost=0.00..0.01 rows=1 width=816) (actual time=0.057..0.081 rows=1 loops=1)

44. 0.038 0.038 ↑ 1.0 2 1

CTE Scan on w_addresses billing_address (cost=0.00..0.01 rows=2 width=168) (actual time=0.035..0.038 rows=2 loops=1)

45. 0.028 0.028 ↑ 1.0 2 1

CTE Scan on w_addresses company_address (cost=0.00..0.01 rows=2 width=168) (actual time=0.000..0.028 rows=2 loops=1)

46. 0.076 0.076 ↑ 1.0 1 1

CTE Scan on w_notifications notifications (cost=0.00..0.01 rows=1 width=36) (actual time=0.075..0.076 rows=1 loops=1)

Planning time : 2.563 ms
Execution time : 0.691 ms