Re: performance regression in 9.2 CTE with SRF function

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: performance regression in 9.2 CTE with SRF function
Date: 2013-02-11 01:17:32
Message-ID: 18040.1360545452@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> In Czech discussion group was reported performance regression of CTE
> query. I wrote a test, when I can show it.

I don't see anything tremendously wrong here. The older branches are
choosing the right plan for entirely wrong reasons: they don't notice
that "select foo(a) from pl" has a set-returning function in the
targetlist and so don't adjust the estimated number of result rows
for that. In this particular example, foo() seems to return an average
of about 11 rows per call, versus the default estimate of 1000 rows per
call, so the size of the result is overestimated and that dissuades
the planner from using a hashed subplan. But the error could easily
have gone the other way, causing the planner to use a hashed subplan
when it shouldn't, and the consequences of that are even worse. So
I don't think that ignoring SRFs in the targetlist is better.

If you add "ROWS 10" or so to the declaration of the function, you
get a better row estimate and it goes back to the hashed subplan.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuo Ishii 2013-02-11 03:40:31 Improving pgbench to log index creation time etc.
Previous Message Tom Lane 2013-02-10 23:47:30 Re: BUG #7493: Postmaster messages unreadable in a Windows console