explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QTc : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #R6am; plan #PlFG; plan #8Mk; plan #0QSL; plan #NG4; plan #oqN; plan #o3hn

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.037 754.910 ↓ 11.0 11 1

Sort (cost=26,587.67..26,587.68 rows=1 width=1,056) (actual time=754.909..754.910 rows=11 loops=1)

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

WindowAgg (cost=26,587.55..26,587.66 rows=1 width=1,056) (actual time=754.863..754.873 rows=11 loops=1)

3. 0.039 754.838 ↓ 11.0 11 1

GroupAggregate (cost=26,587.55..26,587.64 rows=1 width=524) (actual time=754.823..754.838 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.206 754.799 ↓ 17.0 17 1

Sort (cost=26,587.55..26,587.56 rows=1 width=541) (actual time=754.798..754.799 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.090 754.593 ↓ 17.0 17 1

Nested Loop Left Join (cost=1,055.59..26,587.54 rows=1 width=541) (actual time=51.036..754.593 rows=17 loops=1)

6. 0.037 754.384 ↓ 17.0 17 1

Nested Loop Left Join (cost=1,055.31..26,579.23 rows=1 width=572) (actual time=51.017..754.384 rows=17 loops=1)

7. 0.050 754.126 ↓ 17.0 17 1

Nested Loop Left Join (cost=1,054.74..26,562.23 rows=1 width=555) (actual time=50.997..754.126 rows=17 loops=1)

8. 0.050 753.957 ↓ 17.0 17 1

Nested Loop Left Join (cost=1,054.33..26,559.35 rows=1 width=533) (actual time=50.982..753.957 rows=17 loops=1)

9. 0.056 753.788 ↓ 17.0 17 1

Nested Loop Left Join (cost=1,053.91..26,557.07 rows=1 width=522) (actual time=50.965..753.788 rows=17 loops=1)

10. 0.039 753.579 ↓ 17.0 17 1

Nested Loop Left Join (cost=1,053.50..26,551.91 rows=1 width=507) (actual time=50.946..753.579 rows=17 loops=1)

11. 0.046 753.183 ↓ 17.0 17 1

Nested Loop Left Join (cost=1,052.94..26,535.21 rows=1 width=472) (actual time=50.913..753.183 rows=17 loops=1)

12. 0.069 752.746 ↓ 17.0 17 1

Nested Loop Left Join (cost=1,052.65..26,525.45 rows=1 width=425) (actual time=50.875..752.746 rows=17 loops=1)

13. 0.086 752.558 ↓ 17.0 17 1

Nested Loop Left Join (cost=1,052.37..26,522.50 rows=1 width=417) (actual time=50.857..752.558 rows=17 loops=1)

14. 3.098 752.336 ↓ 17.0 17 1

Nested Loop (cost=1,052.23..26,522.32 rows=1 width=385) (actual time=50.825..752.336 rows=17 loops=1)

  • Join Filter: ((i.cart_id)::bpchar = (c.cart_id)::bpchar)
  • Rows Removed by Join Filter: 25651
15. 0.049 20.502 ↓ 12.0 12 1

Nested Loop (cost=972.26..6,826.92 rows=1 width=342) (actual time=9.565..20.502 rows=12 loops=1)

16. 0.059 20.333 ↓ 6.0 12 1

Nested Loop (cost=971.85..6,825.68 rows=2 width=371) (actual time=9.555..20.333 rows=12 loops=1)

  • Join Filter: ((c_1.contact_id)::bpchar = (ad.contact_id)::bpchar)
17. 0.050 20.154 ↓ 4.0 12 1

Nested Loop (cost=971.43..6,823.93 rows=3 width=315) (actual time=9.541..20.154 rows=12 loops=1)

18. 0.042 19.960 ↓ 2.4 12 1

Nested Loop (cost=971.02..6,819.79 rows=5 width=287) (actual time=9.526..19.960 rows=12 loops=1)

19. 0.091 18.874 ↓ 2.4 12 1

Nested Loop (cost=970.60..6,781.98 rows=5 width=272) (actual time=9.508..18.874 rows=12 loops=1)

20. 5.167 18.627 ↑ 4.8 12 1

Hash Join (cost=970.32..6,676.44 rows=57 width=245) (actual time=9.489..18.627 rows=12 loops=1)

  • Hash Cond: ((c.account_id)::bpchar = (a.account_id)::bpchar)
  • Join Filter: (((c.cart_name)::text ~~* '%sudo%'::text) OR ((c.cart_id)::text ~~* '%sudo%'::text) OR ((a.account_id)::text ~~* '%sudo%'::text) OR ((a.company_name)::text ~~* '%sudo%'::text))
  • Rows Removed by Join Filter: 2488
21. 4.831 5.378 ↑ 1.0 2,500 1

Bitmap Heap Scan on sc_carts c (cost=111.97..5,811.52 rows=2,502 width=210) (actual time=0.810..5.378 rows=2,500 loops=1)

  • Recheck Cond: ((cart_type)::text = ANY ('{"MEDIA BUY CAMPAIGN","DIGITAL CAMPAIGN"}'::text[]))
  • Heap Blocks: exact=1674
22. 0.547 0.547 ↑ 1.0 2,500 1

Bitmap Index Scan on ix_sc_carts_lookup_cart_type (cost=0.00..111.34 rows=2,502 width=0) (actual time=0.547..0.547 rows=2,500 loops=1)

  • Index Cond: ((cart_type)::text = ANY ('{"MEDIA BUY CAMPAIGN","DIGITAL CAMPAIGN"}'::text[]))
23. 3.504 8.082 ↑ 1.0 12,327 1

Hash (cost=704.27..704.27 rows=12,327 width=35) (actual time=8.082..8.082 rows=12,327 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 946kB
24. 4.578 4.578 ↑ 1.0 12,327 1

Seq Scan on acct_accounts a (cost=0.00..704.27 rows=12,327 width=35) (actual time=0.009..4.578 rows=12,327 loops=1)

25. 0.156 0.156 ↑ 1.0 1 12

Index Scan using pk_sc_campaigns on sc_campaigns mb (cost=0.28..1.85 rows=1 width=27) (actual time=0.013..0.013 rows=1 loops=12)

  • Index Cond: ((cart_id)::bpchar = (c.cart_id)::bpchar)
26. 1.044 1.044 ↑ 1.0 1 12

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.014..0.087 rows=1 loops=12)

  • Index Cond: ((account_id)::bpchar = (a.account_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND ((contact_type)::text = 'Primary'::text))
  • Rows Removed by Filter: 40
27. 0.144 0.144 ↑ 1.0 1 12

Index Scan using pk_acct_phones on acct_phones p (cost=0.41..0.83 rows=1 width=28) (actual time=0.012..0.012 rows=1 loops=12)

  • Index Cond: (((contact_id)::bpchar = (c_1.contact_id)::bpchar) AND ((phone_type)::text = 'Primary'::text))
  • Filter: (is_active IS TRUE)
28. 0.120 0.120 ↑ 1.0 1 12

Index Scan using ix_acct_addresses_contact_id on acct_addresses ad (cost=0.41..0.57 rows=1 width=56) (actual time=0.010..0.010 rows=1 loops=12)

  • Index Cond: ((contact_id)::bpchar = (p.contact_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND ((address_type)::text = 'Primary'::text))
29. 0.120 0.120 ↑ 1.0 1 12

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.010..0.010 rows=1 loops=12)

  • Index Cond: ((contact_id)::bpchar = (ad.contact_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND ((e_info_type)::text = 'Primary'::text))
30. 87.771 728.736 ↑ 1.2 2,139 12

Hash Join (cost=79.96..19,664.03 rows=2,509 width=94) (actual time=0.077..60.728 rows=2,139 loops=12)

  • Hash Cond: ((i.cart_id)::bpchar = (sw.cart_id)::bpchar)
31. 131.395 640.140 ↑ 1.0 53,203 12

Hash Left Join (cost=1.79..19,446.18 rows=53,203 width=59) (actual time=0.007..53.345 rows=53,203 loops=12)

  • Hash Cond: ((i.vendor_id)::bpchar = (v.vendor_id)::bpchar)
32. 508.716 508.716 ↑ 1.0 53,203 12

Seq Scan on sc_items i (cost=0.00..19,296.03 rows=53,203 width=59) (actual time=0.003..42.393 rows=53,203 loops=12)

33. 0.017 0.029 ↑ 1.0 35 1

Hash (cost=1.35..1.35 rows=35 width=17) (actual time=0.029..0.029 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
34. 0.012 0.012 ↑ 1.0 35 1

Seq Scan on vend_vendors v (cost=0.00..1.35 rows=35 width=17) (actual time=0.004..0.012 rows=35 loops=1)

35. 0.258 0.825 ↓ 1.1 1,216 1

Hash (cost=64.73..64.73 rows=1,076 width=35) (actual time=0.825..0.825 rows=1,216 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 86kB
36. 0.567 0.567 ↓ 1.1 1,216 1

Seq Scan on sc_workflows sw (cost=0.00..64.73 rows=1,076 width=35) (actual time=0.011..0.567 rows=1,216 loops=1)

  • Filter: (((wf_type)::text = 'Campaign'::text) AND ((wf_status)::text = ANY ('{Submitted,Running,Paused,Completed,Cancelled}'::text[])))
  • Rows Removed by Filter: 157
37. 0.136 0.136 ↑ 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.008..0.008 rows=1 loops=17)

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

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

  • Index Cond: ((account_id)::bpchar = (c.account_id)::bpchar)
  • Filter: ((master_account_id)::bpchar = (c.master_account_id)::bpchar)
39. 0.051 0.391 ↑ 1.0 1 17

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

40. 0.153 0.340 ↑ 1.0 1 17

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

41. 0.051 0.051 ↑ 1.0 1 17

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

  • Filter: ((vendor_id)::bpchar = (v.vendor_id)::bpchar)
  • Rows Removed by Filter: 2
42. 0.136 0.136 ↑ 1.0 1 17

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

  • Index Cond: ((account_id)::bpchar = (vend.vendor_account_id)::bpchar)
43. 0.034 0.357 ↑ 1.0 1 17

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

44. 0.068 0.323 ↑ 1.0 1 17

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

45. 0.170 0.170 ↑ 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.010..0.010 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)
46. 0.085 0.085 ↑ 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.005..0.005 rows=1 loops=17)

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

Index Scan using ix_acct_contacts_contact_id on acct_contacts c_2 (cost=0.41..5.13 rows=1 width=32) (actual time=0.009..0.009 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))
48. 0.119 0.119 ↑ 1.0 1 17

Index Scan using pk_acct_phones on acct_phones p_1 (cost=0.41..2.28 rows=1 width=28) (actual time=0.007..0.007 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)
49. 0.119 0.119 ↑ 1.0 1 17

Index Scan using ix_acct_e_info_contact_id on acct_e_info ei_1 (cost=0.41..2.88 rows=1 width=39) (actual time=0.007..0.007 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))
50. 0.051 0.221 ↑ 1.0 1 17

Limit (cost=0.57..16.99 rows=1 width=17) (actual time=0.011..0.013 rows=1 loops=17)

51. 0.068 0.170 ↑ 1.0 1 17

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

52. 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)
53. 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))
54. 0.119 0.119 ↑ 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.007..0.007 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))