Performance issue - Search of item_notes without index on item_id

Description

Observe significant performance issues in mod-remote-storage and underlying Postgres DB (cpu utilization) when the following query is executed by the module (in environment with 700K notes)

select item_id,note,note_type,staff_only from item_notes where item_id='itemid'

Issue is resolved by adding missing index as a workaround

CREATE INDEX IF NOT EXISTS item_notes_item_id_idx
ON tenant_mod_remote_storage.item_notes USING btree
(item_id ASC NULLS LAST)
TABLESPACE pg_default;

Please add index or revise query for performance

This issue is observed in a quenelia sp5 environment (mod-remote-storage-3.2.0)

CSP Request Details

None

CSP Rejection Details

None

Potential Workaround

None

Checklist

hide

Activity

Show:

Details

Assignee

Reporter

Labels

Priority

Development Team

Volaris

Release

Trillium (R2 2025)

RCA Group

TBD

TestRail: Cases

Open TestRail: Cases

TestRail: Runs

Open TestRail: Runs
Created January 8, 2025 at 4:55 PM
Updated March 14, 2025 at 12:13 PM
TestRail: Cases
TestRail: Runs

Flag notifications