possible optimization for evergreen.ranked_volumes database function
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Evergreen |
Fix Released
|
Medium
|
Unassigned |
Bug Description
From depesz's email to the developer list:
my name is Hubert Lubaczewski (a.k.a. depesz). I'm PostgreSQL DBA from
Poland, that was hired to do review for Evergreen installations, and
suggest changes in database "things" (queries, structure).
For the client I analyzed logs from production Pg instance for ~ 10
days.
During this time, the single most time consuming query (summarized time
for all instances of the query, with different parameters) was:
SELECT * FROM unapi.bre ( ... ) AS "unapi.bre";
I profiled this function, and found that in my test case most of the
time (2.04s out of 2.06s, so ~ 99%) was spent in call to
unapi.holdings_
When I profiled this function, I found that most of the time (sorry,
don't have the number now with me) was spent in call to
evergreen.
At this moment in my research something changed on the server I was
testing on, and all subsequent times were ~ 4-5 times lower, but the
ratios were more or less the same.
Anyway - call to evergreen.
full caches/buffers) of ~ 380ms.
I modified the function by:
1. inlining actor.org_
2. inlining evergreen.
3. extracting depth calculation to separate call
4. switched to plpgsql (which gives me ability to use variables)
5. removed evergreen.rank_ou() and evergreen.
select clause - these are still in WINDOW definition, but they
weren't used in the SELECT, so it's better to remove from there.
6. in passing renamed arguments to avoid name clash (argument depth vs.
field depth)
7. in passing changed usage of $* to access parameters to using named
parameters, for readability.
New function did the same work in ~ 18ms.
Now - after I finished my tests, I was unfortunately not able to repeat
slow performance of unapi.bre(), but I think that the optimization of
evergreen.
If you'll agree, I would appreciate modifying the function in project.
If I should do/provide something else, please let me know.
Best regards,
depesz
NOTE: I (Jason Stephenson) have depesz's permission to create a git branch of his function changes with his name and email as the author. We both think this will get more attention if there is a branch and an associated Launchpad bug.
description: | updated |
Changed in evergreen: | |
status: | New → Triaged |
importance: | Undecided → Medium |
milestone: | none → 2.next |
Changed in evergreen: | |
milestone: | 2.6.0-beta1 → 2.6.0-rc1 |
tags: | added: signedoff |
Changed in evergreen: | |
milestone: | 2.6.0-rc1 → 2.next |
Changed in evergreen: | |
status: | Fix Committed → Fix Released |
http:// git.evergreen- ils.org/ ?p=working/ Evergreen. git;a=shortlog; h=refs/ heads/user/ dyrcona/ lp1234845_ ranked_ volumes
I'll add a pullrequest tag when I've had a chance to look at the above and signed off on it, myself.