Re: SQL Help: Multiple LEFT OUTER JOINs - Mailing list pgsql-general

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