Uploaded image for project: 'FOLIO'
  1. FOLIO
  2. FOLIO-1920

SPIKE: optimize query instance by location

    XMLWordPrintable

    Details

    • Template:
    • Sprint:
      Core: Platform - Sprint 60
    • Story Points:
      5
    • Development Team:
      Core: Platform

      Description

      When query instance by location, following query is generated but the performance is very poor. Query explain analyze shows about 50 seconds. Find a way to improve existing query, or come up a different and better query, or propose other solution like re-organize how instance/holdings data are stored. BTW, the query is generated when users click the Inventory app and select a location filter. Note, I adjusted the ~ to LIKE for the location condition, otherwise, it takes about 8 minutes.

      Note: No implementation in the scope of this ticket.

      WITH headrecords AS 
      (
        SELECT
          jsonb,
          lower(f_unaccent(jsonb ->> 'title')) AS title 
        FROM
          supertenant_mod_inventory_storage.instance_holding_view 
        WHERE
          (
      (((lower(f_unaccent(instance_holding_view.jsonb ->> 'title')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]])).*($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))'))) 
            OR 
            (
              lower(f_unaccent(instance_holding_view.jsonb ->> 'contributors')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]]))"name":([[:punct:]]|[[:space:]])+".*"($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))')) 
            )
      ) 
            OR 
            (
              lower(f_unaccent(instance_holding_view.jsonb ->> 'identifiers')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]]))"value":([[:punct:]]|[[:space:]])+".*"($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))')) 
            )
      ) 
            AND 
            (
              lower(f_unaccent(instance_holding_view.ho_jsonb ->> 'permanentLocationId')) LIKE lower(f_unaccent('53cf956f-c1df-410b-8bea-27f712cca7c0')) 
            )
          )
          AND lower(f_unaccent(jsonb ->> 'title')) < ( 
          SELECT
            lower(f_unaccent(jsonb ->> 'title')) 
          FROM
            supertenant_mod_inventory_storage.instance_holding_view 
          ORDER BY
            lower(f_unaccent(jsonb ->> 'title')) OFFSET 10000 LIMIT 1 ) 
          ORDER BY
            lower(f_unaccent(jsonb ->> 'title')) LIMIT 30 OFFSET 0 
      )
      ,
      allrecords AS 
      (
        SELECT
          jsonb,
          lower(f_unaccent(jsonb ->> 'title')) AS title 
        FROM
          supertenant_mod_inventory_storage.instance_holding_view 
        WHERE
          (
      (((lower(f_unaccent(instance_holding_view.jsonb ->> 'title')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]])).*($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))'))) 
            OR 
            (
              lower(f_unaccent(instance_holding_view.jsonb ->> 'contributors')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]]))"name":([[:punct:]]|[[:space:]])+".*"($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))')) 
            )
      ) 
            OR 
            (
              lower(f_unaccent(instance_holding_view.jsonb ->> 'identifiers')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]]))"value":([[:punct:]]|[[:space:]])+".*"($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))')) 
            )
      ) 
            AND 
            (
              lower(f_unaccent(instance_holding_view.ho_jsonb ->> 'permanentLocationId')) LIKE lower(f_unaccent('53cf956f-c1df-410b-8bea-27f712cca7c0')) 
            )
          )
          AND 
          (
            SELECT
              COUNT(*) 
            FROM
              headrecords 
          )
          < 30 
      )
      SELECT
        jsonb,
        title,
        0 AS count 
      FROM
        headrecords 
      WHERE
        (
          SELECT
            COUNT(*) 
          FROM
            headrecords 
        )
        >= 30 
      UNION
      ( 
      SELECT
        jsonb, title, 
        (
          SELECT
            COUNT(*) 
          FROM
            allrecords 
        )
        AS count 
      FROM
        allrecords 
      ORDER BY
        title LIMIT 30 OFFSET 0 ) 
      ORDER BY
        title;
      

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                nassar Nassib Nassar
                Reporter:
                hji Hongwei Ji
                Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases