Re: SQL Help: Multiple LEFT OUTER JOINs

I just noticed, also goofy is your ", person_role" in your from with no
criteria. I would generally put the "person_role.person = person.id" as
an INNER JOIN, and then only have the "person_role.role=3" in the
where. It doesn't look like that's the specific problem, but I
generally find that kind of mixed syntax muddles a query.

John McCawley wrote:

>> 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)
>
>