On Mar 18, 2008, at 3:06 PM, Leon Mergen wrote:
Hello Alban,
On 3/18/08, Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>
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@xxxxxxxxxx
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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
