Message-ID: <5474ED7C.9030403@vmware.com>
Date: Tue, 25 Nov 2014 22:58:36 +0200
From: Heikki Linnakangas <hlinnakangas@vmware.com>
Organization: VMware
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Icedove/31.2.0
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>, pgsql-performance
 <pgsql-performance@postgresql.org>
Subject: Re: Small performance regression in 9.2 has a big impact
References: 
 <CAOR=d=3j1U_q-zf8+jUx1hkx8ps+N8pm=EUTqyFdJ5ov=+fawg@mail.gmail.com>
In-Reply-To: 
 <CAOR=d=3j1U_q-zf8+jUx1hkx8ps+N8pm=EUTqyFdJ5ov=+fawg@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance