Re: A Better Way? (Multi-Left Join Lookup)

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Tom Lane
> Sent: Saturday, July 21, 2012 5:48 PM
> To: David Johnston
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] A Better Way? (Multi-Left Join Lookup)
>
> I wrote:
> > "David Johnston" <polobo(at)yahoo(dot)com> writes:
> >> 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
>
> > Hm ...
>
> >> Was this an intentional design decision to override the result_rows
> >> estimate of the function if it is used in the select list?
>
> > Not so much an intentional decision as just that nobody ever did
> > anything about it.
>
> I've now done something about that for 9.2. I'm loath to back-patch it
into
> any already-stable releases, though, for fear of destabilizing plan
choices that
> production applications might be relying on.
>
> regards, tom lane
>

Understood and agree. It isn't like the proper estimates cannot be gotten -
it just is less than syntactically beautiful to do so.

Maybe a documentation patch instead of a code patch would be in order to at
least give people of chance to learn about the inconsistent behavior before
it bites them in 8.3 to 9.1? For me personally I read and learned about the
function row estimate property and didn't make the connection between the
fact I knew I was using the default of 1000 and the planner was telling me
it was only using 1.

Thank you for your responsiveness on this.

David J.