Re: Subquery in a JOIN not getting restricted?

Jay Levitt <jay(dot)levitt(at)gmail(dot)com> writes:
> When I run the following query:
> select questions.id
> from questions
> join (
> select u.id as user_id
> from users as u
> left join scores as s
> on s.user_id = u.id
> ) as subquery
> on subquery.user_id = questions.user_id;

> the subquery is scanning my entire user table, even though it's restricted
> by the outer query. (My real subquery is much more complicated, of course,
> but this is the minimal fail case.)

> Is this just not a thing the optimizer can do?

Every release since 8.2 has been able to reorder joins in a query
written that way. Probably it just thinks it's cheaper than the
alternatives.

(Unless you've reduced the collapse_limit variables for some reason?)

regards, tom lane