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

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