explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Duay

Settings
# exclusive inclusive rows x rows loops node
1. 0.027 1,885.196 ↓ 266.0 266 1

Subquery Scan on os (cost=134,200.83..134,201.13 rows=1 width=633) (actual time=1,884.550..1,885.196 rows=266 loops=1)

2. 0.542 1,885.169 ↓ 266.0 266 1

Unique (cost=134,200.83..134,201.12 rows=1 width=3,808) (actual time=1,884.548..1,885.169 rows=266 loops=1)

3. 5.531 1,884.627 ↓ 396.0 396 1

Sort (cost=134,200.83..134,200.84 rows=1 width=3,808) (actual time=1,884.547..1,884.627 rows=396 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
  • Sort Method: quicksort Memory: 618kB
4. 2.307 1,879.096 ↓ 396.0 396 1

Nested Loop Left Join (cost=22,539.71..134,200.82 rows=1 width=3,808) (actual time=948.213..1,879.096 rows=396 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: 1734
5. 2.238 1,874.659 ↓ 2.6 2,130 1

Nested Loop Left Join (cost=22,539.29..133,361.12 rows=806 width=2,776) (actual time=147.703..1,874.659 rows=2,130 loops=1)

6. 1.493 1,870.291 ↓ 2.6 2,130 1

Nested Loop Left Join (cost=22,538.87..132,944.82 rows=806 width=2,772) (actual time=147.698..1,870.291 rows=2,130 loops=1)

7. 2.206 1,850.105 ↓ 2.7 2,077 1

Nested Loop Left Join (cost=22,538.44..132,431.27 rows=766 width=2,768) (actual time=147.682..1,850.105 rows=2,077 loops=1)

8. 2.455 1,845.822 ↓ 2.7 2,077 1

Nested Loop Left Join (cost=22,538.02..132,073.74 rows=766 width=2,768) (actual time=147.675..1,845.822 rows=2,077 loops=1)

9. 1.561 1,839.469 ↓ 2.5 1,949 1

Nested Loop Left Join (cost=22,537.59..131,674.68 rows=766 width=2,768) (actual time=147.665..1,839.469 rows=1,949 loops=1)

10. 0.579 1,828.163 ↓ 2.5 1,949 1

Nested Loop Left Join (cost=22,537.17..130,876.65 rows=766 width=2,768) (actual time=147.646..1,828.163 rows=1,949 loops=1)

11. 2.477 1,821.737 ↓ 2.5 1,949 1

Nested Loop Left Join (cost=22,536.75..130,481.01 rows=766 width=2,764) (actual time=147.638..1,821.737 rows=1,949 loops=1)

12. 28.961 1,811.464 ↓ 2.5 1,949 1

Hash Join (cost=22,536.33..130,053.94 rows=766 width=1,674) (actual time=147.626..1,811.464 rows=1,949 loops=1)

  • Hash Cond: (orders.buyer_vendor_id = buyer_vendors.id)
13. 406.113 1,774.312 ↓ 2.4 298,945 1

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

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

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

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

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

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

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

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

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

  • Join Filter: (order_milestones.milestone_id = milestones.id)
  • Rows Removed by Join Filter: 79952
18. 99.996 99.996 ↓ 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.011..99.996 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.018 0.018 ↑ 1.0 1 1

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

  • Filter: ((name)::text = 'submitted'::text)
  • Rows Removed by Filter: 2
21. 203.324 203.324 ↓ 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.007..203.324 rows=234,037 loops=1)

22. 194.171 194.171 ↓ 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.009..194.171 rows=298,945 loops=1)

  • Heap Fetches: 298945
23. 47.294 83.195 ↓ 1.0 253,536 1

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

  • Buckets: 131072 Batches: 4 Memory Usage: 3512kB
24. 35.901 35.901 ↓ 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.022..35.901 rows=253,536 loops=1)

25. 18.334 31.020 ↑ 1.0 54,291 1

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

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

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

27. 0.460 8.191 ↑ 1.0 1,472 1

Hash (cost=2,754.02..2,754.02 rows=1,538 width=4) (actual time=8.191..8.191 rows=1,472 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 68kB
28. 7.278 7.731 ↑ 1.0 1,472 1

Bitmap Heap Scan on buyer_vendors (cost=32.34..2,754.02 rows=1,538 width=4) (actual time=0.598..7.731 rows=1,472 loops=1)

  • Recheck Cond: (vendor_id = 750)
  • Heap Blocks: exact=1136
29. 0.453 0.453 ↑ 1.0 1,475 1

Bitmap Index Scan on index_buyer_vendors_on_vendor_id (cost=0.00..31.96 rows=1,538 width=0) (actual time=0.453..0.453 rows=1,475 loops=1)

  • Index Cond: (vendor_id = 750)
30. 7.796 7.796 ↑ 1.0 1 1,949

Index Scan using users_pkey on users (cost=0.42..0.55 rows=1 width=1,090) (actual time=0.004..0.004 rows=1 loops=1,949)

  • Index Cond: (id = order_milestones.created_by_id)
31. 5.847 5.847 ↑ 1.0 1 1,949

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.002..0.003 rows=1 loops=1,949)

  • Index Cond: (id = orders.buyer_vendor_id)
32. 9.745 9.745 ↑ 1.0 1 1,949

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

  • Index Cond: (id = buyer_vendors_for_sales_reps.account_id)
  • Heap Fetches: 1563
33. 3.898 3.898 ↑ 2.0 1 1,949

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

  • Index Cond: (account_id = accounts_for_sales_reps.id)
34. 2.077 2.077 ↑ 1.0 1 2,077

Index Only Scan using users_pkey on users users_for_sales_reps (cost=0.42..0.46 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=2,077)

  • Index Cond: (id = account_assignments.sales_rep_id)
  • Heap Fetches: 1531
35. 18.693 18.693 ↑ 1.0 1 2,077

Index Scan using index_order_contacts_on_order_id on order_contacts (cost=0.42..0.66 rows=1 width=8) (actual time=0.007..0.009 rows=1 loops=2,077)

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

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.001..0.001 rows=1 loops=2,130)

  • Index Cond: (id = orders.buyer_vendor_id)
37. 2.130 2.130 ↑ 1.0 1 2,130

Index Scan using accounts_pkey on accounts (cost=0.43..1.03 rows=1 width=443) (actual time=0.001..0.001 rows=1 loops=2,130)

  • Index Cond: (id = buyer_vendors_for_account.account_id)