Re: Conditional JOINs ?

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

> Hello,
>
> Now, based on a previous post on the PostgreSQL mailing list
> [http://archives.postgresql.org/pgsql-general/2007-11/msg00723.php] I
> came up with this solution:
>
> ###########################
> -- table where all common request data is stored
> CREATE TABLE log.requests (
> id BIGSERIAL,
> type SMALLINT NOT NULL,
> ref1 BIGINT CHECK (type = 1) = (ref1 IS NOT NULL)),
> ref2 BIGINT CHECK (type = 2) = (ref2 IS NOT NULL)),
> timestamp INTEGER NOT NULL,
> -- all kinds of common data
> )
>
> -- only rows where log.requests.type = 1
> CREATE TABLE log.requests1 (
> request_id BIGINT NOT NULL
> )
>
> -- only rows where log.requests.type = 2
> CREATE TABLE log.requestsz2 (
> request_id BIGINT NOT NULL
> )
>
> etc (about 10 different types)
>
> the current VIEW:
> CREATE VIEW requests_view AS
> SELECT * FROM log.requests LEFT JOIN log.requests1 ON
> (log.requests.ref1 = log.requests1.request_id) LEFT JOIN log.requests2
> ON (log.requests.ref2 = log.requests2.request_id) -- etc
> ###########################
>
> 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.

There have been plenty of posts about how to set up table
partitioning, there's even an article about it in the standard
Postgres documentation:

http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html

Regards,
Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,47e019899786732118417!