To: Jay Levitt <jay.levitt@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Subquery in a JOIN not getting restricted? 
In-reply-to: <4EB84CB4.2010102@gmail.com> 
References: <4EB84CB4.2010102@gmail.com>
Comments: In-reply-to Jay Levitt <jay.levitt@gmail.com>
	message dated "Mon, 07 Nov 2011 16:25:08 -0500"
Date: Mon, 07 Nov 2011 16:41:18 -0500
Message-ID: <3429.1320702078@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Jay Levitt <jay.levitt@gmail.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