Re: Small performance regression in 9.2 has a big impact

On Tue, Nov 25, 2014 at 1:58 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> On 11/25/2014 10:36 PM, Scott Marlowe wrote:
>>
>> OK so there's a simple set of tree functions we use at work. They're
>> quite fast in 8.4 and they've gotten about 40% slower in 9.2. They're
>> a simple mix of sql and plpgsql functions which are at
>> http://pastebin.com/SXTnNhd5 and which I've attached.
>>
>> Here's a test query:
>>
>> select tree_ancestor_keys('000000000000000100000001');
>>
>> According to explain analyze on both 8.4 and 9.2 they have the same
>> plan. However, on the same machine the query is about 40% slower on
>> 9.2. Note we're not hitting the disks, or even buffers here. It's pure
>> in memory plpsql and sql that we're running.
>>
>> explain analyze select tree_ancestor_keys('000000000000000100000001')
>> from generate_series(1,1000);
>>
>> On 8.4 runs in about 280 to 300 ms. (you can run it once and get the
>> same diff, it's just easier to see with the generate series forcing it
>> to run 1000 times to kind of even out the noise.)
>>
>> On 9.2, same machine, clean fresh dbs etc, it runs in ~400 ms. And
>> that difference seems to be there on all plpgsql and sql functions.
>>
>> In our application, these tree functions get called millions and
>> millions of times a day, and a 40% performance penalty is a pretty big
>> deal.
>>
>> We're already using the trick of telling the query planner that this
>> function will return 1 row with alter function rows 1 etc. That helps
>> a lot but it doesn't fix this underlying performance issue.
>>
>> Server versions are 8.4.22 (last I think) and 9.2.9.
>>
>> If anyone has any suggestions I'd love to hear them.
>
>
> I don't know why this regressed between those versions, but looking at the
> functions, there's some low-hanging fruit:
>
> 1. tree_ancestor_keys() could use UNION ALL instead of UNION. (I believe
> duplicates are expected here, although I'm not 100% sure).
>
> 2. tree_ancestor_keys() calculates tree_level($1) every time it recurses.
> Would be cheaper to calculate once, and pass it as argument.
>
> Put together:
>
> CREATE FUNCTION tree_ancestor_keys(bit varying, integer, integer) RETURNS
> SETOF bit varying
>     LANGUAGE sql IMMUTABLE STRICT
>     AS $_$
>   select tree_ancestor_key($1, $2)
>   union all
>   select tree_ancestor_keys($1, $2 + 1, $3)
>   where $2 < $3
> $_$;
>
> CREATE or replace FUNCTION tree_ancestor_keys(bit varying, integer) RETURNS
> SETOF bit varying
>     LANGUAGE sql IMMUTABLE STRICT
>     AS $_$
>   select tree_ancestor_keys($1, $2 + 1, tree_level($1))
> $_$;
>
> These changes make your test query go about 2x faster on my laptop, with git
> master. I'm sure you could optimize the functions further, but those at
> least seem like fairly safe and simple changes.

Wow that made a huge difference. About a 50% increase across the
board. Sadly, 9.2 is still way behind 8.4 (see Tom's email)

Here's the results of 10 runs, 9.2 old functions, 9.2 new functions,
8.4 old functions, 8.4 new functions:

402.454 ms 217.718 ms 283.289 ms 167.108 ms
390.828 ms 219.644 ms 282.994 ms 165.524 ms
397.987 ms 216.864 ms 289.053 ms 170.821 ms
391.670 ms 220.943 ms 296.410 ms 164.190 ms
437.099 ms 233.360 ms 284.279 ms 183.919 ms
473.945 ms 291.199 ms 347.916 ms 168.300 ms
453.974 ms 231.350 ms 367.517 ms 158.717 ms
377.221 ms 226.697 ms 297.255 ms 164.196 ms
396.812 ms 262.638 ms 300.073 ms 161.325 ms
436.822 ms 227.489 ms 292.553 ms 179.194 ms
405.929 ms 233.461 ms 267.355 ms 162.847 ms

so about 400 versus about 220, and about 290 versus about 165 or so.