Re: Conditional JOINs ?

On Mar 18, 2008, at 3:06 PM, Leon Mergen wrote:

> Hello Alban,
>
> On 3/18/08, Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
> wrote:
>>> Now, in my theory, you would say that if postgresql encounters
>>> ref1 =
>>> NULL, it will not attempt to JOIN the log.requests1 table. However,
>>> I've been told that because the PostgreSQL planner doesn't know that
>>> ref1 (or any other refX for that matter) is NULL, it will attempt to
>>> JOIN all tables for all rows.
>>>
>>> Is this true, and if so.. is there a workaround for this (perhaps
>>> that
>>> my database design is flawed) ?
>>
>>
>> This looks almost like table partitioning. If you inherit your
>> requestxxx tables from a common requests table and add a check
>> constraint to each inheriting table (a "partition"), the planner is
>> smart enough to figure out that no rows in that partition can
>> possibly match (constraint exclusion) and skips it.
>>
>> Instead of joining, it uses something equivalent to a UNION ALL btw,
>> which I think is what you're looking for anyway.
>
> Well, the thing (as far as I'm aware) is that table partinioning and
> UNION ALL expect the table layouts to look the same, don't they ? The
> problem I'm having is that each row in a table has some 'additional'
> information, which is in another table, and can be retrieved based on
> a specific column in the table (request_type).
>
> Now, I fail to see how UNION ALL or table partitioning can solve this
> problem, which can be my problem -- am I missing some technique how
> table partitioning can be used to extend a base table with several
> extra tables that provide extra information ?

Table partitioning is normally implemented via table inheritance and
you are free to add more, and different, columns to the "child" tables.

Observe:

CREATE SEQUENCE part_seq;
CREATE TABLE parent (
id integer PRIMARY KEY DEFAULT nextval('part_seq'),
foo text
);

CREATE TABLE child1 (
bar text,
CHECK(foo='some_type1'),
PRIMARY KEY (id)
) INHERITS (parent);

CREATE TABLE child2 (
baz text,
CHECK(foo='some_type2'),
PRIMARY KEY (id)
) INHERITS (parent);

Now, both child1 and child2 have id and foo fields, child1 will only
allow entries with foo='some_type1', child2 will only allow entries
with foo='some_type2', and both children have extra fields that
weren't present in the parent.

Erik Jones

DBA | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com