Re: Small performance regression in 9.2 has a big impact

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.

- Heikki