explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PlFG : Optimization for: plan #R6am

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.040 21.557 ↓ 11.0 11 1

Sort (cost=6,990.90..6,990.90 rows=1 width=1,056) (actual time=21.557..21.557 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. 0.038 21.517 ↓ 11.0 11 1

WindowAgg (cost=6,990.67..6,990.89 rows=1 width=1,056) (actual time=21.504..21.517 rows=11 loops=1)

3. 0.045 21.479 ↓ 11.0 11 1

GroupAggregate (cost=6,990.67..6,990.87 rows=1 width=536) (actual time=21.458..21.479 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.129 21.434 ↓ 8.5 17 1

Sort (cost=6,990.67..6,990.68 rows=2 width=561) (actual time=21.433..21.434 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.043 21.305 ↓ 8.5 17 1

Nested Loop Left Join (cost=999.59..6,990.66 rows=2 width=561) (actual time=9.063..21.305 rows=17 loops=1)

6. 0.011 21.194 ↓ 8.5 17 1

Nested Loop Left Join (cost=999.31..6,974.05 rows=2 width=592) (actual time=9.047..21.194 rows=17 loops=1)

7. 0.025 21.030 ↓ 8.5 17 1

Nested Loop Left Join (cost=998.74..6,940.04 rows=2 width=575) (actual time=9.019..21.030 rows=17 loops=1)

8. 0.019 20.937 ↓ 8.5 17 1

Nested Loop Left Join (cost=998.33..6,934.28 rows=2 width=553) (actual time=9.008..20.937 rows=17 loops=1)

9. 0.028 20.833 ↓ 8.5 17 1

Nested Loop Left Join (cost=997.91..6,929.72 rows=2 width=542) (actual time=8.993..20.833 rows=17 loops=1)

10. 0.022 20.720 ↓ 8.5 17 1

Nested Loop Left Join (cost=997.50..6,919.43 rows=2 width=527) (actual time=8.980..20.720 rows=17 loops=1)

11. 0.015 20.443 ↓ 8.5 17 1

Nested Loop Left Join (cost=996.94..6,886.03 rows=2 width=492) (actual time=8.950..20.443 rows=17 loops=1)

12. 0.029 20.292 ↓ 8.5 17 1

Nested Loop Left Join (cost=996.65..6,866.50 rows=2 width=445) (actual time=8.937..20.292 rows=17 loops=1)

13. 0.032 20.042 ↓ 17.0 17 1

Nested Loop Left Join (cost=992.35..6,854.21 rows=1 width=450) (actual time=8.900..20.042 rows=17 loops=1)

14. 0.202 19.942 ↓ 17.0 17 1

Nested Loop Left Join (cost=992.07..6,851.27 rows=1 width=442) (actual time=8.887..19.942 rows=17 loops=1)

  • Join Filter: ((r.cart_id)::bpchar = (mb.cart_id)::bpchar)
  • Rows Removed by Join Filter: 1751
15. 0.026 19.298 ↓ 17.0 17 1

Nested Loop Left Join (cost=973.06..6,828.75 rows=1 width=434) (actual time=8.719..19.298 rows=17 loops=1)

16. 0.029 19.221 ↓ 17.0 17 1

Nested Loop (cost=972.92..6,828.56 rows=1 width=402) (actual time=8.706..19.221 rows=17 loops=1)

  • Join Filter: ((c.cart_id)::bpchar = (sw.cart_id)::bpchar)
17. 0.025 19.102 ↓ 18.0 18 1

Nested Loop (cost=972.65..6,828.24 rows=1 width=401) (actual time=8.691..19.102 rows=18 loops=1)

18. 0.015 19.005 ↓ 18.0 18 1

Nested Loop (cost=972.23..6,827.62 rows=1 width=430) (actual time=8.680..19.005 rows=18 loops=1)

19. 0.017 18.900 ↓ 18.0 18 1

Nested Loop (cost=971.82..6,827.06 rows=1 width=402) (actual time=8.664..18.900 rows=18 loops=1)

20. 0.032 18.793 ↓ 18.0 18 1

Nested Loop (cost=971.40..6,826.21 rows=1 width=346) (actual time=8.652..18.793 rows=18 loops=1)

21. 0.018 17.987 ↓ 18.0 18 1

Nested Loop Left Join (cost=970.99..6,818.65 rows=1 width=331) (actual time=8.628..17.987 rows=18 loops=1)

  • Filter: (COALESCE((v.vendor_id)::bpchar, (i.vendor_id)::bpchar) = (i.vendor_id)::bpchar)
22. 0.033 17.951 ↓ 2.2 18 1

Nested Loop (cost=970.99..6,817.09 rows=8 width=331) (actual time=8.609..17.951 rows=18 loops=1)

23. 0.026 17.822 ↓ 2.4 12 1

Nested Loop (cost=970.57..6,778.32 rows=5 width=272) (actual time=8.594..17.822 rows=12 loops=1)

24. 5.189 17.724 ↑ 4.8 12 1

Hash Join (cost=970.29..6,672.68 rows=57 width=245) (actual time=8.575..17.724 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: 2487
25. 4.818 5.297 ↑ 1.0 2,499 1

Bitmap Heap Scan on sc_carts c (cost=111.93..5,807.77 rows=2,499 width=210) (actual time=0.751..5.297 rows=2,499 loops=1)

  • Recheck Cond: ((cart_type)::text = ANY ('{"MEDIA BUY CAMPAIGN","DIGITAL CAMPAIGN"}'::text[]))
  • Heap Blocks: exact=1673
26. 0.479 0.479 ↓ 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.479..0.479 rows=2,506 loops=1)

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

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

  • Buckets: 16384 Batches: 1 Memory Usage: 946kB
28. 4.078 4.078 ↑ 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.078 rows=12,327 loops=1)

29. 0.072 0.072 ↑ 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.006..0.006 rows=1 loops=12)

  • Index Cond: ((cart_id)::bpchar = (c.cart_id)::bpchar)
30. 0.096 0.096 ↑ 1.0 2 12

Index Scan using ix_sc_items_cart_id on sc_items i (cost=0.41..7.73 rows=2 width=59) (actual time=0.007..0.008 rows=2 loops=12)

  • Index Cond: ((cart_id)::bpchar = (c.cart_id)::bpchar)
31. 0.004 0.018 ↓ 0.0 0 18

Materialize (cost=0.00..1.44 rows=1 width=17) (actual time=0.001..0.001 rows=0 loops=18)

32. 0.014 0.014 ↓ 0.0 0 1

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

  • Filter: ((vendor_account_id)::bpchar = 'AC01010000000000'::bpchar)
  • Rows Removed by Filter: 35
33. 0.774 0.774 ↑ 1.0 1 18

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=18)

  • 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
34. 0.090 0.090 ↑ 1.0 1 18

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

  • Index Cond: ((contact_id)::bpchar = (c_1.contact_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND ((address_type)::text = 'Primary'::text))
35. 0.090 0.090 ↑ 1.0 1 18

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=18)

  • Index Cond: (((contact_id)::bpchar = (ad.contact_id)::bpchar) AND ((phone_type)::text = 'Primary'::text))
  • Filter: (is_active IS TRUE)
36. 0.072 0.072 ↑ 1.0 1 18

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=18)

  • Index Cond: ((contact_id)::bpchar = (ad.contact_id)::bpchar)
  • Filter: ((is_active IS TRUE) AND ((e_info_type)::text = 'Primary'::text))
37. 0.090 0.090 ↑ 1.0 1 18

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

  • 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[]))
38. 0.051 0.051 ↑ 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.003..0.003 rows=1 loops=17)

  • Index Cond: (((wf_type)::text = 'Campaign'::text) AND ((wf_status)::text = (sw.wf_status)::text))
39. 0.358 0.442 ↑ 1.0 103 17

HashAggregate (cost=19.00..20.09 rows=108 width=65) (actual time=0.009..0.026 rows=103 loops=17)

  • Group Key: r.cart_id
40. 0.084 0.084 ↑ 1.0 141 1

Seq Scan on sc_campaign_requests r (cost=0.00..18.30 rows=141 width=25) (actual time=0.007..0.084 rows=141 loops=1)

  • Filter: (campaign_request_status <> 'CLOSED'::text)
  • Rows Removed by Filter: 43
41. 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..2.93 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)
42. 0.100 0.221 ↑ 2.0 1 17

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

  • Group Key: sw_1.cart_id
43. 0.051 0.119 ↑ 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.006..0.007 rows=2 loops=17)

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

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

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

SubPlan (forGroupAggregate)

46. 0.001 0.002 ↓ 0.0 0 1

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

47. 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)

48. 0.017 0.136 ↓ 0.0 0 17

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

49. 0.017 0.119 ↓ 0.0 0 17

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

50. 0.102 0.102 ↓ 0.0 0 17

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

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

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

53. 0.034 0.238 ↑ 1.0 1 17

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

54. 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 = (c.master_account_id)::bpchar) AND ((workflow)::text = 'MEDIA_BUY'::text))
  • Filter: (is_active IS TRUE)
55. 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 = (c.master_account_id)::bpchar)
56. 0.085 0.085 ↑ 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.005..0.005 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))
57. 0.085 0.085 ↑ 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.005..0.005 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)
58. 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..2.88 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))
59. 0.017 0.153 ↑ 1.0 1 17

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

60. 0.034 0.136 ↑ 1.0 1 17

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

61. 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)
62. 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))
63. 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 : 74.638 ms
Execution time : 22.258 ms