Uploaded image for project: 'mod-inventory-storage'
  1. mod-inventory-storage
  2. MODINVSTOR-884

Query does not use instance_hrid_idx_unique index

    XMLWordPrintable

Details

    • Spitfire
    • Lotus (R1 2022) Bug Fix
    • Implementation coding issue

    Description

      Overview: The following query does not use the existing instance_hrid_idx_unique index.

      SELECT jsonb,id FROM fs09000000_mod_inventory_storage.instance WHERE get_tsvector(f_unaccent(instance.jsonb->>‘hrid’)) @@ tsquery_phrase(f_unaccent(‘in00002129525’)) LIMIT 10 OFFSET 0

      Without the index being applied it's very slow and often ends with timeout exception on DB side.  In a 8M records instance table, it takes 3 minutes to return result. It seems like the CQL query string may need the "==" sign to search for hrid's by using the existing instance-hrid unique index.  If this change in the code is not possible, a FT index may need to be created to make the query's execution speed acceptable.  But this FT index takes about 800ms+ to execute, which is about 8x slower than if it was using the unique index.   Therefore, update the code to use the unique hrid index.

      TestRail: Results

        Attachments

          Issue Links

            Activity

              People

                Unassigned Unassigned
                Roman Fedynyshyn Roman Fedynyshyn
                Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases