explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8xBA

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 173.625 ↓ 266.0 266 1

Subquery Scan on os (cost=24,658.07..24,658.33 rows=1 width=149) (actual time=173.044..173.625 rows=266 loops=1)

2. 0.487 173.591 ↓ 266.0 266 1

Unique (cost=24,658.07..24,658.32 rows=1 width=2,169) (actual time=173.041..173.591 rows=266 loops=1)

3. 3.701 173.104 ↓ 396.0 396 1

Sort (cost=24,658.07..24,658.07 rows=1 width=2,169) (actual time=173.039..173.104 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)), 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, 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: 572kB
4. 2.992 169.403 ↓ 396.0 396 1

Nested Loop Left Join (cost=2,778.53..24,658.06 rows=1 width=2,169) (actual time=21.040..169.403 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.362 158.103 ↓ 2.7 2,077 1

Nested Loop Left Join (cost=2,778.10..24,144.51 rows=766 width=2,088) (actual time=3.847..158.103 rows=2,077 loops=1)

6. 1.113 153.664 ↓ 2.7 2,077 1

Nested Loop Left Join (cost=2,777.67..23,346.48 rows=766 width=1,649) (actual time=3.826..153.664 rows=2,077 loops=1)

7. 0.451 148.397 ↓ 2.7 2,077 1

Nested Loop Left Join (cost=2,777.25..22,950.84 rows=766 width=1,645) (actual time=3.805..148.397 rows=2,077 loops=1)

8. 0.552 143.792 ↓ 2.7 2,077 1

Nested Loop Left Join (cost=2,776.83..22,593.31 rows=766 width=1,645) (actual time=3.775..143.792 rows=2,077 loops=1)

9. 2.047 137.393 ↓ 2.5 1,949 1

Nested Loop Left Join (cost=2,776.41..22,194.25 rows=766 width=1,645) (actual time=3.736..137.393 rows=1,949 loops=1)

10. 0.742 131.448 ↓ 2.5 1,949 1

Nested Loop Left Join (cost=2,775.98..21,396.22 rows=766 width=1,645) (actual time=3.697..131.448 rows=1,949 loops=1)

11. 1.055 124.859 ↓ 2.5 1,949 1

Nested Loop Left Join (cost=2,775.56..21,000.58 rows=766 width=1,641) (actual time=3.661..124.859 rows=1,949 loops=1)

12. 1.783 120.750 ↓ 2.5 1,527 1

Nested Loop Left Join (cost=2,775.14..20,682.67 rows=601 width=1,641) (actual time=3.634..120.750 rows=1,527 loops=1)

13. 0.977 114.386 ↓ 2.5 1,527 1

Hash Join (cost=2,774.72..20,347.59 rows=601 width=551) (actual time=3.589..114.386 rows=1,527 loops=1)

  • Hash Cond: (order_milestones.milestone_id = milestones.id)
14. 1.808 113.392 ↓ 1.1 1,946 1

Nested Loop (cost=2,773.67..20,333.76 rows=1,804 width=551) (actual time=3.552..113.392 rows=1,946 loops=1)

15. 33.135 105.000 ↓ 1.2 1,646 1

Hash Join (cost=2,773.24..19,584.20 rows=1,417 width=519) (actual time=3.150..105.000 rows=1,646 loops=1)

  • Hash Cond: (orders.buyer_vendor_id = buyer_vendors.id)
16. 68.938 68.938 ↑ 1.0 233,428 1

Seq Scan on orders (cost=0.00..15,921.12 rows=233,512 width=519) (actual time=0.176..68.938 rows=233,428 loops=1)

17. 0.381 2.927 ↑ 1.0 1,472 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 68kB
18. 2.352 2.546 ↑ 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.408..2.546 rows=1,472 loops=1)

  • Recheck Cond: (vendor_id = 750)
  • Heap Blocks: exact=1136
19. 0.194 0.194 ↑ 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.194..0.194 rows=1,475 loops=1)

  • Index Cond: (vendor_id = 750)
20. 6.584 6.584 ↑ 2.0 1 1,646

Index Scan using index_order_milestones_on_order_id on order_milestones (cost=0.42..0.51 rows=2 width=32) (actual time=0.004..0.004 rows=1 loops=1,646)

  • Index Cond: (order_id = orders.id)
21. 0.001 0.017 ↑ 1.0 1 1

Hash (cost=1.04..1.04 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.016 0.016 ↑ 1.0 1 1

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

  • Filter: ((name)::text = 'submitted'::text)
  • Rows Removed by Filter: 2
23. 4.581 4.581 ↑ 1.0 1 1,527

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

  • Index Cond: (id = order_milestones.created_by_id)
24. 3.054 3.054 ↑ 2.0 1 1,527

Index Only Scan using index_order_milestones_on_order_id on order_milestones order_milestones_public_orders (cost=0.42..0.51 rows=2 width=4) (actual time=0.001..0.002 rows=1 loops=1,527)

  • Index Cond: (order_id = orders.id)
  • Heap Fetches: 1949
25. 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.003..0.003 rows=1 loops=1,949)

  • Index Cond: (id = orders.buyer_vendor_id)
26. 3.898 3.898 ↑ 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.002..0.002 rows=1 loops=1,949)

  • Index Cond: (id = buyer_vendors_for_sales_reps.account_id)
  • Heap Fetches: 1563
27. 5.847 5.847 ↑ 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.003..0.003 rows=1 loops=1,949)

  • Index Cond: (account_id = accounts_for_sales_reps.id)
28. 4.154 4.154 ↑ 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.002..0.002 rows=1 loops=2,077)

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

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

  • Index Cond: (id = orders.buyer_vendor_id)
30. 2.077 2.077 ↑ 1.0 1 2,077

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,077)

  • Index Cond: (id = buyer_vendors_for_account.account_id)
31. 8.308 8.308 ↑ 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.004..0.004 rows=1 loops=2,077)

  • Index Cond: (order_id = orders.id)
  • Filter: ((party)::text = 'vendor'::text)
  • Rows Removed by Filter: 2