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
Date: Tue, 08 Apr 2014 21:39:40 -0400
Message-ID: <1309.1397007580@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, codon3@gmail.com
Subject: Re: Performance regressions in PG 9.3 vs PG 9.0
In-Reply-To: <1309.1397007580@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox