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@tacocat.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/