Re: Strange query plan invloving a view

Tom Lane wrote:
> Rich Doughty <rich@opusvl.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