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;
QUERY PLAN
------------------------------------------------------------------------
--------------------------------------
Nested Loop Left Join (cost=423.70..4327392.26 rows=216168368 width=48)
-> Nested Loop (cost=250.49..3851.69 rows=30472 width=44)
-> Hash Left Join (cost=249.40..3241.16 rows=30472 width=40)
Hash Cond: (t.token_idx = u1.token_idx)
-> Nested Loop (cost=0.00..967.34 rows=29908 width=40)
-> Nested Loop Left Join (cost=0.00..33.18
rows=4 width=32)
-> Values Scan on
"*VALUES*" (cost=0.00..0.05 rows=4 width=32)
-> Index Scan using tokens_token_key on
tokens (cost=0.00..8.27 rows=1 width=16)
Index Cond: ("*VALUES*".column1 =
(tokens.token)::text)
-> Seq Scan on tokens t (cost=0.00..158.77
rows=7477 width=8)
-> Hash (cost=145.29..145.29 rows=8329 width=8)
-> Seq Scan on user_token u1
(cost=0.00..145.29 rows=8329 width=8)
-> Materialize (cost=1.09..1.10 rows=1 width=4)
-> Seq Scan on users u (cost=0.00..1.09 rows=1
width=4)
Filter: (user_idx = 15)
-> Materialize (cost=173.21..244.15 rows=7094 width=12)
-> Seq Scan on user_token u2 (cost=0.00..166.11 rows=7094
width=12)
Filter: (user_idx = 15)
(18 rows)
On May 12, 2007, at 11:08 AM, Gregory Stark wrote:
"Tom Allison" <[EMAIL PROTECTED]> writes:
OK, after reviewing many emails and what I was trying to do I
upgraded from 8.2.
Seems to work as it did in 8.1 which is a good start.
I'm doing all of this so I can use the 'values' that was
described as being
something like:
select * from (values ('one','two','three')) "foo";
SELECT * FROM (VALUES ('one'),('two'),('three')) AS foo(value)
I initially thought that I could do this with:
select t.value, v.value from
values('one','two','three') left outer join mytable using (value)
postgres=# SELECT *
FROM (VALUES ('one'),('two'),('three')) AS foo(value)
LEFT OUTER JOIN mytable ON (foo.value = mytable.value);
value | value
-------+-------
one |
two | two
three | three
(3 rows)
"USING" would work too but then you only get one output column
rather than two
which is not so helpful in this case.
postgres=# SELECT *
FROM (VALUES ('one'),('two'),('three')) AS foo(value)
LEFT OUTER JOIN mytable USING (value) ;
value
-------
one
two
three
(3 rows)
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---------------------------(end of
broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
