From: Tom Lane <tgl@sss.pgh.pa.us>
To: uher dslij <codon3@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Performance regressions in PG 9.3 vs PG 9.0
In-reply-to: 
 <CAKGDDeuX1uzQgR-g5cYzJsnj9nfrd=F7=PXq4yyF=tANKmTYXw@mail.gmail.com>
References: 
 <CAKGDDes8jznFSneY8dmHAs9my0J3JtMuksuLFvnCSGkMc+eX+Q@mail.gmail.com>
 <CAKGDDevyYqASsJBQ8poQP9WturteSBTzPp8PHZRbc+3feLOegA@mail.gmail.com>
 <28394.1396992397@sss.pgh.pa.us>
 <CAKGDDeuX1uzQgR-g5cYzJsnj9nfrd=F7=PXq4yyF=tANKmTYXw@mail.gmail.com>
Comments: In-reply-to uher dslij <codon3@gmail.com>
 message dated "Tue, 08 Apr 2014 20:23:21 -0400"
Date: Tue, 08 Apr 2014 21:39:40 -0400
Message-ID: <1309.1397007580@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

uher dslij <codon3@gmail.com> writes:
> Thanks for your reply Tom.  I've found that the culprit is the function
> parentContainers(), which recurses up a folder structure and looks like
> this:

Hmm ... I had noticed the execution of that in a subplan, but it appeared
that the subplan was being done the same number of times and took about
the same amount of time in both 9.0 and 9.3, so I'd discounted it as the
source of trouble.  Still, it's hard to argue with experimental evidence.

> create function parentContainers(numeric) returns setof numeric
> as '
>     select parentContainers( (select container_id from container where id =
> $1 ) )
> union
>     select id from container where id = $1
> ' language sql stable returns null on null input;

Yeah, that looks like performance trouble waiting to happen --- it's not
clear what would bound the recursion, for one thing.  Have you considered
replacing this with a RECURSIVE UNION construct?  Wasn't there in 9.0
of course, but 9.3 can do that.

			regards, tom lane


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