explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0QSL : Optimization for: Optimization for: Optimization for: plan #R6am; plan #PlFG; plan #8Mk

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.049 19.720 ↓ 11.0 11 1

Sort (cost=5,943.93..5,943.93 rows=1 width=1,056) (actual time=19.719..19.720 rows=11 loops=1)

  • Sort Key: (max(i.item_order_status_ts)) DESC
  • Sort Method: quicksort Memory: 36kB
2. 0.037 19.671 ↓ 11.0 11 1

WindowAgg (cost=5,943.81..5,943.92 rows=1 width=1,056) (actual time=19.658..19.671 rows=11 loops=1)

3. 0.037 19.634 ↓ 11.0 11 1

GroupAggregate (cost=5,943.81..5,943.90 rows=1 width=524) (actual time=19.616..19.634 rows=11 loops=1)

  • Group Key: mb.cart_id, c.master_account_id, c.cart_name, ((COALESCE((aot.order_type_override)::character varying, (c.cart_order_type)::character varying))::text), a.company_name, c.account_id, m.tp_account_id, c_1.full_name, ad.address1, ad.city, ad.region_code, ad.postal_code, p.phone_number, ei.email_address, (COALESCE(((vend.vendor_id)::text), (ra.vendor_id)::text)), (COALESCE(((av.company_name)::text), (av_1.company_name)::text)), c.coordination_contact_id, c.coordination_time_to_call, c.coordination_notes, c_2.full_name, p_1.phone_number, ei_1.email_address, c.cart_status, wts.namespace, wts.term, sw.notes
  • Filter: ((count(i.cart_item_id))::integer > 0)
4. 0.135 19.597 ↓ 17.0 17 1

Sort (cost=5,943.81..5,943.81 rows=1 width=541) (actual time=19.596..19.597 rows=17 loops=1)

  • Sort Key: mb.cart_id, c.master_account_id, c.cart_name, ((COALESCE((aot.order_type_override)::character varying, (c.cart_order_type)::character varying))::text), a.company_name, c.account_id, m.tp_account_id, c_1.full_name, ad.address1, ad.city, ad.region_code, ad.postal_code, p.phone_number, ei.email_address, (COALESCE(((vend.vendor_id)::text), (ra.vendor_id)::text)), (COALESCE(((av.company_name)::text), (av_1.company_name)::text)), c.coordination_contact_id, c.coordination_time_to_call, c.coordination_notes, c_2.full_name, p_1.phone_number, ei_1.email_address, c.cart_status, wts.namespace, wts.term, sw.notes
  • Sort Method: quicksort Memory: 33kB
5. 0.044 19.462 ↓ 17.0 17 1

Nested Loop Left Join (cost=117.59..5,943.80 rows=1 width=541) (actual time=1.915..19.462 rows=17 loops=1)

6. 0.011 19.350 ↓ 17.0 17 1

Nested Loop Left Join (cost=117.31..5,935.49 rows=1 width=572) (actual time=1.899..19.350 rows=17 loops=1)

7. 0.020 19.203 ↓ 17.0 17 1

Nested Loop Left Join (cost=116.74..5,918.48 rows=1 width=555) (actual time=1.884..19.203 rows=17 loops=1)

8. 0.024 19.115 ↓ 17.0 17 1

Nested Loop Left Join (cost=116.33..5,910.05 rows=1 width=533) (actual time=1.873..19.115 rows=17 loops=1)

9. 0.023 19.023 ↓ 17.0 17 1

Nested Loop Left Join (cost=115.91..5,901.61 rows=1 width=522) (actual time=1.862..19.023 rows=17 loops=1)

10. 0.010 18.898 ↓ 17.0 17 1

Nested Loop Left Join (cost=115.50..5,893.12 rows=1 width=507) (actual time=1.850..18.898 rows=17 loops=1)

11. 0.012 18.667 ↓ 17.0 17 1

Nested Loop Left Join (cost=114.94..5,876.42 rows=1 width=472) (actual time=1.826..18.667 rows=17 loops=1)

12. 0.051 18.536 ↓ 17.0 17 1

Nested Loop Left Join (cost=114.65..5,866.66 rows=1 width=425) (actual time=1.814..18.536 rows=17 loops=1)

13. 0.035 18.417 ↓ 17.0 17 1

Nested Loop Left Join (cost=114.37..5,858.34 rows=1 width=417) (actual time=1.802..18.417 rows=17 loops=1)

14. 0.024 18.314 ↓ 17.0 17 1

Nested Loop (cost=114.23..5,858.16 rows=1 width=385) (actual time=1.792..18.314 rows=17 loops=1)

  • Join Filter: ((c.cart_id)::bpchar = (sw.cart_id)::bpchar)
15. 0.021 18.188 ↓ 17.0 17 1

Nested Loop (cost=113.95..5,857.84 rows=1 width=401) (actual time=1.777..18.188 rows=17 loops=1)

16. 0.014 18.099 ↓ 17.0 17 1

Nested Loop (cost=113.54..5,857.21 rows=1 width=430) (actual time=1.767..18.099 rows=17 loops=1)

17. 0.027 18.000 ↓ 17.0 17 1

Nested Loop (cost=113.12..5,856.65 rows=1 width=402) (actual time=1.755..18.000 rows=17 loops=1)

18. 0.026 17.905 ↓ 17.0 17 1

Nested Loop (cost=112.71..5,855.81 rows=1 width=346) (actual time=1.743..17.905 rows=17 loops=1)

19. 0.037 17.148 ↓ 17.0 17 1

Nested Loop Left Join (cost=112.29..5,848.24 rows=1 width=331) (actual time=1.720..17.148 rows=17 loops=1)

  • Filter: (COALESCE((v.vendor_id)::bpchar, (i.vendor_id)::bpchar) = (i.vendor_id)::bpchar)
20. 0.024 16.992 ↓ 17.0 17 1

Nested Loop (cost=112.29..5,846.79 rows=1 width=331) (actual time=1.700..16.992 rows=17 loops=1)

  • Join Filter: ((c.cart_id)::bpchar = (mb.cart_id)::bpchar)
21. 0.017 16.900 ↓ 17.0 17 1

Nested Loop (cost=112.01..5,846.41 rows=1 width=304) (actual time=1.687..16.900 rows=17 loops=1)

22. 1.537 16.784 ↓ 11.0 11 1

Nested Loop (cost=111.60..5,834.21 rows=1 width=245) (actual time=1.673..16.784 rows=11 loops=1)

23. 5.403 5.943 ↓ 2,326.0 2,326 1

Bitmap Heap Scan on sc_carts c (cost=111.31..5,825.88 rows=1 width=210) (actual time=0.819..5.943 rows=2,326 loops=1)

  • Recheck Cond: ((cart_type)::text = ANY ('{"MEDIA BUY CAMPAIGN","DIGITAL CAMPAIGN"}'::text[]))
  • Filter: (((master_account_id)::bpchar = COALESCE((master_account_id)::bpchar)) AND ((cart_type)::text = COALESCE((cart_type)::text)) AND ((cart_order_type)::text = COALESCE((cart_order_type)::text)))
  • Rows Removed by Filter: 173
  • Heap Blocks: exact=1673
24. 0.540 0.540 ↓ 1.0 2,506 1

Bitmap Index Scan on ix_sc_carts_lookup_cart_type (cost=0.00..111.31 rows=2,499 width=0) (actual time=0.539..0.540 rows=2,506 loops=1)

  • Index Cond: ((cart_type)::text = ANY ('{"MEDIA BUY CAMPAIGN","DIGITAL CAMPAIGN"}'::text[]))
25. 9.304 9.304 ↓ 0.0 0 2,326

Index Scan using ix_acct_accounts_account_id on acct_accounts a (cost=0.29..8.32 rows=1 width=35) (actual time=0.004..0.004 rows=0 loops=2,326)

  • Index Cond: ((account_id)::bpchar = (c.account_id)::bpchar)
  • Filter: (((c.cart_name)::text ~~* '%sudo%'::text) OR ((c.cart_id)::text ~~* '%sudo%'::text) OR ((account_id)::text ~~* '%sudo%'::text) OR ((company_name)::text ~~* '%sudo%'::text))
  • Rows Removed by Filter: 1
26. 0.099 0.099 ↑ 1.0 2 11

Index Scan using ix_sc_items_cart_id on sc_items i (cost=0.41..12.18 rows=2 width=59) (actual time=0.007..0.009 rows=2 loops=11)

  • Index Cond: ((cart_id)::bpchar = (c.cart_id)::bpchar)
27. 0.068 0.068 ↑ 1.0 1 17

Index Scan using pk_sc_campaigns on sc_campaigns mb (cost=0.28..0.37 rows=1 width=27) (actual time=0.004..0.004 rows=1 loops=17)

  • Index Cond: ((cart_id)::bpchar = (i.cart_id)::bpchar)
28. 0.119 0.119 ↓ 0.0 0 17

Seq Scan on vend_vendors v (cost=0.00..1.44 rows=1 width=17) (actual time=0.007..0.007 rows=0 loops=17)

  • Filter: ((vendor_account_id)::bpchar = 'AC01010000000000'::bpchar)
  • Rows Removed by Filter: 35
29. 0.731 0.731 ↑ 1.0 1 17

Index Scan using ix_acct_contacts_account_id on acct_contacts c_1 (cost=0.41..7.55 rows=1 width=49) (actual time=0.006..0.043 rows=1 loops=17)

  • Index Cond: ((account_id)::bpchar = (a.account_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND ((contact_type)::text = 'Primary'::text))
  • Rows Removed by Filter: 42
30. 0.068 0.068 ↑ 1.0 1 17

Index Scan using ix_acct_addresses_contact_id on acct_addresses ad (cost=0.41..0.84 rows=1 width=56) (actual time=0.004..0.004 rows=1 loops=17)

  • Index Cond: ((contact_id)::bpchar = (c_1.contact_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND ((address_type)::text = 'Primary'::text))
31. 0.085 0.085 ↑ 1.0 1 17

Index Scan using pk_acct_phones on acct_phones p (cost=0.41..0.57 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=17)

  • Index Cond: (((contact_id)::bpchar = (ad.contact_id)::bpchar) AND ((phone_type)::text = 'Primary'::text))
  • Filter: (is_active IS TRUE)
32. 0.068 0.068 ↑ 1.0 1 17

Index Scan using ix_acct_e_info_contact_id on acct_e_info ei (cost=0.41..0.62 rows=1 width=39) (actual time=0.004..0.004 rows=1 loops=17)

  • Index Cond: ((contact_id)::bpchar = (ad.contact_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND ((e_info_type)::text = 'Primary'::text))
33. 0.102 0.102 ↑ 1.0 1 17

Index Scan using pk_sc_workflows on sc_workflows sw (cost=0.28..0.31 rows=1 width=35) (actual time=0.006..0.006 rows=1 loops=17)

  • Index Cond: (((cart_id)::bpchar = (i.cart_id)::bpchar) AND ((wf_type)::text = 'Campaign'::text))
  • Filter: ((wf_status)::text = ANY ('{Submitted,Running,Paused,Completed,Cancelled}'::text[]))
34. 0.068 0.068 ↑ 1.0 1 17

Index Scan using pk_wf_type_statuses on wf_type_statuses wts (cost=0.14..0.17 rows=1 width=54) (actual time=0.004..0.004 rows=1 loops=17)

  • Index Cond: (((wf_type)::text = 'Campaign'::text) AND ((wf_status)::text = (sw.wf_status)::text))
35. 0.068 0.068 ↑ 1.0 1 17

Index Scan using ix_rel_masters_accounts_account_id on rel_masters_accounts m (cost=0.29..8.30 rows=1 width=42) (actual time=0.004..0.004 rows=1 loops=17)

  • Index Cond: ((account_id)::bpchar = (c.account_id)::bpchar)
  • Filter: ((master_account_id)::bpchar = (c.master_account_id)::bpchar)
36. 0.017 0.119 ↓ 0.0 0 17

Limit (cost=0.29..9.74 rows=1 width=96) (actual time=0.007..0.007 rows=0 loops=17)

37. 0.017 0.102 ↓ 0.0 0 17

Nested Loop (cost=0.29..9.74 rows=1 width=96) (actual time=0.006..0.006 rows=0 loops=17)

38. 0.085 0.085 ↓ 0.0 0 17

Seq Scan on vend_vendors vend (cost=0.00..1.44 rows=1 width=34) (actual time=0.005..0.005 rows=0 loops=17)

  • Filter: ((vendor_id)::bpchar = (v.vendor_id)::bpchar)
  • Rows Removed by Filter: 35
39. 0.000 0.000 ↓ 0.0 0

Index Scan using ix_acct_accounts_account_id on acct_accounts av (cost=0.29..8.30 rows=1 width=35) (never executed)

  • Index Cond: ((account_id)::bpchar = (vend.vendor_account_id)::bpchar)
40. 0.017 0.221 ↑ 1.0 1 17

Limit (cost=0.56..16.68 rows=1 width=67) (actual time=0.012..0.013 rows=1 loops=17)

41. 0.034 0.204 ↑ 1.0 1 17

Nested Loop (cost=0.56..16.68 rows=1 width=67) (actual time=0.012..0.012 rows=1 loops=17)

42. 0.119 0.119 ↑ 1.0 1 17

Index Scan using pk_rel_acct_vendor_services on rel_acct_vendor_services ra (cost=0.28..8.37 rows=1 width=34) (actual time=0.007..0.007 rows=1 loops=17)

  • Index Cond: (((account_id)::bpchar = (c.master_account_id)::bpchar) AND ((workflow)::text = 'MEDIA_BUY'::text))
  • Filter: (is_active IS TRUE)
43. 0.051 0.051 ↑ 1.0 1 17

Index Scan using ix_acct_accounts_account_id on acct_accounts av_1 (cost=0.29..8.30 rows=1 width=35) (actual time=0.003..0.003 rows=1 loops=17)

  • Index Cond: ((account_id)::bpchar = (c.master_account_id)::bpchar)
44. 0.102 0.102 ↑ 1.0 1 17

Index Scan using ix_acct_contacts_contact_id on acct_contacts c_2 (cost=0.41..8.43 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=17)

  • Index Cond: ((contact_id)::bpchar = (c.coordination_contact_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND ((contact_type)::text = 'Coordination'::text))
45. 0.068 0.068 ↑ 1.0 1 17

Index Scan using pk_acct_phones on acct_phones p_1 (cost=0.41..8.43 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=17)

  • Index Cond: (((contact_id)::bpchar = (c.coordination_contact_id)::bpchar) AND ((phone_type)::text = 'Primary'::text))
  • Filter: (is_active IS TRUE)
46. 0.068 0.068 ↑ 1.0 1 17

Index Scan using ix_acct_e_info_contact_id on acct_e_info ei_1 (cost=0.41..8.43 rows=1 width=39) (actual time=0.004..0.004 rows=1 loops=17)

  • Index Cond: ((contact_id)::bpchar = (c.coordination_contact_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND ((e_info_type)::text = 'Primary'::text))
47. 0.017 0.136 ↑ 1.0 1 17

Limit (cost=0.57..16.99 rows=1 width=17) (actual time=0.007..0.008 rows=1 loops=17)

48. 0.017 0.119 ↑ 1.0 1 17

Nested Loop (cost=0.57..16.99 rows=1 width=17) (actual time=0.007..0.007 rows=1 loops=17)

49. 0.051 0.051 ↑ 1.0 1 17

Index Scan using ix_rel_masters_accounts_account_id on rel_masters_accounts d (cost=0.29..8.30 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=17)

  • Index Cond: ((account_id)::bpchar = (c.account_id)::bpchar)
50. 0.051 0.051 ↑ 1.0 1 17

Index Scan using ix_acct_accounts_account_id on acct_accounts xa (cost=0.29..8.30 rows=1 width=17) (actual time=0.003..0.003 rows=1 loops=17)

  • Index Cond: ((account_id)::bpchar = (d.master_account_id)::bpchar)
  • Filter: (is_primary_master AND (is_active IS TRUE))
51. 0.068 0.068 ↑ 1.0 1 17

Index Scan using ix_acct_order_types_account_id_order_type on acct_order_types aot (cost=0.28..8.30 rows=1 width=43) (actual time=0.004..0.004 rows=1 loops=17)

  • Index Cond: (((account_id)::bpchar = COALESCE((d.master_account_id)::bpchar, (c.master_account_id)::bpchar)) AND ((order_type)::text = (c.cart_order_type)::text))
Planning time : 81.409 ms
Execution time : 20.250 ms