| 2013-07-12 19:12:54 |
Galen Charlton |
description |
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)) ~ '^esi-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) ~ '^esi-admin'::text) AND (lowercase((profile)::text) ~ '^'::text))
Total runtime: 20160.282 ms
Evergreen 2.4 |
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 |
|
