Sorry for the premature send on that last email. Here is the full one:
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@xxxxxxxxxxxxx> wrote:
uher dslij <codon3@xxxxxxxxx> writes:I don't see any reason to think this is a planner regression. The
> 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?
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
