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.
Attachment:
z.sql
Description: application/sql
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
