Re: Perfomance bug in v10

On Fri, Jun 2, 2017 at 10:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Teodor Sigaev <teodor@sigaev.ru> writes:
>>> Teodor, could you check if this patch fixes your real-world problem?
>
>> It works fine with original query, thank you. But some other query slowdowns for
>> ~10% (9 secs vs 10 secs). Look at following part of plans of huge query:
>> ...
>> As you said, it removes Materialize node, although it's useful here.
>
> Meh.  If it's expecting only one outer row, it shouldn't be using a
> Materialize on the inner side, period.  That's not sane per the cost
> model.  You haven't shown us enough to guess why the rowcount estimates
> are so far off reality in this query, but I don't think it's the fault
> of this patch if the result is slightly slower given that much error.
>
> Most likely, the answer for your real-world problem is "you need to
> ANALYZE before running the query".
>
>                         regards, tom lane

I don't know. Perhaps the risky part is assuming rows=1 for non-unique
inner scans. In fact a wrongly estimated rows=1 outer scan would be
just as risky.

There were old threads about considering a risk factor when estimating
plans, and I'm thinking this issue is the planner failing to do
exactly that.