explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9ie7

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.054 30.458 ↓ 11.0 11 1

Sort (cost=6,063.37..6,063.37 rows=1 width=1,099) (actual time=30.457..30.458 rows=11 loops=1)

  • Sort Key: (max(GREATEST(i.item_order_status_ts, COALESCE((max(r.created_ts)), '-infinity'::timestamp with time zone)))) DESC
  • Sort Method: quicksort Memory: 36kB
2.          

CTE carts_cte

3. 7.378 8.218 ↓ 2,326.0 2,326 1

Bitmap Heap Scan on sc_carts c_2 (cost=111.31..5,825.88 rows=1 width=210) (actual time=1.242..8.218 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
4. 0.840 0.840 ↓ 1.0 2,503 1

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

  • Index Cond: ((cart_type)::text = ANY ('{"MEDIA BUY CAMPAIGN","DIGITAL CAMPAIGN"}'::text[]))
5. 0.053 30.404 ↓ 11.0 11 1

WindowAgg (cost=237.25..237.47 rows=1 width=1,099) (actual time=30.388..30.404 rows=11 loops=1)

6. 0.061 30.351 ↓ 11.0 11 1

GroupAggregate (cost=237.25..237.46 rows=1 width=610) (actual time=30.323..30.351 rows=11 loops=1)

  • Group Key: mb.cart_id, ca.master_account_id, ca.cart_name, ((COALESCE((aot.order_type_override)::character varying, (ca.cart_order_type)::character varying))::text), a.company_name, ca.account_id, m.tp_account_id, (max((r.campaign_request_id)::bpchar)), c.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)), ca.coordination_contact_id, ca.coordination_time_to_call, ca.coordination_notes, c_1.full_name, p_1.phone_number, ei_1.email_address, ca.cart_status, wts.namespace, wts.term, sw.notes
  • Filter: ((count(i.cart_item_id))::integer > 0)
7. 0.142 30.290 ↓ 8.5 17 1

Sort (cost=237.25..237.26 rows=2 width=635) (actual time=30.289..30.290 rows=17 loops=1)

  • Sort Key: mb.cart_id, ca.master_account_id, ca.cart_name, ((COALESCE((aot.order_type_override)::character varying, (ca.cart_order_type)::character varying))::text), a.company_name, ca.account_id, m.tp_account_id, (max((r.campaign_request_id)::bpchar)), c.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)), ca.coordination_contact_id, ca.coordination_time_to_call, ca.coordination_notes, c_1.full_name, p_1.phone_number, ei_1.email_address, ca.cart_status, wts.namespace, wts.term, sw.notes
  • Sort Method: quicksort Memory: 33kB
8. 0.053 30.148 ↓ 8.5 17 1

Nested Loop Left Join (cost=59.08..237.24 rows=2 width=635) (actual time=28.434..30.148 rows=17 loops=1)

9. 0.024 30.010 ↓ 8.5 17 1

Nested Loop Left Join (cost=58.80..220.63 rows=2 width=687) (actual time=28.411..30.010 rows=17 loops=1)

10. 0.032 29.816 ↓ 8.5 17 1

Nested Loop Left Join (cost=58.23..186.62 rows=2 width=670) (actual time=28.388..29.816 rows=17 loops=1)

11. 0.027 29.699 ↓ 8.5 17 1

Nested Loop Left Join (cost=57.81..169.75 rows=2 width=648) (actual time=28.371..29.699 rows=17 loops=1)

12. 0.029 29.570 ↓ 8.5 17 1

Nested Loop Left Join (cost=57.40..152.88 rows=2 width=637) (actual time=28.353..29.570 rows=17 loops=1)

13. 0.026 29.422 ↓ 8.5 17 1

Nested Loop Left Join (cost=56.98..135.95 rows=2 width=622) (actual time=28.330..29.422 rows=17 loops=1)

14. 0.016 29.124 ↓ 8.5 17 1

Nested Loop Left Join (cost=56.42..102.55 rows=2 width=587) (actual time=28.291..29.124 rows=17 loops=1)

15. 0.039 28.938 ↓ 8.5 17 1

Nested Loop Left Join (cost=56.14..83.03 rows=2 width=540) (actual time=28.269..28.938 rows=17 loops=1)

16. 0.031 28.627 ↓ 17.0 17 1

Nested Loop Left Join (cost=51.84..70.73 rows=1 width=545) (actual time=28.206..28.627 rows=17 loops=1)

17. 0.039 28.511 ↓ 17.0 17 1

Nested Loop Left Join (cost=51.55..62.42 rows=1 width=537) (actual time=28.179..28.511 rows=17 loops=1)

18. 0.053 28.404 ↓ 17.0 17 1

Nested Loop Left Join (cost=51.41..62.23 rows=1 width=505) (actual time=28.164..28.404 rows=17 loops=1)

  • Filter: CASE WHEN ((sw.wf_status)::text = ANY ('{Submitted,Running,Paused,Completed,Cancelled}'::text[])) THEN true ELSE false END
19. 0.038 28.266 ↓ 17.0 17 1

Nested Loop (cost=51.14..53.92 rows=1 width=519) (actual time=28.140..28.266 rows=17 loops=1)

20. 0.060 28.143 ↓ 17.0 17 1

Hash Right Join (cost=50.72..53.30 rows=1 width=548) (actual time=28.120..28.143 rows=17 loops=1)

  • Hash Cond: ((r.cart_id)::bpchar = (mb.cart_id)::bpchar)
21. 0.145 0.250 ↑ 1.0 103 1

HashAggregate (cost=19.36..20.44 rows=108 width=65) (actual time=0.216..0.250 rows=103 loops=1)

  • Group Key: r.cart_id
22. 0.105 0.105 ↑ 1.0 141 1

Seq Scan on sc_campaign_requests r (cost=0.00..18.30 rows=141 width=42) (actual time=0.013..0.105 rows=141 loops=1)

  • Filter: (campaign_request_status <> 'CLOSED'::text)
  • Rows Removed by Filter: 43
23. 0.046 27.833 ↓ 17.0 17 1

Hash (cost=31.35..31.35 rows=1 width=508) (actual time=27.833..27.833 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
24. 0.022 27.787 ↓ 17.0 17 1

Nested Loop (cost=2.23..31.35 rows=1 width=508) (actual time=2.617..27.787 rows=17 loops=1)

25. 0.025 27.663 ↓ 17.0 17 1

Nested Loop (cost=1.81..30.79 rows=1 width=480) (actual time=2.596..27.663 rows=17 loops=1)

26. 0.025 27.536 ↓ 17.0 17 1

Nested Loop (cost=1.40..29.94 rows=1 width=424) (actual time=2.579..27.536 rows=17 loops=1)

27. 0.046 26.593 ↓ 17.0 17 1

Nested Loop Left Join (cost=0.98..22.38 rows=1 width=409) (actual time=2.555..26.593 rows=17 loops=1)

  • Filter: (COALESCE((v.vendor_id)::bpchar, (i.vendor_id)::bpchar) = (i.vendor_id)::bpchar)
28. 0.026 26.377 ↓ 17.0 17 1

Nested Loop (cost=0.98..20.93 rows=1 width=409) (actual time=2.526..26.377 rows=17 loops=1)

  • Join Filter: ((ca.cart_id)::bpchar = (mb.cart_id)::bpchar)
29. 0.020 26.266 ↓ 17.0 17 1

Nested Loop (cost=0.70..20.54 rows=1 width=382) (actual time=2.509..26.266 rows=17 loops=1)

30. 1.307 26.136 ↓ 11.0 11 1

Nested Loop (cost=0.29..8.35 rows=1 width=323) (actual time=2.489..26.136 rows=11 loops=1)

31. 10.873 10.873 ↓ 2,326.0 2,326 1

CTE Scan on carts_cte ca (cost=0.00..0.02 rows=1 width=288) (actual time=1.249..10.873 rows=2,326 loops=1)

32. 13.956 13.956 ↓ 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.006..0.006 rows=0 loops=2,326)

  • Index Cond: ((account_id)::bpchar = (ca.account_id)::bpchar)
  • Filter: (((ca.cart_name)::text ~~* '%sudo%'::text) OR ((ca.cart_id)::text ~~* '%sudo%'::text) OR ((account_id)::text ~~* '%sudo%'::text) OR ((company_name)::text ~~* '%sudo%'::text))
  • Rows Removed by Filter: 1
33. 0.110 0.110 ↑ 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.008..0.010 rows=2 loops=11)

  • Index Cond: ((cart_id)::bpchar = (ca.cart_id)::bpchar)
34. 0.085 0.085 ↑ 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.005..0.005 rows=1 loops=17)

  • Index Cond: ((cart_id)::bpchar = (i.cart_id)::bpchar)
35. 0.170 0.170 ↓ 0.0 0 17

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

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

Index Scan using ix_acct_contacts_account_id on acct_contacts c (cost=0.41..7.55 rows=1 width=49) (actual time=0.008..0.054 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
37. 0.102 0.102 ↑ 1.0 1 17

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

  • Index Cond: ((contact_id)::bpchar = (c.contact_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND ((address_type)::text = 'Primary'::text))
38. 0.102 0.102 ↑ 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.006..0.006 rows=1 loops=17)

  • Index Cond: (((contact_id)::bpchar = (ad.contact_id)::bpchar) AND ((phone_type)::text = 'Primary'::text))
  • Filter: (is_active IS TRUE)
39. 0.085 0.085 ↑ 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.005..0.005 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))
40. 0.085 0.085 ↑ 1.0 1 17

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

  • Index Cond: (((cart_id)::bpchar = (ca.cart_id)::bpchar) AND ((wf_type)::text = 'Campaign'::text))
41. 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))
42. 0.085 0.085 ↑ 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.005..0.005 rows=1 loops=17)

  • Index Cond: ((account_id)::bpchar = (ca.account_id)::bpchar)
  • Filter: ((master_account_id)::bpchar = (ca.master_account_id)::bpchar)
43. 0.116 0.272 ↑ 2.0 1 17

GroupAggregate (cost=4.30..12.25 rows=2 width=33) (actual time=0.015..0.016 rows=1 loops=17)

  • Group Key: sw_1.cart_id
44. 0.068 0.153 ↑ 1.0 2 17

Bitmap Heap Scan on sc_item_workflows sw_1 (cost=4.30..11.41 rows=2 width=43) (actual time=0.008..0.009 rows=2 loops=17)

  • Recheck Cond: ((cart_id)::bpchar = (i.cart_id)::bpchar)
  • Heap Blocks: exact=20
45. 0.085 0.085 ↑ 1.0 2 17

Bitmap Index Scan on pk_sc_item_workflows (cost=0.00..4.29 rows=2 width=0) (actual time=0.005..0.005 rows=2 loops=17)

  • Index Cond: ((cart_id)::bpchar = (i.cart_id)::bpchar)
46.          

SubPlan (forGroupAggregate)

47. 0.002 0.003 ↓ 0.0 0 1

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.003..0.003 rows=0 loops=1)

48. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

49. 0.017 0.170 ↓ 0.0 0 17

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

50. 0.017 0.153 ↓ 0.0 0 17

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

51. 0.136 0.136 ↓ 0.0 0 17

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

  • Filter: ((vendor_id)::bpchar = (v.vendor_id)::bpchar)
  • Rows Removed by Filter: 35
52. 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)
53. 0.017 0.272 ↑ 1.0 1 17

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

54. 0.051 0.255 ↑ 1.0 1 17

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

55. 0.136 0.136 ↑ 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.008..0.008 rows=1 loops=17)

  • Index Cond: (((account_id)::bpchar = (ca.master_account_id)::bpchar) AND ((workflow)::text = 'MEDIA_BUY'::text))
  • Filter: (is_active IS TRUE)
56. 0.068 0.068 ↑ 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.004..0.004 rows=1 loops=17)

  • Index Cond: ((account_id)::bpchar = (ca.master_account_id)::bpchar)
57. 0.119 0.119 ↑ 1.0 1 17

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

  • Index Cond: ((contact_id)::bpchar = (ca.coordination_contact_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND ((contact_type)::text = 'Coordination'::text))
58. 0.102 0.102 ↑ 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.006..0.006 rows=1 loops=17)

  • Index Cond: (((contact_id)::bpchar = (ca.coordination_contact_id)::bpchar) AND ((phone_type)::text = 'Primary'::text))
  • Filter: (is_active IS TRUE)
59. 0.085 0.085 ↑ 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.005..0.005 rows=1 loops=17)

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

Limit (cost=0.57..16.99 rows=1 width=17) (actual time=0.009..0.010 rows=1 loops=17)

61. 0.034 0.153 ↑ 1.0 1 17

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

62. 0.068 0.068 ↑ 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.004 rows=1 loops=17)

  • Index Cond: ((account_id)::bpchar = (ca.account_id)::bpchar)
63. 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))
64. 0.085 0.085 ↑ 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.005..0.005 rows=1 loops=17)

  • Index Cond: (((account_id)::bpchar = COALESCE((d.master_account_id)::bpchar, (ca.master_account_id)::bpchar)) AND ((order_type)::text = (ca.cart_order_type)::text))
Planning time : 48.795 ms
Execution time : 31.626 ms