From: Gregory Stark <stark@enterprisedb.com>
To: "Brian Herlihy" <btherl@yahoo.com.au>
Cc: "Postgresql Performance" <pgsql-performance@postgresql.org>
Subject: Re: Join vs Subquery
In-Reply-To: <243066.5712.qm@web52308.mail.re2.yahoo.com> (Brian Herlihy's
	message of "Wed, 2 May 2007 23:07:57 -0700 (PDT)")
Organization: EnterpriseDB
References: <243066.5712.qm@web52308.mail.re2.yahoo.com>
Date: Thu, 03 May 2007 12:46:31 +0100
Message-ID: <874pmu2kq0.fsf@oxford.xeocode.com>
User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Brian Herlihy" <btherl@yahoo.com.au> writes:

> There is a unique index mapping domains to domain_ids.
...
> The issue: the second query results in a lower cost estimate. I am wondering
> why the second query plan was not chosen for the first query.

Well the unique index you mentioned is critical to being able to conclude the
queries are equivalent. Postgres in the past hasn't been able to use things
like unique indexes to make planning decisions because it had no
infrastructure to replan if you dropped the index. We do have such
infrastructure now so it may be possible to add features like this in the
future.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com