To: mark@mark.mielke.cc
cc: pgsql-performance@postgresql.org
Subject: Re: Q: Performance of join vs embedded query for simple queries? 
In-reply-to: <20060818022131.GA15599@mark.mielke.cc> 
References: <20060818003326.GA11682@mark.mielke.cc>
	<1484.1155864093@sss.pgh.pa.us>
	<20060818022131.GA15599@mark.mielke.cc>
Comments: In-reply-to mark@mark.mielke.cc
	message dated "Thu, 17 Aug 2006 22:21:31 -0400"
Date: Thu, 17 Aug 2006 22:37:16 -0400
Message-ID: <2305.1155868636@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

mark@mark.mielke.cc writes:
> That makes sense. Would it be reasonable for the planner to eliminate
> plan considerations based on the existence of unique indexes, or is
> this a fundamentally difficult thing to get right in the general case?

The big obstacle to that at the moment is that we don't have any plan
cache invalidation mechanism; so a plan that depended for correctness on
the existence of a unique index might silently give wrong results after
someone drops the index and inserts non-unique values into the table.
(If the plan actually *uses* the index, then you'd at least get an
access failure ... but if the index was merely used to make an
assumption at plan time, you wouldn't.)

The present "constraint_exclusion" mechanism will actually fail in
exactly this kind of scenario, which is why I insisted it be off by
default :-(

This has been on the radar screen for awhile.  I'd hoped we'd get a
plan invalidation mechanism in place for 8.2, but seems that's not
happening.  Eventually it'll be there, though, and then we can get
more aggressive about making deductions based on constraints.

			regards, tom lane