Re: Constraint Exclusion + Joins? - Mailing list pgsql-hackers

On 4/30/06, Heikki Linnakangas <hlinnaka@iki.fi> wrote:
> On Fri, 28 Apr 2006, Brandon Black wrote:
>
> > I dug around in CVS to have a look for this, and I did eventually find
> > it (well, I found the corresponding docs patch that removed the note
> > about not working for joins).  I see it's in MAIN but not in
> > 8_1_STABLE.  Does that mean it's headed for 8.2.x when that comes
> > about? (Sorry, I'm not terribly familiar with how you guys handle all
> > of this).
>
> Yes.
>

Perhaps I'm confused about the meaning of the removal of the
JOINs-related caveat from the constraint exclusion docs in MAIN.  What
I was intending to ask about was constraint exclusion kicking in where
the constrained column is being joined to a column of another table,
with no constants involved.

For a contrived example:

--------------

CREATE TABLE basic (
   basic_id INTEGER NOT NULL PRIMARY KEY,
   basic_data TEXT
);

CREATE TABLE basic_sub1 (
  PRIMARY KEY (basic_id),
  CHECK ( basic_id >= 0 AND basic_id < 100 )
) INHERITS (basic);

CREATE TABLE basic_sub2 (
  PRIMARY KEY (basic_id),
  CHECK ( basic_id >= 100 AND basic_id < 200 )
) INHERITS (basic);

[...]

CREATE TABLE jstuff (
    jstuff_id INTEGER NOT NULL PRIMARY KEY,
    jstuff_data TEXT
);

EXPLAIN ANALYZE SELECT basic.* FROM basic JOIN jstuff ON
(basic.basic_id = jstuff.jstuff_id) WHERE jstuff_data = 'foo';

------------------