I agree I'm going in the wrong direction.
in a Venn sort of way, what I'm trying to do is:
values(...) --> left outer --> tokens --> left outer --> (user_tokens
where user_tokens.user_idx = users.user_idx and users.user_idx = 4)
To give me a list of
all values || any matching token || any matching user_token where
user_idx = 4
something like:
SELECT values.token, t.token_idx, ut.token_idx
FROM
(values('one'),('want'),('examine'),('three')) as values(token)
left outer join tokens t using (token)
left outer join ( select token_idx from user_token where
user_idx = 14) "ut"
using (token_idx)
;
That seems to be better.
I think the part I was trying to get my brain around was how, in
postgres, do you do multiple outer joins.
On my day job I do this in Oracle without thinking, but the syntax of
postgres is new to me.
Like doing dates. Everyone has a different way of doing dates and
they are all weird.
Now I have to go impliment it into my code and see what it actually
does.
I'm hoping to peel 3-5 seconds off each process!
On May 12, 2007, at 12:06 PM, Richard Broersma Jr wrote:
>
> --- Tom Allison <tom@tacocat.net> wrote:
>
>> This is getting really ugly...
>> it won't finish in less than .. minutes.
>>
>> spam=> explain select u2.token_idx, t.token_idx, foo.token from
>> tokens t left outer join user_token u1 using (token_idx),
>> users u left outer join user_token u2 using (user_idx),
>> (values('one'),('want'),('examine'),('three')) as foo(token)
>> left outer join tokens using (token)
>> where u.user_idx = 15;
>
> It looks to me that your query has (3) left joins and (3) implied
> cross-joins. Perhaps reforming
> your query to eliminate the cross-joins with help performance. In
> regard to your tables <tokens>
> and <user_tokens>, in this query you are referring to (2) separate
> instances of these tables when
> a single instance these tables would probably work just fine. i.e.
>
> tokens t vs. tokens,
> user_token u1 vs user_token u2
>
> Regards,
> Richard Broersma Jr.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend