Re: stuck on values in 8.2

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" <tom(at)tacocat(dot)net> 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/