Search Postgresql Archives

Re: SQL Help: Multiple LEFT OUTER JOINs

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux