Re: Performance regressions in PG 9.3 vs PG 9.0

Sorry for the premature send on that last email. Here is the full one:

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:

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;

It is declared stable, but I know that STABLE is just planner hint, so it
doesn't guarantee that it will only get called once. If I replace the
function call with the two values this function returns, I get < 1 ms
runtime on all versions of pg. So there is data to support the statement
that we were relying on planner luck before and that luck has run out.

What is the best practice to ensure a stable function only gets called
once? Should I use a CTE to cache the result? Is there a better way?

Thanks in advance,

On Tue, Apr 8, 2014 at 5:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> uher dslij <codon3(at)gmail(dot)com> writes:
> > The EXPLAINs all pretty much look like my original post. The planner in
> > 9.2 and above is simply not using bitmap heap scans or bitmap index
> scans?
> > What could be the reason for this?
>
> I don't see any reason to think this is a planner regression. The
> rowcount estimates are pretty far off in both versions; so it's just a
> matter of luck that 9.0 is choosing a better join order than 9.3.
>
> I'd try cranking up the statistics targets for the join columns
> (particularly domain_id) and see if that leads to better estimates.
>
> regards, tom lane
>