From: Tom Lane <tgl@sss.pgh.pa.us>
To: Erik Aronesty <erik@memebot.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Putting an INDEX on a boolean field?
Date: Sun, 19 Jun 2005 14:46:24 -0400
Message-ID: <18815.1119206784@sss.pgh.pa.us> (raw)
In-Reply-To: <000a01c57328$fe00f990$1400a8c0@yellow>
References: <000a01c57328$fe00f990$1400a8c0@yellow>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, erik@memebot.com
Subject: Re: Putting an INDEX on a boolean field?
In-Reply-To: <18815.1119206784@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox