Re: Conditional JOINs ?

Hello,

On 3/18/08, Erik Jones <erik(at)myemma(dot)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