patron search by user name can be slow

[Galen Charlton]

Searching for patrons by username can be slow in large databases. Here is query plan of such a search:

 explain analyze SELECT evergreen.lowercase(CAST(users.family_name AS text)), evergreen.lowercase(CAST(users.first_given_name AS text)), evergreen.lowercase(CAST(users.second_given_name AS text)), evergreen.lowercase(CAST(users.dob AS text)), evergreen.lowercase(CAST(users.id AS text))
      FROM actor.usr AS users
     JOIN actor.org_unit_descendants(1) d ON (d.id = users.home_ou)
     JOIN (SELECT id as id FROM actor.usr u WHERE evergreen.lowercase(CAST(profile AS text)) ~ '^' AND evergreen.lowercase(CAST(usrname AS text)) ~ '^admin') AS search ON (search.id = users.id)
      WHERE users.deleted = FALSE
     AND users.active = TRUE
      GROUP BY evergreen.lowercase(CAST(users.family_name AS text)), evergreen.lowercase(CAST(users.first_given_name AS text)), evergreen.lowercase(CAST(users.second_given_name AS text)), evergreen.lowercase(CAST(users.dob AS text)), evergreen.lowercase(CAST(users.id AS text))
      ORDER BY evergreen.lowercase(CAST(users.family_name AS text)) ASC, evergreen.lowercase(CAST(users.first_given_name AS text)) ASC, evergreen.lowercase(CAST(users.second_given_name AS text)) ASC, evergreen.lowercase(CAST(users.dob AS text)) DESC, evergreen.lowercase(CAST(users.id AS text))
      LIMIT 101 ;

 Limit (cost=24390.09..24462.76 rows=57 width=30) (actual time=20160.035..20160.049 rows=2 loops=1)
   -> Group (cost=24390.09..24462.76 rows=57 width=30) (actual time=20160.028..20160.036 rows=2 loops=1)
         -> Sort (cost=24390.09..24390.23 rows=57 width=30) (actual time=20160.021..20160.023 rows=2 loops=1)
               Sort Key: (lowercase(users.family_name)), (lowercase(users.first_given_name)), (lowercase(users.second_given_name)), (lowercase((users.dob)::text)), (lowercase((users.id)::text))
               Sort Method: quicksort Memory: 25kB
               -> Nested Loop (cost=162.64..24388.43 rows=57 width=30) (actual time=627.302..20159.982 rows=2 loops=1)
                     -> Nested Loop (cost=162.64..13975.83 rows=11374 width=30) (actual time=3.667..4574.770 rows=807990 loops=1)
                           -> Function Scan on org_unit_descendants d (cost=0.25..0.26 rows=1 width=4) (actual time=3.241..3.479 rows=96 loops=1)
                           -> Bitmap Heap Scan on usr users (cost=162.39..13830.16 rows=11633 width=34) (actual time=2.327..26.977 rows=8417 loops=96)
                                 Recheck Cond: (home_ou = d.id)
                                 Filter: ((NOT deleted) AND active)
                                 -> Bitmap Index Scan on actor_usr_home_ou_idx (cost=0.00..159.55 rows=11633 width=0) (actual time=1.631..1.631 rows=9265 loops=96)
                                       Index Cond: (home_ou = d.id)
                     -> Index Scan using usr_pkey on usr u (cost=0.00..0.90 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=807990)
                           Index Cond: (id = users.id)
                           Filter: ((lowercase(usrname) ~ '^admin'::text) AND (lowercase((profile)::text) ~ '^'::text))
 Total runtime: 20160.282 ms

Evergreen 2.4

[Galen Charlton (gmc)]

A patch is available in the user/gmcharlt/lp1200735_usrname_index branch in the working/Evergreen repository:

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/gmcharlt/lp1200735_usrname_index

[Bill Erickson (berick)]

I have borne witness to the painfully slow searches. Testing...

[Bill Erickson (berick)]

Tested and pushed from 2.3 forward.