Message-ID: <43831D39.4030407@opusvl.com>
Date: Tue, 22 Nov 2005 13:29:29 +0000
From: Rich Doughty <rich@opusvl.com>
Organization: Opus VL
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pgsql-Performance <pgsql-performance@postgresql.org>
Subject: Re: Strange query plan invloving a view
References: <437BD7A2.608@opusvl.com> <29531.1132250815@sss.pgh.pa.us>
In-Reply-To: <29531.1132250815@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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