Search Postgresql Archives

Conditional JOINs ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello,

I'm currently camping with a bit of a problem -- i have a common
requests table, and based on an entry's type, I need to join a
specific other table. Consider this database layout:

###########################
-- table where all common request data is stored
CREATE TABLE log.requests (
 id BIGSERIAL,
 type SMALLINT 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.requests2 (
 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.id = log.requests1.request_id) LEFT JOIN log.requests2
ON (log.requests.id = log.requests2.request_id) -- etc
###########################


As you can see, if the requests table gets a bit large (50mil+ rows)
and when there are many different types, there will be a lot of wasted
JOINs.

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) ?

Regards,

Leon Mergen

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux