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

Add indexes to improve performance

    XMLWordPrintable

    Details

    • Template:
    • Sprint:
      CP: sprint 73
    • Story Points:
      0.5
    • Development Team:
      Core: Platform

      Description

      During BugFest q3.2-2019 testing, noticed below SQL queries are slow. Adding indexes can help (see the timing number before and after).

      create index on fs00001000_mod_users.users using gin (to_tsvector('simple', f_unaccent(jsonb ->> 'patronGroup')));
      SELECT COUNT(*) FROM fs00001000_mod_users.users WHERE to_tsvector('simple', f_unaccent(users.jsonb->>'patronGroup')) @@ to_tsquery('simple', f_unaccent('ad0bc554-d5bc-463c-85d1-5562127ae91b'));
      319.702 ms vs 0.110 ms
      
      create index on fs00001000_mod_users.users using gin (to_tsvector('simple', f_unaccent(jsonb ->> 'barcode')));
      SELECT COUNT(*) FROM fs00001000_mod_users.users WHERE to_tsvector('simple', f_unaccent(users.jsonb->>'barcode')) @@ to_tsquery('simple', f_unaccent('299308637069604'));
      129.645 ms vs 0.035 ms
      
      create index on fs00001000_mod_users.users using gin (to_tsvector('simple', f_unaccent(jsonb ->> 'username')));
      SELECT COUNT(*) FROM fs00001000_mod_users.users  WHERE to_tsvector('simple', f_unaccent(users.jsonb->>'username')) @@ to_tsquery('simple', f_unaccent('JMPajerek'))
      109.786 ms vs 
      
      create index on fs00001000_mod_users.users (lower(f_unaccent(jsonb -> 'personal' ->> 'firstName')));
      create index on fs00001000_mod_users.users (lower(f_unaccent(jsonb -> 'personal' ->> 'lastName')));
      SELECT * FROM fs00001000_mod_users.users  WHERE to_tsvector('simple', f_unaccent(users.jsonb->>'active')) @@ to_tsquery('simple', f_unaccent('true')) ORDER BY lower(f_unaccent(users.jsonb->'personal'->>'lastName')), lower(f_unaccent(users.jsonb->'personal'->>'firstName')) LIMIT 30 OFFSET 30;
      155.960 ms vs 
      
      create index on fs00001000_mod_users.users using gin (to_tsvector('simple', f_unaccent(jsonb -> 'personal' ->> 'firstName')));
      create index on fs00001000_mod_users.users using gin (to_tsvector('simple', f_unaccent(jsonb -> 'personal' ->> 'lastName')));
      create index on fs00001000_mod_users.users using gin (to_tsvector('simple', f_unaccent(jsonb -> 'personal' ->> 'email')));
      create index on fs00001000_mod_users.users using gin (to_tsvector('simple', f_unaccent(jsonb ->> 'externalSystemId')));
      create index on fs00001000_mod_users.users using gin (to_tsvector('simple', f_unaccent(jsonb ->> 'active')));
      SELECT COUNT(*) FROM fs00001000_mod_users.users  WHERE ((((((to_tsvector('simple', f_unaccent(users.jsonb->>'username')) @@ to_tsquery('simple', f_unaccent('emelianov:*'))) OR (to_tsvector('simple', f_unaccent(users.jsonb->'personal'->>'firstName')) @@ to_tsquery('simple', f_unaccent('emelianov:*')))) OR (to_tsvector('simple', f_unaccent(users.jsonb->'personal'->>'lastName')) @@ to_tsquery('simple', f_unaccent('emelianov:*')))) OR (to_tsvector('simple', f_unaccent(users.jsonb->'personal'->>'email')) @@ to_tsquery('simple', f_unaccent('emelianov:*')))) OR (to_tsvector('simple', f_unaccent(users.jsonb->>'barcode')) @@ to_tsquery('simple', f_unaccent('emelianov:*')))) OR (false /* id == invalid UUID */)) OR (to_tsvector('simple', f_unaccent(users.jsonb->>'externalSystemId')) @@ to_tsquery('simple', f_unaccent('emelianov:*')));
      910.729 ms vs 0.107 ms
      

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                hji Hongwei Ji
                Reporter:
                hji Hongwei Ji
                Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases