From: Rich Doughty <rich@opusvl.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pgsql-Performance <pgsql-performance@postgresql.org>
Subject: Re: Strange query plan invloving a view
Date: Tue, 22 Nov 2005 13:29:29 +0000
Message-ID: <43831D39.4030407@opusvl.com> (raw)
In-Reply-To: <29531.1132250815@sss.pgh.pa.us>
References: <437BD7A2.608@opusvl.com>
<29531.1132250815@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rich@opusvl.com, tgl@sss.pgh.pa.us
Subject: Re: Strange query plan invloving a view
In-Reply-To: <43831D39.4030407@opusvl.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox