Skip to:

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

Investigate way to improve performance of Orders filtering

Description

We have generated 1.6 M PoLine records in DB for ±300K Orders.
when opening tab “Order Lines“ nothing is showing up even after long time.
In dev tool we can see that call to retrieve order lines failed after 30s of waiting

image-20240607-094909.png

After some time inside the logs of mod-orders-storage we can see “Getaway-Timeout“ error as well.
after investigating, we defined query going to the database that is taking almost 10 minutes:

SET search_path TO cs00000int_mod_orders_storage,public; SELECT jsonb,id FROM cs00000int_mod_orders_storage.po_line WHERE ((po_line.purchaseOrderId IN ( SELECT id FROM purchase_order WHERE get_tsvector(f_unaccent(purchase_order.jsonb->>'acqUnitIds')) @@ tsquery_phrase(f_unaccent('0ebb1f7d-983f-3026-8a4c-5318e0ebc041')))) OR ((true) AND ( (po_line.purchaseOrderId IN ( SELECT id FROM purchase_order WHERE CASE WHEN length(lower(f_unaccent('[]'))) <= 600 THEN left(lower(f_unaccent(purchase_order.jsonb->>'acqUnitIds')),600) NOT LIKE lower(f_unaccent('[]')) ELSE left(lower(f_unaccent(purchase_order.jsonb->>'acqUnitIds')),600) NOT LIKE left(lower(f_unaccent('[]')),600) OR lower(f_unaccent(purchase_order.jsonb->>'acqUnitIds')) NOT LIKE lower(f_unaccent('[]')) END)) IS NOT TRUE))) AND ((CASE WHEN length(lower(f_unaccent('Ongoing'))) <= 600 THEN left(lower(f_unaccent(po_line.jsonb->>'receiptStatus')),600) LIKE lower(f_unaccent('Ongoing')) ELSE left(lower(f_unaccent(po_line.jsonb->>'receiptStatus')),600) LIKE left(lower(f_unaccent('Ongoing')),600) AND lower(f_unaccent(po_line.jsonb->>'receiptStatus')) LIKE lower(f_unaccent('Ongoing')) END) OR (CASE WHEN length(lower(f_unaccent('Pending'))) <= 600 THEN left(lower(f_unaccent(po_line.jsonb->>'receiptStatus')),600) LIKE lower(f_unaccent('Pending')) ELSE left(lower(f_unaccent(po_line.jsonb->>'receiptStatus')),600) LIKE left(lower(f_unaccent('Pending')),600) AND lower(f_unaccent(po_line.jsonb->>'receiptStatus')) LIKE lower(f_unaccent('Pending')) END)) ORDER BY left(lower(f_unaccent(po_line.jsonb->'metadata'->>'updatedDate')),600) DESC, lower(f_unaccent(po_line.jsonb->'metadata'->>'updatedDate')) DESC LIMIT 50 OFFSET 0

this query needs to be optimized.

Environment

None

Potential Workaround

None

Attachments

1
  • 07 Jun 2024, 10:01 AM

Checklist

hide

Activity

Show:

Serhii_Nosko July 11, 2024 at 7:26 AM

Filtering response time has been improved in the Orders app together with sorting by adding new DB indexes, so going to close this ticket.

JenkinsNotifications July 9, 2024 at 7:34 AM

Deployed to the Quesnelia bf env. Moved status to In bugfix review from status Awaiting deployment. Please proceed with the verification.

Serhii_Nosko June 21, 2024 at 7:59 AM

These 4 indexes were helped, PTF does not observe issues with filtering/sorting any more on data set around 500k po lines.

Results for sorting is described in this ticket: MODORDSTOR-409

Orders → Order liner
Search all order lines(481K)
Sorting: POL number - 1,1 seconds
Update date- 1,2 seconds
Title or package name - 800ms

Indexes were added in scope of this PR: https://github.com/folio-org/mod-orders-storage/pull/418/files

Closing this ticket

Damien June 14, 2024 at 2:23 PM

Could you try creating these 4 indices, to make sure it fixes the issue in an existing environment ?

Damien June 13, 2024 at 10:46 PM
Edited

To sum things up, the following indices should resolve this issue. We could do more to speed things up, but I think this is the most important:

CREATE INDEX IF NOT EXISTS purchase_order_no_acq_unit ON ${tenant}_mod_orders_storage.purchase_order(jsonb) WHERE left(lower(f_unaccent(jsonb ->> 'acqUnitIds')), 600) NOT LIKE '[]';

CREATE INDEX IF NOT EXISTS po_line_updated_date_sort ON ${tenant}_mod_orders_storage.po_line (left(lower(f_unaccent(jsonb->'metadata'->>'updatedDate')),600) DESC, lower(f_unaccent(jsonb->'metadata'->>'updatedDate')) DESC);

CREATE INDEX IF NOT EXISTS po_line_title_or_package_sort ON ${tenant}_mod_orders_storage.po_line (left(lower(f_unaccent(jsonb->>'titleOrPackage')),600) DESC, lower(f_unaccent(jsonb->>'titleOrPackage')) DESC);

CREATE INDEX IF NOT EXISTS po_line_po_line_number_sort ON ${tenant}_mod_orders_storage.po_line (left(lower(f_unaccent(jsonb->>'poLineNumber')),600) DESC, lower(f_unaccent(jsonb->>'poLineNumber')) DESC);

Done

Details

Assignee

Reporter

Tester Assignee

Labels

Priority

Story Points

Sprint

Development Team

Thunderjet

Fix versions

Release

Quesnelia (R1 2024) Service Patch #1

CSP Approved

Yes

TestRail: Cases

Open TestRail: Cases

TestRail: Runs

Open TestRail: Runs

Created June 7, 2024 at 9:47 AM
Updated December 13, 2024 at 1:45 PM
Resolved June 21, 2024 at 8:00 AM
TestRail: Cases
TestRail: Runs

Flag notifications