Re: Performance regressions in PG 9.3 vs PG 9.0 - Mailing list pgsql-performance

From Tom Lane
Subject Re: Performance regressions in PG 9.3 vs PG 9.0
Date
Msg-id 1309.1397007580@sss.pgh.pa.us
Whole thread Raw
In response to Re: Performance regressions in PG 9.3 vs PG 9.0  (uher dslij <codon3@gmail.com>)
List pgsql-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


pgsql-performance by date:

Previous
From: "'Andrew W. Gibbs'"
Date:
Subject: Re: query against large table not using sensible index to find very small amount of data
Next
From: Bruce Momjian
Date:
Subject: Re: Why shared_buffers max is 8GB?