Re: Constraint Exclusion + Joins?

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';

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

I tried things like the above with small test data sets against cvs
just now on my home machine, and constraint exclusion doesn't seem to
apply here (even if all of the joined jstuff rows have ids which only
match the constraint for basic_sub3, all basic_subX's seem to get
scanned, as is the case I'm seeing in my real code against 8.1.3). Is
this sort of dynamic constraint exclusion on the radar?

Without it, some inheritance-based partitioning constructs which could
otherwise be written as a single query have to be done as loops with
seperate nested statements (to extract constants and then manually
plug them into the next statement down the chain), which seems to
make it more difficult (or nearly impossible) to support varying
where/grouping/ordering/count of the resultant inner query rows from
client code efficiently without writing a seperate plpgsql function
for every possible variation.

-- Brandon