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)
