Re: stuck on values in 8.2

"Tom Allison" <tom(at)tacocat(dot)net> writes:

> 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;

You might find it easier to follow your SQL queries if you formatted them to
make their structure clear:

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;

Note that your query is joining 6 tables and there are two joins that don't
have any join constraint on them. So you're getting the cartesian product of
those joins. Postgres estimates your query will return 216 million records.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com