Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Long running query leads to DB CPU reach 100% for 20 minutes

Description

Steps to reproduce:

  1. Navigate to Receiving app

  2. Filter by date created

  3. Filter by order status (open+pending)

Expected result: data is shown

Actual result: Data won’t show.

Additional information:

actual query

SELECT cs00000int_mod_orders_storage.count_estimate('SELECT jsonb,id FROM cs00000int_mod_orders_storage.titles WHERE ((get_tsvector(f_unaccent(titles.jsonb->>''acqUnitIds'')) @@ tsquery_phrase(f_unaccent(''c241e53b-fd56-448f-82c7-066d1333a532''))) OR ((true) AND ( (CASE WHEN length(lower(f_unaccent(''[]''))) <= 600 THEN left(lower(f_unaccent(titles.jsonb->>''acqUnitIds'')),600) NOT LIKE lower(f_unaccent(''[]'')) ELSE left(lower(f_unaccent(titles.jsonb->>''acqUnitIds'')),600) NOT LIKE left(lower(f_unaccent(''[]'')),600) OR lower(f_unaccent(titles.jsonb->>''acqUnitIds'')) NOT LIKE lower(f_unaccent(''[]'')) END) IS NOT TRUE))) AND (((titles.jsonb->''metadata''->>''createdDate'' >=''2025-02-17T00:00:00.000'') AND (titles.jsonb->''metadata''->>''createdDate'' <=''2025-02-24T23:59:59.999'')) AND ((titles.poLineId IN ( SELECT id FROM po_line WHERE po_line.purchaseOrderId IN ( SELECT id FROM purchase_order WHERE CASE WHEN length(lower(f_unaccent(''Pending''))) <= 600 THEN left(lower(f_unaccent(purchase_order.jsonb->>''workflowStatus'')),600) LIKE lower(f_unaccent(''Pending'')) ELSE left(lower(f_unaccent(purchase_order.jsonb->>''workflowStatus'')),600) LIKE left(lower(f_unaccent(''Pending'')),600) AND lower(f_unaccent(purchase_order.jsonb->>''workflowStatus'')) LIKE lower(f_unaccent(''Pending'')) END))) OR (titles.poLineId IN ( SELECT id FROM po_line WHERE po_line.purchaseOrderId IN ( SELECT id FROM purchase_order WHERE CASE WHEN length(lower(f_unaccent(''Open''))) <= 600 THEN left(lower(f_unaccent(purchase_order.jsonb->>''workflowStatus'')),600) LIKE lower(f_unaccent(''Open'')) ELSE left(lower(f_unaccent(purchase_order.jsonb->>''workflowStatus'')),600) LIKE left(lower(f_unaccent(''Open'')),600) AND lower(f_unaccent(purchase_order.jsonb->>''workflowStatus'')) LIKE lower(f_unaccent(''Open'')) END)))))')

this query affecting DB CPU usage, it reach 100% and stayed at this level for 15-20 minutes. (see screenshot )

CSP Request Details

None

CSP Rejection Details

None

Potential Workaround

None

Attachments

1
  • 25 Feb 2025, 10:59 AM

Checklist

hide

Activity

Show:

Khalilah Gambrell February 25, 2025 at 2:07 PM

Hey , please assign issues to the applicable Development Team as a team will not see these issues in their backlog without this assignment.

Details

Assignee

Reporter

Priority

Development Team

Thunderjet

RCA Group

TBD

TestRail: Cases

Open TestRail: Cases

TestRail: Runs

Open TestRail: Runs

Created February 25, 2025 at 10:55 AM
Updated 5 days ago
TestRail: Cases
TestRail: Runs

Flag notifications