Re: A Better Way? (Multi-Left Join Lookup) - Mailing list pgsql-general

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Tom Lane
> Sent: Friday, July 20, 2012 6:51 PM
> To: David Johnston
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] A Better Way? (Multi-Left Join Lookup)
>
> "David Johnston" <polobo@yahoo.com> writes:
> >> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Surely not.  Neither merge
> >> nor hash joins require an index.  What plan is getting selected?
>
> > I have attached a scrubbed query and explain/analyze.  Let me know if
> > something more is needed.
>
> Well, here's your problem:
>
> >   CTE master_listing {# The LEFT side of the multi-joins #}
> >     ->  Subquery Scan on call  (cost=22762.65..22762.94 rows=1
> > width=32) (actual time=619.158..735.559 rows=8656 loops=1)
>
> The planner thinks master_listing will return only one row, which would
> make a nestloop the right way to do things.  However, with 8500 rows
coming
> out, the nestloop iterates 8500 times and takes forever.
>
> So what you need to do is figure out why that rowcount estimate is so far
off
> and do whatever's needful to make it better.  It does not have to be dead
on
> --- even an estimate of a few dozen rows would likely be enough to
> discourage the planner from using a nestloop.
>
> You haven't shown enough info for anybody else to guess exactly why the
> rowcount estimate is bad, though.
>
>             regards, tom lane
>

OK.

So,

EXPLAIN SELECT function_call(...)  -- yields a planner expectation of 1 row

[Whereas]

EXPLAIN SELECT * FROM function_call(...) -- yields a planner expectation of
"result_rows" which defaults to 1000

The syntax:

SELECT function_call(field_on_another_relation)
    FROM another_relation

Is convenient in order to avoid...

SELECT * FROM function_call(
    (SELECT field_on_another_relation FROM another_relation)
    );

...especially when you need multiple fields from "another_relation"

I guess I get the idea that a function used "inline" is generally going to
return a single result and so the estimate of "1" is most probable.

May I suggest, then, that the CREATE FUNCTION documentation for "ROWS
result_rows" be modified:

Something like:

"The default assumption is 1,000 rows if the function is called in the FROM
clause of a query.  If it is called anywhere else (e.g., the Select List)
the assumption is 1 row regardless of an explicit or default ROWS estimate."

Was this an intentional design decision to override the result_rows estimate
of the function if it is used in the select list?  I get the general
reasoning behind it and do not know enough regarding the internals to make a
judgement but if intentional could it maybe be a little smarter as to when
the override occurs?  Obviously the ideal solution is to implement
LATERAL...

FYI:  I included the following section in the query I provided because I
suspected the function call may have been the issue...

, master_listing AS (

SELECT

    -- identifier fields

    FROM (
        SELECT (func).* FROM (
            SELECT fuction_generating_8500_records(...)
--<<<<< Use of function in Select List; results in the 1-row estimate.
1,000 rows triggers the "MERGE LEFT JOIN" plan
            ) func
            FROM scenario_info
         ) call
    ) master (function_column_rename)

)

Thank You!

David J.