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!

Hello Alban,

On 3/18/08, Alban Hertroys <dalroi@solfertje.student.utwente.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

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

Hello Alban,

On 3/18/08, Alban Hertroys <dalroi@solfertje.student.utwente.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@myemma.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

Hello Erik,

On 3/18/08, Erik Jones <erik@myemma.com> wrote:

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.

Ah, silly that I failed to understand that.

Thanks a lot for your response (Alban too) -- I can see table
partitioning solving my problem.

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

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

Ah, silly that I failed to understand that.

Nah, we all do that stuff.

Thanks a lot for your response (Alban too) -- I can see table
partitioning solving my problem.

You're welcome!

Erik Jones

DBA | Emma®
erik@myemma.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

Hello,

On 3/18/08, Erik Jones <erik@myemma.com> wrote:

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.

Excuse me for bumping this up again, but I still don't understand how
to use this approach to sequentially walk through all different child
tables in one select, without having to JOIN these tables all the time
-- or will the planner 'understand' a query such as this:

SELECT parent.*, child1.*, child2.* FROM parent LEFT JOIN child1 ON
(parent.id = child1.id) LEFT JOIN child2 ON (parent.id = child2.id);

When running explain on this, as I interpret it, it shows that the
query plan will join both child1 and child2 on all the rows inside the
parent table:

QUERY PLAN
-----------------------------------------------------------------------------------
Hash Left Join (cost=56.00..189.50 rows=2760 width=172)
Hash Cond: (public.parent.id = child1.id)
-> Hash Left Join (cost=28.00..123.55 rows=2760 width=104)
Hash Cond: (public.parent.id = child2.id)
-> Append (cost=0.00..57.60 rows=2760 width=36)
-> Seq Scan on parent (cost=0.00..21.60 rows=1160 width=36)
-> Seq Scan on child1 parent (cost=0.00..18.00
rows=800 width=36)
-> Seq Scan on child2 parent (cost=0.00..18.00
rows=800 width=36)
-> Hash (cost=18.00..18.00 rows=800 width=68)
-> Seq Scan on child2 (cost=0.00..18.00 rows=800 width=68)
-> Hash (cost=18.00..18.00 rows=800 width=68)
-> Seq Scan on child1 (cost=0.00..18.00 rows=800 width=68)

Now, of course there must be something I'm missing here.. but this
seems like the solution of table inheritance will only result in the
same problem I was having before -- either I need to JOIN every row on
all child tables, or I need to specifically iterate over all the child
tables, one child table at a time (which will probably result in even
worse performance, since the 'parent' table is huge).

Am I misunderstanding something here, or is there simple no solution
for what I want ?

Regards,

Leon Mergen

On 3/19/08, Leon Mergen <leon@solatis.com> wrote:

Excuse me for bumping this up again, but I still don't understand how
to use this approach to sequentially walk through all different child
tables in one select, without having to JOIN these tables all the time

Apparently a UNION all solved this problem -- sorry for the noise.

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

On Mar 19, 2008, at 9:01 AM, Leon Mergen wrote:

On 3/19/08, Leon Mergen <leon@solatis.com> wrote:

Excuse me for bumping this up again, but I still don't understand how
to use this approach to sequentially walk through all different child
tables in one select, without having to JOIN these tables all the
time

Apparently a UNION all solved this problem -- sorry for the noise.

If you have the child tables INHERITing from the parent, then a simple

SELECT parent.* FROM parent;

would be equivalent to manually spelling out a UNION ALL that
explicitly lists all of the tables.

Erik Jones

DBA | Emma®
erik@myemma.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

On 3/19/08, Erik Jones <erik@myemma.com> wrote:

Excuse me for bumping this up again, but I still don't understand how
to use this approach to sequentially walk through all different child
tables in one select, without having to JOIN these tables all the
time

Apparently a UNION all solved this problem -- sorry for the noise.

If you have the child tables INHERITing from the parent, then a simple

SELECT parent.* FROM parent;

would be equivalent to manually spelling out a UNION ALL that
explicitly lists all of the tables.

But this will only display the information that is common for all the
child tables -- if I also want to display all the information that is
specific for the child tables, as I understand it, I have to use a
UNION ALL and merge all the child tables together this way.

The EXPLAIN of this query:

Append (cost=0.00..2169.52 rows=34376 width=94)
-> Seq Scan ON child1 (cost=0.00..1824.71 rows=34371 width=94)
-> Seq Scan ON child2 (cost=0.00..1.05 rows=5 width=56)

Regards,

Leon Mergen

Leon Mergen wrote:

On 3/19/08, Erik Jones <erik@myemma.com> wrote:

Excuse me for bumping this up again, but I still don't understand how
to use this approach to sequentially walk through all different child
tables in one select, without having to JOIN these tables all the
time

Apparently a UNION all solved this problem -- sorry for the noise.

If you have the child tables INHERITing from the parent, then a simple

SELECT parent.* FROM parent;

would be equivalent to manually spelling out a UNION ALL that
explicitly lists all of the tables.

But this will only display the information that is common for all the
child tables -- if I also want to display all the information that is
specific for the child tables, as I understand it, I have to use a
UNION ALL and merge all the child tables together this way.

The EXPLAIN of this query:

Append (cost=0.00..2169.52 rows=34376 width=94)
-> Seq Scan ON child1 (cost=0.00..1824.71 rows=34371 width=94)
-> Seq Scan ON child2 (cost=0.00..1.05 rows=5 width=56)

Regards,

Leon Mergen

What I think you desire is more in the form of:
SELECT id, foo, bar, NULL AS "baz"
FROM child1
UNION ALL
SELECT id, foo, NULL, baz
FROM child2.

I think if you compare it to I/O volume, the joins will not cause many
additional I/Os as long as the indexes on "id" for tables child1 and
child2 will fit into memory.

- Joris