Skip to:

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

Custom fields - Check if db indexes are getting used during filter/search for custom fields in UI

Description

purchase_order table. If indexes are missing, we should create them.

Result

The logs of mod-orders-storage are showing missing indexes for customFields:

WARN CQL2PgJSON Doing LIKE search without index for purchase_order.jsonb->'customFields'->>'checkbox', CQL >>> SQL: customFields.checkbox == true >>> lower(f_unaccent(purchase_order.jsonb->'customFields'->>'checkbox')) LIKE lower(f_unaccent('true')) WARN CQL2PgJSON Doing LIKE search without index for purchase_order.jsonb->'customFields'->>'radioselect', CQL >>> SQL: customFields.radioselect == opt_0 >>> lower(f_unaccent(purchase_order.jsonb->'customFields'->>'radioselect')) LIKE lower(f_unaccent('opt\_0')) WARN CQL2PgJSON Doing LIKE search without index for purchase_order.jsonb->'customFields'->>'singleselect', CQL >>> SQL: customFields.singleselect == opt_0 >>> lower(f_unaccent(purchase_order.jsonb->'customFields'->>'singleselect')) LIKE lower(f_unaccent('opt\_0')) WARN CQL2PgJSON Doing SQL query without index for purchase_order.jsonb->'customFields'->>'datepicker', CQL >>> SQL: customFields.datepicker >= 2024-03-01T00:00:00.000 >>> purchase_order.jsonb->'customFields'->>'datepicker' >='2024-03-01T00:00:00.000' WARN CQL2PgJSON Doing SQL query without index for purchase_order.jsonb->'customFields'->>'datepicker', CQL >>> SQL: customFields.datepicker <= 2024-03-08T23:59:59.999 >>> purchase_order.jsonb->'customFields'->>'datepicker' <='2024-03-08T23:59:59.999' WARN CQL2PgJSON Doing wildcard LIKE search without index for purchase_order.jsonb->'customFields'->>'datepicker', CQL >>> SQL: customFields.datepicker == "Invalid date*" >>> lower(f_unaccent(purchase_order.jsonb->'customFields'->>'datepicker')) LIKE lower(f_unaccent('Invalid date%')) WARN CQL2PgJSON Doing wildcard LIKE search without index for purchase_order.jsonb->'customFields'->>'multiselect', CQL >>> SQL: customFields.multiselect == *opt_0* >>> lower(f_unaccent(purchase_order.jsonb->'customFields'->>'multiselect')) LIKE lower(f_unaccent('%opt\_0%')) WARN CQL2PgJSON Doing wildcard LIKE search without index for purchase_order.jsonb->'customFields'->>'textarea', CQL >>> SQL: customFields.textarea == *ABC* >>> lower(f_unaccent(purchase_order.jsonb->'customFields'->>'textarea')) LIKE lower(f_unaccent('%ABC%')) WARN CQL2PgJSON Doing wildcard LIKE search without index for purchase_order.jsonb->'customFields'->>'textfield', CQL >>> SQL: customFields.textfield == *ABC* >>> lower(f_unaccent(purchase_order.jsonb->'customFields'->>'textfield')) LIKE lower(f_unaccent('%ABC%'))

Required indexes

Each custom field type requires a gin_trgm_ops index to optimize the wildcard LIKE and LIKE searches.

Example:

purchase_order_customfields_singleselect_idx_gin" gin (lower(diku_mod_orders_storage.f_unaccent((jsonb -> 'customFields'::text) ->> 'singleselect'::text)) gin_trgm_ops)

Equivalent to:

"ginIndex": [ { "fieldName": "customFields.singleselect", "caseSensitive": false, "removeAccents": true } ]

The custom field type DATE_PICKER additionally needs a btree index to optimize queries involving >= and <= operators.

Example:

"purchase_order_customfields_datepicker_idx" btree ("left"((jsonb -> 'customFields'::text) ->> 'datepicker'::text, 600))

Equivalent to:

"index": [ { "fieldName": "customFields.datepicker", "caseSensitive": true, "removeAccents": false } ]

Problem

Due to the dynamic nature of the keys in the customFields object, setting up indexes in advance is not possible. The keys are user-generated and can vary. Depending on the custom field type different indexes are required.

Possible solution

  • Implement a index management mechanism to create/remove indexes dynamically when custom fields are created/removed.

  • Possibly triggers on the custom_fields table can be used for implementation.

Environment

None

Potential Workaround

None

Checklist

hide

Activity

Show:
Done

Details

Assignee

Reporter

Priority

Sprint

Development Team

Leipzig

Release

Quesnelia (R1 2024)

TestRail: Cases

Open TestRail: Cases

TestRail: Runs

Open TestRail: Runs

Created March 8, 2024 at 4:08 PM
Updated April 8, 2024 at 8:58 AM
Resolved April 8, 2024 at 8:58 AM
TestRail: Cases
TestRail: Runs

Flag notifications