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(at)hank(dot)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.01 rows=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(at)hank(dot)org
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
>

--
Bill Moseley
moseley(at)hank(dot)org