explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Allc

Settings
# exclusive inclusive rows x rows loops node
1. 0.053 3,361.296 ↓ 82.0 82 1

Subquery Scan on os (cost=136,291.34..136,291.64 rows=1 width=633) (actual time=3,361.003..3,361.296 rows=82 loops=1)

2. 0.200 3,361.243 ↓ 82.0 82 1

Unique (cost=136,291.34..136,291.63 rows=1 width=3,809) (actual time=3,361.003..3,361.243 rows=82 loops=1)

3. 1.474 3,361.043 ↓ 114.0 114 1

Sort (cost=136,291.34..136,291.34 rows=1 width=3,809) (actual time=3,361.002..3,361.043 rows=114 loops=1)

  • Sort Key: orders.order_number, orders.submitted_at, (CASE WHEN (accounts.id IS NOT NULL) THEN 'no_rep'::text ELSE 'no_account'::text END), ((((users.first_name)::text || ' '::text) || (users.last_name)::text)), (COALESCE(accounts.name, buyers.name)), orders.id, orders.created_at, orders.updated_at, orders.account_id, orders.processed_by_id, orders.processed_at, orders.sales_rep_id, orders.buyer_user_id, orders.created_by, orders.date, orders.buyer_vendor_id, orders.buyer_note, orders.reply_to_token, orders.seen_by_rep, orders.delivery_date, orders.user_facing_order_id, orders.rep_confirmed_by_id, orders.rep_confirmed_at, orders.rep_confirmed_email, orders.rep_replied_by_id, orders.rep_replied_at, orders.rep_replied_email, orders.vendor_order_id, orders.vendor_ignored_by_id, orders.vendor_ignored_at, accounts.id, accounts.name, accounts.liquor_license_id, accounts.vendor_id, accounts.created_at, accounts.updated_at, accounts.note, accounts.internal_account_number, accounts.lead, accounts.deprecated_verified, accounts.deprecated_verified_by_id, accounts.deprecated_verified_at, accounts.state_abbr, accounts.street_address, accounts.city, accounts.zip, accounts.active, accounts.latitude, accounts.longitude, accounts.google_geocode_id, accounts.phone, accounts.allocations, accounts.delivery_instructions, accounts.extra, accounts.admin_verified_by_id, accounts.admin_verified_at, buyers.id, buyers.name, buyers.address, buyers.city, buyers.state, buyers.phone, buyers.buyer_type, buyers.liquor_license_id, buyers.created_at, buyers.updated_at, buyers.stripe_customer_token, buyers.zipcode_id, buyers.demo, buyers.class_b_buyer_vendor_id, order_milestones.id, order_milestones.milestone_id, order_milestones.created_by_id, order_milestones.created_at, order_milestones.updated_at, users.id, users.first_name, users.last_name, users.phone, users.title, users.email, users.encrypted_password, users.reset_password_token, users.remember_token, users.remember_created_at, users.sign_in_count, users.current_sign_in_at, users.last_sign_in_at, users.current_sign_in_ip, users.last_sign_in_ip, users.confirmation_token, users.confirmed_at, users.confirmation_sent_at, users.created_at, users.updated_at, users.created_by, users.updated_by, users.current_zone_id, users.zipcode_id, users.temporary_password, users.photo_file_name, users.photo_content_type, users.photo_file_size, users.photo_updated_at, users.unconfirmed_email, users.email_confirmation_token, users.prefs, users.active_company_id, users.active_company_type, users.demo, users.reset_password_sent_at, users.buyer_beta_enabled, users.order_waitlist, users.topshelf_enabled, users.last_activity_at, users.photo_processing
  • Sort Method: quicksort Memory: 190kB
4. 4.862 3,359.569 ↓ 114.0 114 1

Nested Loop Left Join (cost=22,959.40..136,291.33 rows=1 width=3,809) (actual time=2,442.033..3,359.569 rows=114 loops=1)

  • Filter: (((order_contacts.id IS NULL) AND (users_for_sales_reps.id IS NULL)) OR (accounts.id IS NULL))
  • Rows Removed by Filter: 7628
5. 12.682 3,339.223 ↓ 6.9 7,742 1

Nested Loop Left Join (cost=22,958.97..135,115.12 rows=1,129 width=2,777) (actual time=247.449..3,339.223 rows=7,742 loops=1)

6. 7.251 3,311.057 ↓ 6.9 7,742 1

Nested Loop Left Join (cost=22,958.55..134,531.99 rows=1,129 width=2,773) (actual time=247.440..3,311.057 rows=7,742 loops=1)

7. 8.254 2,373.068 ↓ 7.1 7,629 1

Nested Loop Left Join (cost=22,958.13..133,812.63 rows=1,073 width=2,769) (actual time=247.424..2,373.068 rows=7,629 loops=1)

8. 7.819 2,349.556 ↓ 7.1 7,629 1

Nested Loop Left Join (cost=22,957.71..133,310.93 rows=1,073 width=2,769) (actual time=247.293..2,349.556 rows=7,629 loops=1)

9. 5.136 2,296.131 ↓ 7.1 7,601 1

Nested Loop Left Join (cost=22,957.28..132,751.93 rows=1,073 width=2,769) (actual time=247.279..2,296.131 rows=7,601 loops=1)

10. 6.692 2,268.192 ↓ 7.1 7,601 1

Nested Loop Left Join (cost=22,956.85..131,634.07 rows=1,073 width=2,769) (actual time=247.092..2,268.192 rows=7,601 loops=1)

11. 6.983 2,208.293 ↓ 7.1 7,601 1

Nested Loop Left Join (cost=22,956.43..131,079.87 rows=1,073 width=2,765) (actual time=247.081..2,208.293 rows=7,601 loops=1)

12. 36.413 2,140.502 ↓ 7.1 7,601 1

Hash Join (cost=22,956.01..130,476.69 rows=1,073 width=1,674) (actual time=247.059..2,140.502 rows=7,601 loops=1)

  • Hash Cond: (orders.buyer_vendor_id = buyer_vendors.id)
13. 404.998 2,090.219 ↓ 2.4 298,945 1

Hash Left Join (cost=19,763.08..126,799.56 rows=126,260 width=1,674) (actual time=229.873..2,090.219 rows=298,945 loops=1)

  • Hash Cond: (buyer_vendors_public_orders_join_2.buyer_id = buyers.id)
14. 428.066 1,583.039 ↓ 2.4 298,945 1

Hash Left Join (cost=10,027.54..89,690.93 rows=126,260 width=555) (actual time=126.891..1,583.039 rows=298,945 loops=1)

  • Hash Cond: (orders.buyer_vendor_id = buyer_vendors_public_orders_join_2.id)
15. 96.079 1,028.487 ↓ 2.4 298,945 1

Merge Left Join (cost=2.02..59,182.34 rows=126,260 width=551) (actual time=0.184..1,028.487 rows=298,945 loops=1)

  • Merge Cond: (orders.id = order_milestones_public_orders.order_id)
16. 130.528 573.119 ↓ 2.2 217,455 1

Merge Join (cost=1.60..46,013.47 rows=99,135 width=551) (actual time=0.173..573.119 rows=217,455 loops=1)

  • Merge Cond: (order_milestones.order_id = orders.id)
17. 107.591 226.495 ↓ 2.2 217,455 1

Nested Loop (cost=0.42..15,377.04 rows=99,135 width=32) (actual time=0.158..226.495 rows=217,455 loops=1)

  • Join Filter: (order_milestones.milestone_id = milestones.id)
  • Rows Removed by Join Filter: 79952
18. 118.904 118.904 ↓ 1.0 297,407 1

Index Scan using index_order_milestones_on_order_id on order_milestones (cost=0.42..10,914.93 rows=297,405 width=32) (actual time=0.131..118.904 rows=297,407 loops=1)

19. 0.000 0.000 ↑ 1.0 1 297,407

Materialize (cost=0.00..1.04 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=297,407)

20. 0.024 0.024 ↑ 1.0 1 1

Seq Scan on milestones (cost=0.00..1.04 rows=1 width=4) (actual time=0.020..0.024 rows=1 loops=1)

  • Filter: ((name)::text = 'submitted'::text)
  • Rows Removed by Filter: 2
21. 216.096 216.096 ↓ 1.0 234,037 1

Index Scan using orders_pkey on orders (cost=0.42..28,814.71 rows=233,512 width=519) (actual time=0.010..216.096 rows=234,037 loops=1)

22. 359.289 359.289 ↓ 1.0 298,945 1

Index Only Scan using index_order_milestones_on_order_id on order_milestones order_milestones_public_orders (cost=0.42..10,914.93 rows=297,405 width=4) (actual time=0.008..359.289 rows=298,945 loops=1)

  • Heap Fetches: 298945
23. 53.915 126.486 ↓ 1.0 253,536 1

Hash (cost=5,865.34..5,865.34 rows=253,534 width=8) (actual time=126.486..126.486 rows=253,536 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 3512kB
24. 72.571 72.571 ↓ 1.0 253,536 1

Seq Scan on buyer_vendors buyer_vendors_public_orders_join_2 (cost=0.00..5,865.34 rows=253,534 width=8) (actual time=0.008..72.571 rows=253,536 loops=1)

25. 80.312 102.182 ↑ 1.0 54,291 1

Hash (cost=1,421.91..1,421.91 rows=54,291 width=1,123) (actual time=102.182..102.182 rows=54,291 loops=1)

  • Buckets: 4096 Batches: 16 Memory Usage: 505kB
26. 21.870 21.870 ↑ 1.0 54,291 1

Seq Scan on buyers (cost=0.00..1,421.91 rows=54,291 width=1,123) (actual time=0.006..21.870 rows=54,291 loops=1)

27. 0.342 13.870 ↓ 1.1 2,287 1

Hash (cost=3,165.99..3,165.99 rows=2,155 width=4) (actual time=13.869..13.870 rows=2,287 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 113kB
28. 13.311 13.528 ↓ 1.1 2,287 1

Bitmap Heap Scan on buyer_vendors (cost=41.12..3,165.99 rows=2,155 width=4) (actual time=0.371..13.528 rows=2,287 loops=1)

  • Recheck Cond: (vendor_id = 30)
  • Heap Blocks: exact=1617
29. 0.217 0.217 ↓ 1.1 2,289 1

Bitmap Index Scan on index_buyer_vendors_on_vendor_id (cost=0.00..40.58 rows=2,155 width=0) (actual time=0.217..0.217 rows=2,289 loops=1)

  • Index Cond: (vendor_id = 30)
30. 60.808 60.808 ↑ 1.0 1 7,601

Index Scan using users_pkey on users (cost=0.42..0.55 rows=1 width=1,091) (actual time=0.007..0.008 rows=1 loops=7,601)

  • Index Cond: (id = order_milestones.created_by_id)
31. 53.207 53.207 ↑ 1.0 1 7,601

Index Scan using buyer_vendors_pkey on buyer_vendors buyer_vendors_for_sales_reps (cost=0.42..0.51 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=7,601)

  • Index Cond: (id = orders.buyer_vendor_id)
32. 22.803 22.803 ↑ 1.0 1 7,601

Index Only Scan using accounts_pkey on accounts accounts_for_sales_reps (cost=0.43..1.03 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=7,601)

  • Index Cond: (id = buyer_vendors_for_sales_reps.account_id)
  • Heap Fetches: 7490
33. 45.606 45.606 ↑ 2.0 1 7,601

Index Scan using index_account_assignments_on_account_id on account_assignments (cost=0.43..0.50 rows=2 width=8) (actual time=0.006..0.006 rows=1 loops=7,601)

  • Index Cond: (account_id = accounts_for_sales_reps.id)
34. 15.258 15.258 ↑ 1.0 1 7,629

Index Only Scan using users_pkey on users users_for_sales_reps (cost=0.42..0.46 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=7,629)

  • Index Cond: (id = account_assignments.sales_rep_id)
  • Heap Fetches: 7174
35. 930.738 930.738 ↑ 1.0 1 7,629

Index Scan using index_order_contacts_on_order_id on order_contacts (cost=0.42..0.66 rows=1 width=8) (actual time=0.095..0.122 rows=1 loops=7,629)

  • Index Cond: (order_id = orders.id)
  • Filter: ((party)::text = 'vendor'::text)
  • Rows Removed by Filter: 1
36. 15.484 15.484 ↑ 1.0 1 7,742

Index Scan using buyer_vendors_pkey on buyer_vendors buyer_vendors_for_account (cost=0.42..0.51 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=7,742)

  • Index Cond: (id = orders.buyer_vendor_id)
37. 15.484 15.484 ↑ 1.0 1 7,742

Index Scan using accounts_pkey on accounts (cost=0.43..1.03 rows=1 width=443) (actual time=0.002..0.002 rows=1 loops=7,742)

  • Index Cond: (id = buyer_vendors_for_account.account_id)