Re: SQL Help: Multiple LEFT OUTER JOINs

On Mon, Nov 21, 2005 at 11:45:34AM -0600, Bruno Wolff III wrote:
> On Mon, Nov 21, 2005 at 05:40:10 -0800,
>   Bill Moseley <moseley@hank.org> wrote:
> >
> > Here's where I'm missing something.  Trying to do an outer join on
> > to bring in the class row with its class_time column:
>
> You don't say exactly why you are having a problem with this, but I think you
> would be better off doing an inner join between instructors and class and
> then do an outer join of that result to person.

Sorry, I thought I was so far off it might be obvious.  I suspect I'm
making the query harder than it really is.

This query just eats CPU and doesn't seem to finish, but I didn't let
it run more than a minute (which is forever as far as I'm concerned).
The tables are not that big (10,000 people, 1500 classes)


> >     SELECT      person.id AS id, last_name,
> >                 count(instructors.class) as total,
> >                 sum (CASE WHEN class_time > now() THEN 1 ELSE 0 END) as future_class_count,
> >                 sum (CASE WHEN class_time <= now() THEN 1 ELSE 0 END) as past_class_count
> >
> >
> >       FROM      (person LEFT OUTER JOIN instructors ON (person.id = instructors.person)) t
> >                      LEFT OUTER JOIN class on ( t.class = class.id ),
> >                 person_role
> >
> >      WHERE      person_role.person = person.id
> >                 AND person_role.role = 3
> >
> >   GROUP BY      person.id, last_name;

Well, I'm stabbing in the dark now.  You mean like:

    SELECT      person.id AS id, first_name, last_name,
                count(instructors.class) as total_classes,
                sum (CASE WHEN class.id IS NULL THEN 0 ELSE 1 END) as total_class_count,  -- which is better?
                sum (CASE WHEN class_time > now() THEN 1 ELSE 0 END) as future_class_count,
                sum (CASE WHEN class_time <= now() THEN 1 ELSE 0 END) as past_class_count


      FROM      (class INNER JOIN instructors ON ( class.id = instructors.class )) t
                    LEFT OUTER JOIN person  ON ( person.id = t.person ),
                person_role

    WHERE       person_role.person = person.id
                AND person_role.role = 3

    GROUP BY    person.id, first_name, last_name;

Still eats CPU.

 GroupAggregate  (cost=1750458.67..1890662.91 rows=10212 width=39)
   ->  Sort  (cost=1750458.67..1767958.67 rows=7000000 width=39)
         Sort Key: person.id, person.first_name, person.last_name
         ->  Nested Loop  (cost=111.27..140276.35 rows=7000000 width=39)
               ->  Nested Loop  (cost=91.27..256.35 rows=7000 width=35)
                     ->  Hash Join  (cost=71.27..96.35 rows=7 width=31)
                           Hash Cond: ("outer".id = "inner"."class")
                           ->  Seq Scan on "class"  (cost=0.00..20.00 rows=1000 width=12)
                           ->  Hash  (cost=71.25..71.25 rows=7 width=27)
                                 ->  Nested Loop  (cost=3.20..71.25 rows=7 width=27)
                                       ->  Hash Join  (cost=3.20..30.77 rows=7 width=12)
                                             Hash Cond: ("outer".person = "inner".person)
                                             ->  Seq Scan on instructors  (cost=0.00..20.00 rows=1000 width=8)
                                             ->  Hash  (cost=3.01..3.01 rows=75 width=4)
                                                   ->  Index Scan using person_role_role_index on person_role
(cost=0.00..3.01rows=75 width=4) 
                                                         Index Cond: (role = 3)
                                       ->  Index Scan using person_pkey on person  (cost=0.00..5.77 rows=1 width=23)
                                             Index Cond: ("outer".person = person.id)
                     ->  Materialize  (cost=20.00..30.00 rows=1000 width=4)
                           ->  Seq Scan on instructors  (cost=0.00..20.00 rows=1000 width=4)
               ->  Materialize  (cost=20.00..30.00 rows=1000 width=4)
                     ->  Seq Scan on "class"  (cost=0.00..20.00 rows=1000 width=4)
(22 rows)



> >
> >
> >
> >
> >
> > --
> > Bill Moseley
> > moseley@hank.org
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >        subscribe-nomail command to majordomo@postgresql.org so that your
> >        message can get through to the mailing list cleanly
>

--
Bill Moseley
moseley@hank.org