Re: Constraint Exclusion + Joins?

On 5/2/06, Brandon Black <blblack(at)gmail(dot)com> wrote:
>
> On 4/30/06, Heikki Linnakangas <hlinnaka(at)iki(dot)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';
>
> ------------------

If you only need 1 column from basic, then a subquery will do it for you:

SELECT jstuff_id AS basic_id, (SELECT basic_data FROM basic WHERE
basic.basic_id = jstuff.jstuff_id) FROM jstuff WHERE jstuff_data = 'foo';

If you need more than one column you can use ROW() constructors but that
gets pretty dirty.