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.
--
To understand recursion, one must first understand recursion.