To: "Erik Aronesty" <erik@memebot.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Putting an INDEX on a boolean field? 
In-reply-to: <000a01c57328$fe00f990$1400a8c0@yellow> 
References: <000a01c57328$fe00f990$1400a8c0@yellow>
Comments: In-reply-to "Erik Aronesty" <erik@memebot.com>
	message dated "Fri, 17 Jun 2005 06:40:34 -0400"
Date: Sun, 19 Jun 2005 14:46:24 -0400
Message-ID: <18815.1119206784@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Erik Aronesty" <erik@memebot.com> writes:
> Should I start looking to figure out why the optimizer didn't figure out
> that it should be doing this sort of thing?

It looks to me that the problem is that convert_IN_to_join() is not
being smart about where to attach the IN's subselect to the join tree.
It's just adding it to the top FROM-expression, so that the join tree
is effectively
	((sites left join quota) IN-join usersites)
and since we don't currently allow any rearrangement of outer joins,
this cannot be rearranged into
	((sites IN-join usersites) left join quota)
as you'd like.

The really clean solution to this would be to implement logic about when
it is safe to rearrange the join order of outer joins.  But I think
that's a fairly hard problem in general.  A shorter-term solution might
be possible by teaching convert_IN_to_join() to attach the IN subselect
further down in the join tree, using logic similar to what we use to
decide where ordinary WHERE quals can bubble down to.

			regards, tom lane