Re: Conditional JOINs ? - Mailing list pgsql-general

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