Re: SQL Help: Multiple LEFT OUTER JOINs

It looks to me like your problem is that weird area where you alias your
inner join as "t" and thenn inner join based on this alias. You're
getting a cartesian product somewhere, as evidenced by the
"rows=7000000" in your explain.
I already deleted the old mail with your table structure, but try
changing that FROM section to:

class INNER JOIN instructors ON class.id = instructors.class
LEFT OUTER JOIN person ON person.id = (whatevertable).person

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