explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gjx

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.568 ↓ 0.0 0 1

Subquery Scan on q (cost=1,902.39..1,902.51 rows=6 width=670) (actual time=0.568..0.568 rows=0 loops=1)

  • Filter: (q.actualsmscode IS NOT NULL)
2. 0.001 0.567 ↓ 0.0 0 1

HashAggregate (cost=1,902.39..1,902.45 rows=6 width=953) (actual time=0.567..0.567 rows=0 loops=1)

  • Group Key: ('TDLVCRA70'::text), (0), "*SELECT* 1".shippingid, "*SELECT* 1".poid, "*SELECT* 1".shipmobileno, "*SELECT* 1".smstext, ('Pending'::text), "*SELECT* 1".assigndate, "*SELECT* 1".srname, "*SELECT* 1".srmobileno, ('support@xpressbees.com'::text), "*SELECT* 1".createddate, (NULL::date), (NULL::date), "*SELECT* 1".clientmasterid, "*SELECT* 1".smsparameters, "*SELECT* 1".clientbusinessaccountid, "*SELECT* 1".smsshipmentdetailid, (NULL::text), "*SELECT* 1".new_deliverycode, (NULL::text)
3. 0.002 0.566 ↓ 0.0 0 1

Append (cost=484.04..1,902.07 rows=6 width=953) (actual time=0.566..0.566 rows=0 loops=1)

4. 0.000 0.560 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=484.04..484.14 rows=1 width=729) (actual time=0.560..0.560 rows=0 loops=1)

5. 0.001 0.560 ↓ 0.0 0 1

HashAggregate (cost=484.04..484.13 rows=1 width=729) (actual time=0.560..0.560 rows=0 loops=1)

  • Group Key: 'TDLVCRA70'::text, 0, gos.shippingid, gos.poid, gos.shipmobileno, $77, 'Pending'::text, gos.assigndate, gos.deliveryusername, gos.deliveryusermobileno, 'support@xpressbees.com'::text, now(), NULL::date, NULL::date, gos.clientmasterid, replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(($78)::text, '{ClientName}'::text, (CASE WHEN COALESCE(csa.isusevendornameasclientname, false) THEN COALESCE(gos.rtoname, 'Shopping'::character varying) ELSE COALESCE(gos.smsshortname, ''::character varying) END)::text), '{AWB}'::text, (COALESCE(gos.shippingid, ''::character varying))::text), '{awb}'::text, (COALESCE(gos.shippingid, ''::character varying))::text), '{itemCount}'::text, ((COALESCE(gos.totalitems, 1))::character varying)::text), '{NetPayment}'::text, ((COALESCE(gos.netpayment, '0'::numeric))::character varying)::text), '{IteamName}'::text, (COALESCE(gos.productinfo, 'product'::character varying))::text), '{POID}'::text, (COALESCE(gos.poid, ''::character varying))::text), '{CustomerFirstName}'::text, (COALESCE(gos.shipname, ''::character varying))::text), '{VendorName}'::text, (COALESCE(gos.rtoname, ''::character varying))::text), '{SenderName}'::text, (COALESCE(gos.sender_name, ''::character varying))::text), '{SRFirstName}'::text, (COALESCE(gos.deliveryusername, ''::character varying))::text), '{DeliveryUserMobNo}'::text, (COALESCE(gos.deliveryusermobileno, ''::character varying))::text), '{ItemSummary}'::text, CASE WHEN (COALESCE(gos.totalitems, 1) > 1) THEN concat(' and ', (COALESCE(gos.totalitems, 1))::character varying, ' more item''s') ELSE ''::text END), '{CustomerCareNo}'::text, '020-49116100'::text), '{NDRCode}'::text, (COALESCE(gos.otpno, ''::character varying))::text), '{NavigationLink}'::text, concat('fb.xbees.in/C/u/', replace(replace(replace(encode((gos.shippingid)::bytea, 'base64'::text), '+'::text, '.'::text), '='::text, '-'::text), '/'::text, '~'::text))), gos.actual_clientbusinessaccountid, gos.smsshipmentdetailid, NULL::text, gos.new_deliverycode, NULL::text
6.          

Initplan (for HashAggregate)

7. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..20.75 rows=1 width=32) (never executed)

8. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_16 (cost=0.00..20.75 rows=1 width=32) (never executed)

  • Filter: ((actualsmscode)::text = 'TDLVCRA70'::text)
9. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..20.75 rows=1 width=32) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_17 (cost=0.00..20.75 rows=1 width=32) (never executed)

  • Filter: ((actualsmscode)::text = 'TDLVCRA70'::text)
11. 0.011 0.559 ↓ 0.0 0 1

Hash Join (cost=10.36..442.49 rows=1 width=729) (actual time=0.559..0.559 rows=0 loops=1)

  • Hash Cond: (csa.clientbusinessaccountid = gos.clientbusinessaccountid)
12. 0.548 0.548 ↑ 1,263.0 1 1

Seq Scan on client_sms_type_allocation csa (cost=0.00..427.30 rows=1,263 width=5) (actual time=0.548..0.548 rows=1 loops=1)

  • Filter: (COALESCE(isactive, false) AND ((smstemplatetype)::text = 'DeliveryOFD'::text))
  • Rows Removed by Filter: 2
13. 0.000 0.000 ↓ 0.0 0 1

Hash (cost=10.35..10.35 rows=1 width=732) (actual time=0.000..0.000 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
14. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on getofdshipmentsforsmsqueue gos (cost=0.00..10.35 rows=1 width=732) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((otpno IS NOT NULL) AND ((uniquecode)::text = ANY ('{XBU00241,XBU00240}'::text[])) AND (length(shipmobileno) >= 10))
15. 0.000 0.001 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=500.97..564.25 rows=1 width=879) (actual time=0.001..0.001 rows=0 loops=1)

16. 0.000 0.001 ↓ 0.0 0 1

HashAggregate (cost=500.97..564.24 rows=1 width=879) (actual time=0.001..0.001 rows=0 loops=1)

  • Group Key: (SubPlan 14), 0, gos_1.shippingid, gos_1.poid, gos_1.shipmobileno, (SubPlan 15), 'Pending'::text, gos_1.assigndate, gos_1.deliveryusername, gos_1.deliveryusermobileno, 'support@xpressbees.com'::text, now(), NULL::date, NULL::date, gos_1.clientmasterid, replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(((SubPlan 16))::text, '{ClientName}'::text, (CASE WHEN COALESCE(csa_1.isusevendornameasclientname, false) THEN COALESCE(gos_1.rtoname, 'Shopping'::character varying) ELSE COALESCE(gos_1.smsshortname, ''::character varying) END)::text), '{AWB}'::text, (COALESCE(gos_1.shippingid, ''::character varying))::text), '{awb}'::text, (COALESCE(gos_1.shippingid, ''::character varying))::text), '{itemCount}'::text, ((COALESCE(gos_1.totalitems, 1))::character varying)::text), '{NetPayment}'::text, ((COALESCE(gos_1.netpayment, '0'::numeric))::character varying)::text), '{IteamName}'::text, (COALESCE(gos_1.productinfo, 'product'::character varying))::text), '{POID}'::text, (COALESCE(gos_1.poid, ''::character varying))::text), '{CustomerFirstName}'::text, (COALESCE(gos_1.shipname, ''::character varying))::text), '{VendorName}'::text, (COALESCE(gos_1.rtoname, ''::character varying))::text), '{SenderName}'::text, (COALESCE(gos_1.sender_name, ''::character varying))::text), '{SRFirstName}'::text, (COALESCE(gos_1.deliveryusername, ''::character varying))::text), '{DeliveryUserMobNo}'::text, (COALESCE(gos_1.deliveryusermobileno, ''::character varying))::text), '{ItemSummary}'::text, CASE WHEN (COALESCE(gos_1.totalitems, 1) > 1) THEN concat(' and ', (COALESCE(gos_1.totalitems, 1))::character varying, ' more item''s') ELSE ''::text END), '{CustomerCareNo}'::text, '020-49116100'::text), '{OTPNo}'::text, (COALESCE(gos_1.otpno, ''::character varying))::text), '{NavigationLink}'::text, concat('fb.xbees.in/C/u/', replace(replace(replace(encode((gos_1.shippingid)::bytea, 'base64'::text), '+'::text, '.'::text), '='::text, '-'::text), '/'::text, '~'::text))), gos_1.actual_clientbusinessaccountid, gos_1.smsshipmentdetailid, gos_1.paymenturl, gos_1.new_deliverycode, NULL::text
17. 0.001 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.00..500.92 rows=1 width=879) (actual time=0.001..0.001 rows=0 loops=1)

  • Join Filter: (gos_1.clientbusinessaccountid = csa_1.clientbusinessaccountid)
18. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on getofdshipmentsforsmsqueue gos_1 (cost=0.00..10.35 rows=1 width=882) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (((uniquecode)::text = ANY ('{XBU00241,XBU00240}'::text[])) AND (length(shipmobileno) >= 10))
19. 0.000 0.000 ↓ 0.0 0

Seq Scan on client_sms_type_allocation csa_1 (cost=0.00..427.30 rows=1 width=5) (never executed)

  • Filter: (COALESCE(isactive, false) AND iscontainsrcontactno AND ((smstemplatetype)::text = 'DeliveryOFD'::text))
20.          

SubPlan (for Nested Loop)

21. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.06..21.07 rows=1 width=122) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.06..21.07 rows=1 width=122) (never executed)

  • Sort Key: sms_13.clientbusinessaccountid DESC
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_13 (cost=0.00..21.05 rows=1 width=122) (never executed)

  • Filter: (COALESCE(isactive, false) AND (clientid = 500,001) AND ((smscode)::text = CASE WHEN ((gos_1.ordertype)::text = ANY ('{COD,CashOnDelivery}'::text[])) THEN 'TDLVCOD10'::text ELSE 'TDLVPPD10'::text END))
24. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.06..21.07 rows=1 width=36) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.06..21.07 rows=1 width=36) (never executed)

  • Sort Key: sms_14.clientbusinessaccountid DESC
26. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_14 (cost=0.00..21.05 rows=1 width=36) (never executed)

  • Filter: (COALESCE(isactive, false) AND (clientid = 500,001) AND ((smscode)::text = CASE WHEN ((gos_1.ordertype)::text = ANY ('{COD,CashOnDelivery}'::text[])) THEN 'TDLVCOD10'::text ELSE 'TDLVPPD10'::text END))
27. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..21.05 rows=1 width=36) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_15 (cost=0.00..21.05 rows=1 width=36) (never executed)

  • Filter: (COALESCE(isactive, false) AND (clientid = 500,001) AND ((smscode)::text = CASE WHEN ((gos_1.ordertype)::text = ANY ('{COD,CashOnDelivery}'::text[])) THEN 'TDLVCOD10'::text ELSE 'TDLVPPD10'::text END))
29. 0.000 0.001 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=103.42..167.62 rows=1 width=770) (actual time=0.001..0.001 rows=0 loops=1)

30. 0.001 0.001 ↓ 0.0 0 1

HashAggregate (cost=103.42..167.61 rows=1 width=770) (actual time=0.001..0.001 rows=0 loops=1)

  • Group Key: (SubPlan 11), 0, gos_2.shippingid, gos_2.poid, gos_2.shipmobileno, (SubPlan 12), 'Pending'::text, gos_2.assigndate, gos_2.deliveryusername, gos_2.deliveryusermobileno, 'support@xpressbees.com'::text, now(), NULL::date, NULL::date, gos_2.clientmasterid, replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(((SubPlan 13))::text, '{ClientName}'::text, (CASE WHEN COALESCE(csa_2.isusevendornameasclientname, false) THEN COALESCE(gos_2.rtoname, 'Shopping'::character varying) ELSE COALESCE(gos_2.smsshortname, ''::character varying) END)::text), '{AWB}'::text, (COALESCE(gos_2.shippingid, ''::character varying))::text), '{awb}'::text, (COALESCE(gos_2.shippingid, ''::character varying))::text), '{itemCount}'::text, ((COALESCE(gos_2.totalitems, 1))::character varying)::text), '{NetPayment}'::text, ((COALESCE(gos_2.netpayment, '0'::numeric))::character varying)::text), '{IteamName}'::text, (COALESCE(gos_2.productinfo, 'product'::character varying))::text), '{POID}'::text, (COALESCE(gos_2.poid, ''::character varying))::text), '{CustomerFirstName}'::text, (COALESCE(gos_2.shipname, ''::character varying))::text), '{VendorName}'::text, (COALESCE(gos_2.rtoname, ''::character varying))::text), '{SenderName}'::text, (COALESCE(gos_2.sender_name, ''::character varying))::text), '{SRFirstName}'::text, (COALESCE(gos_2.deliveryusername, ''::character varying))::text), '{DeliveryUserMobNo}'::text, (COALESCE(gos_2.deliveryusermobileno, ''::character varying))::text), '{ItemSummary}'::text, CASE WHEN (COALESCE(gos_2.totalitems, 1) > 1) THEN concat(' and ', (COALESCE(gos_2.totalitems, 1))::character varying, ' more item''s') ELSE ''::text END), '{CustomerCareNo}'::text, '020-49116100'::text), '{NavigationLink}'::text, concat('fb.xbees.in/C/u/', replace(replace(replace(encode((gos_2.shippingid)::bytea, 'base64'::text), '+'::text, '.'::text), '='::text, '-'::text), '/'::text, '~'::text))), gos_2.actual_clientbusinessaccountid, gos_2.smsshipmentdetailid, gos_2.paymenturl, gos_2.new_deliverycode, NULL::text
31. 0.000 0.000 ↓ 0.0 0 1

Nested Loop (cost=4.34..103.37 rows=1 width=770) (actual time=0.000..0.000 rows=0 loops=1)

32. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on getofdshipmentsforsmsqueue gos_2 (cost=0.00..10.35 rows=1 width=764) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (((uniquecode)::text = ANY ('{XBU00241,XBU00240}'::text[])) AND (length(shipmobileno) >= 10))
33. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on client_sms_type_allocation csa_2 (cost=4.34..28.84 rows=1 width=10) (never executed)

  • Recheck Cond: (clientid = gos_2.clientmasterid)
  • Filter: (COALESCE(isactive, false) AND (NOT COALESCE(iscontainsrcontactno, false)) AND ((smstemplatetype)::text = 'DeliveryOFD'::text) AND ((COALESCE(isparensmsallow, false) AND (clientid = gos_2.clientmasterid)) OR (clientbusinessaccountid = gos_2.clientbusinessaccountid)))
34. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_client_sms_type_allocation_status_clientid_busin_parentallo (cost=0.00..4.34 rows=7 width=0) (never executed)

  • Index Cond: (clientid = gos_2.clientmasterid)
35.          

SubPlan (for Nested Loop)

36. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.36..21.37 rows=1 width=122) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.36..21.37 rows=1 width=122) (never executed)

  • Sort Key: sms_10.clientbusinessaccountid DESC
38. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_10 (cost=0.00..21.35 rows=1 width=122) (never executed)

  • Filter: (COALESCE(isactive, false) AND ((smscode)::text = CASE WHEN ((gos_2.ordertype)::text = ANY ('{COD,CashOnDelivery}'::text[])) THEN 'TDLVCOD10'::text ELSE 'TDLVPPD10'::text END) AND ((COALESCE(csa_2.isparensmsallow, false) AND (csa_2.clientid = gos_2.clientmasterid) AND (clientmasterid = gos_2.clientmasterid)) OR (clientbusinessaccountid = ANY (ARRAY[gos_2.clientbusinessaccountid, 0]))))
39. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.36..21.37 rows=1 width=36) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.36..21.37 rows=1 width=36) (never executed)

  • Sort Key: sms_11.clientbusinessaccountid DESC
41. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_11 (cost=0.00..21.35 rows=1 width=36) (never executed)

  • Filter: (COALESCE(isactive, false) AND ((smscode)::text = CASE WHEN ((gos_2.ordertype)::text = ANY ('{COD,CashOnDelivery}'::text[])) THEN 'TDLVCOD10'::text ELSE 'TDLVPPD10'::text END) AND ((COALESCE(csa_2.isparensmsallow, false) AND (csa_2.clientid = gos_2.clientmasterid) AND (clientmasterid = gos_2.clientmasterid)) OR (clientbusinessaccountid = ANY (ARRAY[gos_2.clientbusinessaccountid, 0]))))
42. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.36..21.37 rows=1 width=36) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.36..21.37 rows=1 width=36) (never executed)

  • Sort Key: sms_12.clientbusinessaccountid DESC
44. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_12 (cost=0.00..21.35 rows=1 width=36) (never executed)

  • Filter: (COALESCE(isactive, false) AND ((smscode)::text = CASE WHEN ((gos_2.ordertype)::text = ANY ('{COD,CashOnDelivery}'::text[])) THEN 'TDLVCOD10'::text ELSE 'TDLVPPD10'::text END) AND ((COALESCE(csa_2.isparensmsallow, false) AND (csa_2.clientid = gos_2.clientmasterid) AND (clientmasterid = gos_2.clientmasterid)) OR (clientbusinessaccountid = ANY (ARRAY[gos_2.clientbusinessaccountid, 0]))))
45. 0.000 0.001 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=103.02..166.78 rows=1 width=964) (actual time=0.001..0.001 rows=0 loops=1)

46. 0.000 0.001 ↓ 0.0 0 1

HashAggregate (cost=103.02..166.77 rows=1 width=964) (actual time=0.001..0.001 rows=0 loops=1)

  • Group Key: (SubPlan 8), 0, gos_3.shippingid, gos_3.poid, gos_3.shipmobileno, (SubPlan 9), 'Pending'::text, gos_3.assigndate, gos_3.deliveryusername, gos_3.deliveryusermobileno, 'support@xpressbees.com'::text, now(), NULL::date, NULL::date, gos_3.clientmasterid, replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(((SubPlan 10))::text, '{ClientName}'::text, (CASE WHEN COALESCE(csa_3.isusevendornameasclientname, false) THEN COALESCE(gos_3.rtoname, 'Shopping'::character varying) ELSE COALESCE(gos_3.smsshortname, ''::character varying) END)::text), '{AWB}'::text, (COALESCE(gos_3.shippingid, ''::character varying))::text), '{awb}'::text, (COALESCE(gos_3.shippingid, ''::character varying))::text), '{itemCount}'::text, ((COALESCE(gos_3.totalitems, 1))::character varying)::text), '{NetPayment}'::text, ((COALESCE(gos_3.netpayment, '0'::numeric))::character varying)::text), '{IteamName}'::text, (COALESCE(gos_3.productinfo, 'product'::character varying))::text), '{POID}'::text, (COALESCE(gos_3.poid, ''::character varying))::text), '{CustomerFirstName}'::text, (COALESCE(gos_3.shipname, ''::character varying))::text), '{VendorName}'::text, (COALESCE(gos_3.rtoname, ''::character varying))::text), '{SenderName}'::text, (COALESCE(gos_3.sender_name, ''::character varying))::text), '{SRFirstName}'::text, (COALESCE(gos_3.deliveryusername, ''::character varying))::text), '{DeliveryUserMobNo}'::text, (COALESCE(gos_3.deliveryusermobileno, ''::character varying))::text), '{ItemSummary}'::text, CASE WHEN (COALESCE(gos_3.totalitems, 1) > 1) THEN concat(' and ', (COALESCE(gos_3.totalitems, 1))::character varying, ' more item''s') ELSE ''::text END), '{CustomerCareNo}'::text, '020-49116100'::text), '{NavigationLink}'::text, concat('fb.xbees.in/C/u/', replace(replace(replace(encode((gos_3.shippingid)::bytea, 'base64'::text), '+'::text, '.'::text), '='::text, '-'::text), '/'::text, '~'::text))), '{deliverydate}'::text, (COALESCE(((gos_3.deliverydate)::date)::character varying, ''::character varying))::text), '{deliverytime}'::text, (COALESCE((gos_3.deliverytime)::character varying(5), ''::character varying))::text), '{recipient}'::text, (COALESCE(gos_3.receivername, ''::character varying))::text), gos_3.actual_clientbusinessaccountid, gos_3.smsshipmentdetailid, NULL::text, NULL::text, NULL::text
47. 0.001 0.001 ↓ 0.0 0 1

Nested Loop (cost=4.34..102.96 rows=1 width=964) (actual time=0.001..0.001 rows=0 loops=1)

48. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on getofdshipmentsforsmsqueue gos_3 (cost=0.00..10.38 rows=1 width=958) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((feedbacklink IS NULL) AND ((uniquecode)::text <> ALL ('{XBU00241,XBU00240,XBU00219}'::text[])) AND (length(shipmobileno) >= 10))
49. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on client_sms_type_allocation csa_3 (cost=4.34..28.84 rows=1 width=10) (never executed)

  • Recheck Cond: (clientid = gos_3.clientmasterid)
  • Filter: (COALESCE(isactive, false) AND ((smstemplatetype)::text = 'DeliveryNDR'::text) AND ((COALESCE(isparensmsallow, false) AND (clientid = gos_3.clientmasterid)) OR (clientbusinessaccountid = gos_3.clientbusinessaccountid)))
50. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_client_sms_type_allocation_status_clientid_busin_parentallo (cost=0.00..4.34 rows=7 width=0) (never executed)

  • Index Cond: (clientid = gos_3.clientmasterid)
51.          

SubPlan (for Nested Loop)

52. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.21..21.21 rows=1 width=122) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.21..21.21 rows=1 width=122) (never executed)

  • Sort Key: sms_7.clientbusinessaccountid DESC
54. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_7 (cost=0.00..21.20 rows=1 width=122) (never executed)

  • Filter: (COALESCE(isactive, false) AND ((uniquecode)::text = (gos_3.uniquecode)::text) AND ((COALESCE(csa_3.isparensmsallow, false) AND (csa_3.clientid = gos_3.clientmasterid) AND (clientmasterid = gos_3.clientmasterid)) OR (clientbusinessaccountid = ANY (ARRAY[gos_3.clientbusinessaccountid, 0]))))
55. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.21..21.21 rows=1 width=36) (never executed)

56. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.21..21.21 rows=1 width=36) (never executed)

  • Sort Key: sms_8.clientbusinessaccountid DESC
57. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_8 (cost=0.00..21.20 rows=1 width=36) (never executed)

  • Filter: (COALESCE(isactive, false) AND ((uniquecode)::text = (gos_3.uniquecode)::text) AND ((COALESCE(csa_3.isparensmsallow, false) AND (csa_3.clientid = gos_3.clientmasterid) AND (clientmasterid = gos_3.clientmasterid)) OR (clientbusinessaccountid = ANY (ARRAY[gos_3.clientbusinessaccountid, 0]))))
58. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.21..21.21 rows=1 width=36) (never executed)

59. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.21..21.21 rows=1 width=36) (never executed)

  • Sort Key: sms_9.clientbusinessaccountid DESC
60. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_9 (cost=0.00..21.20 rows=1 width=36) (never executed)

  • Filter: (COALESCE(isactive, false) AND ((uniquecode)::text = (gos_3.uniquecode)::text) AND ((COALESCE(csa_3.isparensmsallow, false) AND (csa_3.clientid = gos_3.clientmasterid) AND (clientmasterid = gos_3.clientmasterid)) OR (clientbusinessaccountid = ANY (ARRAY[gos_3.clientbusinessaccountid, 0]))))
61. 0.001 0.001 ↓ 0.0 0 1

Subquery Scan on *SELECT* 5 (cost=103.22..166.99 rows=1 width=996) (actual time=0.001..0.001 rows=0 loops=1)

62. 0.000 0.000 ↓ 0.0 0 1

HashAggregate (cost=103.22..166.98 rows=1 width=996) (actual time=0.000..0.000 rows=0 loops=1)

  • Group Key: (SubPlan 5), 0, gos_4.shippingid, gos_4.poid, gos_4.shipmobileno, (SubPlan 6), 'Pending'::text, gos_4.assigndate, gos_4.deliveryusername, gos_4.deliveryusermobileno, 'support@xpressbees.com'::text, now(), NULL::date, NULL::date, gos_4.clientmasterid, replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(((SubPlan 7))::text, '{ClientName}'::text, (CASE WHEN COALESCE(csa_4.isusevendornameasclientname, false) THEN COALESCE(gos_4.rtoname, 'Shopping'::character varying) ELSE COALESCE(gos_4.smsshortname, ''::character varying) END)::text), '{AWB}'::text, (COALESCE(gos_4.shippingid, ''::character varying))::text), '{awb}'::text, (COALESCE(gos_4.shippingid, ''::character varying))::text), '{itemCount}'::text, ((COALESCE(gos_4.totalitems, 1))::character varying)::text), '{NetPayment}'::text, ((COALESCE(gos_4.netpayment, '0'::numeric))::character varying)::text), '{IteamName}'::text, (COALESCE(gos_4.productinfo, 'product'::character varying))::text), '{POID}'::text, (COALESCE(gos_4.poid, ''::character varying))::text), '{CustomerFirstName}'::text, (COALESCE(gos_4.shipname, ''::character varying))::text), '{VendorName}'::text, (COALESCE(gos_4.rtoname, ''::character varying))::text), '{SenderName}'::text, (COALESCE(gos_4.sender_name, ''::character varying))::text), '{SRFirstName}'::text, (COALESCE(gos_4.deliveryusername, ''::character varying))::text), '{DeliveryUserMobNo}'::text, (COALESCE(gos_4.deliveryusermobileno, ''::character varying))::text), '{ItemSummary}'::text, CASE WHEN (COALESCE(gos_4.totalitems, 1) > 1) THEN concat(' and ', (COALESCE(gos_4.totalitems, 1))::character varying, ' more item''s') ELSE ''::text END), '{CustomerCareNo}'::text, '020-49116100'::text), '{NavigationLink}'::text, concat('fb.xbees.in/C/u/', replace(replace(replace(encode((gos_4.shippingid)::bytea, 'base64'::text), '+'::text, '.'::text), '='::text, '-'::text), '/'::text, '~'::text))), '{deliverydate}'::text, (COALESCE(((gos_4.deliverydate)::date)::character varying, ''::character varying))::text), '{deliverytime}'::text, (COALESCE((gos_4.deliverytime)::character varying(5), ''::character varying))::text), '{recipient}'::text, (CASE WHEN (length((gos_4.receivername_with_deliveredto)::text) > 0) THEN gos_4.receivername_with_deliveredto ELSE COALESCE(gos_4.receivername, ''::character varying) END)::text), gos_4.actual_clientbusinessaccountid, gos_4.smsshipmentdetailid, NULL::text, NULL::text, NULL::text
63. 0.000 0.000 ↓ 0.0 0 1

Nested Loop (cost=4.34..103.17 rows=1 width=996) (actual time=0.000..0.000 rows=0 loops=1)

64. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on getofdshipmentsforsmsqueue gos_4 (cost=0.00..10.57 rows=1 width=990) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((feedbacklink IS NULL) AND (length(shipmobileno) >= 10) AND (lower((ordertype)::text) = 'prepaid'::text) AND ((uniquecode)::text = ANY ('{XBU00219,XBU00220,XBU00221,XBU00222,XBU00346,XBU00347,XBU00348}'::text[])))
65. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on client_sms_type_allocation csa_4 (cost=4.34..28.84 rows=1 width=10) (never executed)

  • Recheck Cond: (clientid = gos_4.clientmasterid)
  • Filter: (COALESCE(isactive, false) AND ((smstemplatetype)::text = 'DeliveryConfirmation'::text) AND ((COALESCE(isparensmsallow, false) AND (clientid = gos_4.clientmasterid)) OR (clientbusinessaccountid = gos_4.clientbusinessaccountid)))
66. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_client_sms_type_allocation_status_clientid_busin_parentallo (cost=0.00..4.34 rows=7 width=0) (never executed)

  • Index Cond: (clientid = gos_4.clientmasterid)
67.          

SubPlan (for Nested Loop)

68. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.21..21.21 rows=1 width=122) (never executed)

69. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.21..21.21 rows=1 width=122) (never executed)

  • Sort Key: sms_4.clientbusinessaccountid DESC
70. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_4 (cost=0.00..21.20 rows=1 width=122) (never executed)

  • Filter: (COALESCE(isactive, false) AND ((uniquecode)::text = (gos_4.uniquecode)::text) AND ((COALESCE(csa_4.isparensmsallow, false) AND (csa_4.clientid = gos_4.clientmasterid) AND (clientmasterid = gos_4.clientmasterid)) OR (clientbusinessaccountid = ANY (ARRAY[gos_4.clientbusinessaccountid, 0]))))
71. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.21..21.21 rows=1 width=36) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.21..21.21 rows=1 width=36) (never executed)

  • Sort Key: sms_5.clientbusinessaccountid DESC
73. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_5 (cost=0.00..21.20 rows=1 width=36) (never executed)

  • Filter: (COALESCE(isactive, false) AND ((uniquecode)::text = (gos_4.uniquecode)::text) AND ((COALESCE(csa_4.isparensmsallow, false) AND (csa_4.clientid = gos_4.clientmasterid) AND (clientmasterid = gos_4.clientmasterid)) OR (clientbusinessaccountid = ANY (ARRAY[gos_4.clientbusinessaccountid, 0]))))
74. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.21..21.21 rows=1 width=36) (never executed)

75. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.21..21.21 rows=1 width=36) (never executed)

  • Sort Key: sms_6.clientbusinessaccountid DESC
76. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_6 (cost=0.00..21.20 rows=1 width=36) (never executed)

  • Filter: (COALESCE(isactive, false) AND ((uniquecode)::text = (gos_4.uniquecode)::text) AND ((COALESCE(csa_4.isparensmsallow, false) AND (csa_4.clientid = gos_4.clientmasterid) AND (clientmasterid = gos_4.clientmasterid)) OR (clientbusinessaccountid = ANY (ARRAY[gos_4.clientbusinessaccountid, 0]))))
77. 0.000 0.000 ↓ 0.0 0 1

Subquery Scan on *SELECT* 6 (cost=266.72..352.30 rows=1 width=1,382) (actual time=0.000..0.000 rows=0 loops=1)

78. 0.000 0.000 ↓ 0.0 0 1

HashAggregate (cost=266.72..352.29 rows=1 width=1,382) (actual time=0.000..0.000 rows=0 loops=1)

  • Group Key: (SubPlan 1), 0, gos_5.shippingid, gos_5.poid, gos_5.shipmobileno, (SubPlan 2), 'Pending'::text, gos_5.assigndate, gos_5.deliveryusername, gos_5.deliveryusermobileno, 'support@xpressbees.com'::text, now(), NULL::date, NULL::date, gos_5.clientmasterid, replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(((SubPlan 3))::text, '{ClientName}'::text, (CASE WHEN COALESCE(csa_5.isusevendornameasclientname, false) THEN COALESCE(gos_5.rtoname, 'Shopping'::character varying) ELSE COALESCE(gos_5.smsshortname, ''::character varying) END)::text), '{AWB}'::text, (COALESCE(gos_5.shippingid, ''::character varying))::text), '{awb}'::text, (COALESCE(gos_5.shippingid, ''::character varying))::text), '{itemCount}'::text, ((COALESCE(gos_5.totalitems, 1))::character varying)::text), '{NetPayment}'::text, ((COALESCE(gos_5.netpayment, '0'::numeric))::character varying)::text), '{IteamName}'::text, (COALESCE(gos_5.productinfo, 'product'::character varying))::text), '{POID}'::text, (COALESCE(gos_5.poid, ''::character varying))::text), '{CustomerFirstName}'::text, (COALESCE(gos_5.shipname, ''::character varying))::text), '{VendorName}'::text, (COALESCE(gos_5.rtoname, ''::character varying))::text), '{SenderName}'::text, (COALESCE(gos_5.sender_name, ''::character varying))::text), '{SRFirstName}'::text, (COALESCE(gos_5.deliveryusername, ''::character varying))::text), '{DeliveryUserMobNo}'::text, (COALESCE(gos_5.deliveryusermobileno, ''::character varying))::text), '{ItemSummary}'::text, CASE WHEN (COALESCE(gos_5.totalitems, 1) > 1) THEN concat(' and ', (COALESCE(gos_5.totalitems, 1))::character varying, ' more item''s') ELSE ''::text END), '{CustomerCareNo}'::text, '020-49116100'::text), '{NavigationLink}'::text, concat('fb.xbees.in/C/u/', replace(replace(replace(encode((gos_5.shippingid)::bytea, 'base64'::text), '+'::text, '.'::text), '='::text, '-'::text), '/'::text, '~'::text))), '{deliverydate}'::text, (COALESCE(((gos_5.deliverydate)::date)::character varying, ''::character varying))::text), '{deliverytime}'::text, (COALESCE((gos_5.deliverytime)::character varying(5), ''::character varying))::text), '{recipient}'::text, (COALESCE(gos_5.receivername, ''::character varying))::text), '{FeedBacklink}'::text, (COALESCE(gos_5.feedbacklink, ''::character varying))::text), gos_5.actual_clientbusinessaccountid, gos_5.smsshipmentdetailid, NULL::text, NULL::text, (SubPlan 4)
79. 0.000 0.000 ↓ 0.0 0 1

Nested Loop (cost=0.29..266.66 rows=1 width=1,382) (actual time=0.000..0.000 rows=0 loops=1)

80. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on getofdshipmentsforsmsqueue gos_5 (cost=0.00..10.55 rows=6 width=1,376) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((feedbacklink IS NOT NULL) AND ((uniquecode)::text <> ALL ('{XBU00241,XBU00240}'::text[])) AND (length(shipmobileno) >= 10) AND (((uniquecode)::text <> 'XBU00269'::text) OR (((uniquecode)::text = 'XBU00269'::text) AND (lower((ordertype)::text) = 'prepaid'::text))))
81. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_client_sms_type_allocation_status_clientid_busin_parentallo on client_sms_type_allocation csa_5 (cost=0.29..28.42 rows=1 width=10) (never executed)

  • Index Cond: (clientid = gos_5.clientmasterid)
  • Filter: (COALESCE(isactive, false) AND ((smstemplatetype)::text = ANY ('{DeliveryConfirmation,DeliveryNDR}'::text[])) AND ((COALESCE(isparensmsallow, false) AND (clientid = gos_5.clientmasterid)) OR (clientbusinessaccountid = gos_5.clientbusinessaccountid)))
82.          

SubPlan (for Nested Loop)

83. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.36..21.37 rows=1 width=122) (never executed)

84. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.36..21.37 rows=1 width=122) (never executed)

  • Sort Key: sms.clientid
85. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms (cost=0.00..21.35 rows=1 width=122) (never executed)

  • Filter: (COALESCE(isactive, false) AND (clientid <> 0) AND ((uniquecode)::text = (gos_5.uniquecode)::text) AND ((COALESCE(csa_5.isparensmsallow, false) AND (csa_5.clientid = gos_5.clientmasterid) AND (clientmasterid = gos_5.clientmasterid)) OR (clientbusinessaccountid = ANY (ARRAY[gos_5.clientbusinessaccountid, 0]))))
86. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.36..21.37 rows=1 width=36) (never executed)

87. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.36..21.37 rows=1 width=36) (never executed)

  • Sort Key: sms_1.clientid
88. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_1 (cost=0.00..21.35 rows=1 width=36) (never executed)

  • Filter: (COALESCE(isactive, false) AND (clientid <> 0) AND ((uniquecode)::text = (gos_5.uniquecode)::text) AND ((COALESCE(csa_5.isparensmsallow, false) AND (csa_5.clientid = gos_5.clientmasterid) AND (clientmasterid = gos_5.clientmasterid)) OR (clientbusinessaccountid = ANY (ARRAY[gos_5.clientbusinessaccountid, 0]))))
89. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.36..21.37 rows=1 width=36) (never executed)

90. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.36..21.37 rows=1 width=36) (never executed)

  • Sort Key: sms_2.clientid
91. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_2 (cost=0.00..21.35 rows=1 width=36) (never executed)

  • Filter: (COALESCE(isactive, false) AND (clientid <> 0) AND ((uniquecode)::text = (gos_5.uniquecode)::text) AND ((COALESCE(csa_5.isparensmsallow, false) AND (csa_5.clientid = gos_5.clientmasterid) AND (clientmasterid = gos_5.clientmasterid)) OR (clientbusinessaccountid = ANY (ARRAY[gos_5.clientbusinessaccountid, 0]))))
92. 0.000 0.000 ↓ 0.0 0

Limit (cost=21.37..21.37 rows=1 width=4) (never executed)

93. 0.000 0.000 ↓ 0.0 0

Sort (cost=21.37..21.37 rows=1 width=4) (never executed)

  • Sort Key: sms_3.clientid
94. 0.000 0.000 ↓ 0.0 0

Seq Scan on tempsms_client_wise_sms_template_final sms_3 (cost=0.00..21.36 rows=1 width=4) (never executed)

  • Filter: (COALESCE(isactive, false) AND (clientid <> 0) AND ((uniquecode)::text = (gos_5.uniquecode)::text) AND ((COALESCE(csa_5.isparensmsallow, false) AND (csa_5.clientid = gos_5.clientmasterid) AND (clientmasterid = gos_5.clientmasterid)) OR (clientbusinessaccountid = ANY (ARRAY[gos_5.clientbusinessaccountid, 0]))))
Planning time : 6.883 ms
Execution time : 1.525 ms