Date: Wed, 23 Oct 2002 08:27:45 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: sub-select with aggregate
In-Reply-To: <20021023081036.M5349-100000@megazone23.bigpanda.com>
Message-ID: <20021023082355.G5481-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Wed, 23 Oct 2002, Stephan Szabo wrote:

> Did you see the other two queries I gave?  On 7.3, both of those queries
> appear (according to explain output) to do the limiting of group_id
> inside the subquery rather than doing the subquery with all rows.
> The explanation above was why I believe it was different from your
> original query.

Replying to myself with a clarification.

The other forms only move the filtering clauses around.  There's still
only a filter on the outer group_id equaling the inner group_id and
a filter on group_id=3.  It's just a question of whether it's:

Scan users in subselect from group_id=3, group and aggregate them
 and join with users on subselect's group_id = users.group_id

or

Scan users for group_id=3, scan users in subselect, group and
 aggregate them and join these two on subselect's
 group_id=users.group_id.

The latter is effectively what your original query gave, the former
appears to be what my two queries from a couple of mails ago gives.