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 <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
