Re: Strange query plan invloving a view

Tom Lane wrote:
> Rich Doughty <rich(at)opusvl(dot)com> writes:
>
>>However, the following query (which i believe should be equivalent)
>
>
>>SELECT *
>>FROM
>> tokens.ta_tokenhist h INNER JOIN
>> tokens.ta_tokens t ON h.token_id = t.token_id LEFT JOIN
>> tokens.ta_tokenhist i ON t.token_id = i.token_id AND
>> i.status = 'issued' LEFT JOIN
>> tokens.ta_tokenhist s ON t.token_id = s.token_id AND
>> s.status = 'sold' LEFT JOIN
>> tokens.ta_tokenhist r ON t.token_id = r.token_id AND
>> r.status = 'redeemed'
>>WHERE
>> h.sarreport_id = 9
>>;
>
>
> No, that's not equivalent at all, because the implicit parenthesization
> is left-to-right; therefore you've injected the constraint to a few rows
> of ta_tokenhist (and therefore only a few rows of ta_tokens) into the
> bottom of the LEFT JOIN stack. In the other case the constraint is at
> the wrong end of the join stack, and so the full view output gets formed
> before anything gets thrown away.
>
> Some day the Postgres planner will probably be smart enough to rearrange
> the join order despite the presence of outer joins ... but today is not
> that day.

thanks for the reply.

is there any way i can achieve what i need to by using views, or should i
just use a normal query? i'd prefer to use a view but i just can't get round
the performance hit.

--

- Rich Doughty