Re: Subquery in a JOIN not getting restricted?

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

That said, there might be some room for an optimization which pushes
that test into the query with the "group by" clause.  I don't know
if there's a problem with that which I'm missing, the construct was
judged to be too rare to be worth the cost of testing for it, or
it's just that nobody has yet gotten to it.

-Kevin