Uploaded image for project: 'mod-users'
  1. mod-users
  2. MODUSERS-338

Keyword search in users app is very slow

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • P3
    • Resolution: Done
    • None
    • None
    • Prokopovych - Sprint 158
    • Prokopovych
    • Orchid (R1 2023)
    • !!!ALL!!!
    • TBD
    • Morning Glory (R2 2022)

    Description

      Overview:

      In Pontifica Universidad Javeriana - Biblioteca Central when we are performing search using keyword in users app the response time is 15-20 secs as number of users are 284378 and there are 11 custom fields ** 

      The issue is temporarily fixed by creating index on "customFields" field in users table in mod-users.

      Additional Information:
      Search query  used is:

      SELECT jsonb,id FROM fsxxxxXXXX_mod_users.users WHERE ((((((((get_tsvector(f_unaccent(users.jsonb->>'username')) @@ tsquery_phrase(f_unaccent('xyz*'))) OR (get_tsvector(f_unaccent(users.jsonb->'personal'>>'firstName')) @@ tsquery_phrase(f_unaccent('xyz*')))) OR (get_tsvector(f_unaccent(users.jsonb>'personal'>>'preferredFirstName')) @@ tsquery_phrase(f_unaccent('xyz*')))) OR (get_tsvector(f_unaccent(users.jsonb>'personal'>>'lastName')) @@ tsquery_phrase(f_unaccent('xyz*')))) OR (get_tsvector(f_unaccent(users.jsonb>'personal'>>'email')) @@ tsquery_phrase(f_unaccent('xyz*')))) OR (get_tsvector(f_unaccent(users.jsonb>>'barcode')) @@ tsquery_phrase(f_unaccent('xyz*')))) OR (false /* id == invalid UUID /)) OR (get_tsvector(f_unaccent(users.jsonb->>'externalSystemId')) @@ tsquery_phrase(f_unaccent('xyz')))) OR (get_tsvector(f_unaccent(users.jsonb->>'customFields')) @@ tsquery_phrase(f_unaccent('xyz*'))) ORDER BY left(lower(f_unaccent(users.jsonb->'personal'>>'lastName')),600), lower(f_unaccent(users.jsonb>'personal'>>'lastName')), left(lower(f_unaccent(users.jsonb>'personal'>>'firstName')),600), lower(f_unaccent(users.jsonb>'personal'->>'firstName')) LIMIT 100 OFFSET 0

      TestRail: Results

        Attachments

          Issue Links

            Activity

              People

                felkerk Kyle Felker
                Sri Sri Ranjan
                Votes:
                0 Vote for this issue
                Watchers:
                10 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases