Re: Subquery in a JOIN not getting restricted?

On Wed, Nov 9, 2011 at 9:15 AM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Jay Levitt <jay(dot)levitt(at)gmail(dot)com> wrote:
>
>> I don't get why the GROUP BY in this subquery forces it to scan
>> the entire users table (seq scan here, index scan on a larger
>> table) when there's only one row in users that can match:
>
>> explain analyze
>> select questions.id
>> from questions
>> join (
>>    select u.id
>>    from users as u
>>    group by u.id
>> ) as s
>> on s.id = questions.user_id
>> where questions.id = 1;
>
>>   Total runtime: 1.262 ms
>
> Are you sure there's a plan significantly faster than 1.3 ms?

Well, this may not fit the OP's 'real' query, but the inner subquery
is probably better written as a semi-join (WHERE EXISTS).

merlin