From: Tom Lane <tgl@sss.pgh.pa.us>
To: mark@mark.mielke.cc
Cc: pgsql-performance@postgresql.org
Subject: Re: Q: Performance of join vs embedded query for simple queries?
Date: Thu, 17 Aug 2006 22:37:16 -0400
Message-ID: <2305.1155868636@sss.pgh.pa.us> (raw)
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>
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
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mark@mark.mielke.cc
Subject: Re: Q: Performance of join vs embedded query for simple queries?
In-Reply-To: <2305.1155868636@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