Re: Conditional JOINs ?

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 ?

--
Leon Mergen
http://www.solatis.com