Re: Help analyzing 7.2.4 EXPLAIN

Tom,

If you're interested, here's the query I ended up with.  It's much uglier than
the original query, but gives me slightly more data (one bit of information
is seperated into 2 columns rather than rolled up), is 100ms faster, and
should not slow down much with the growth of the tables:

SELECT users.user_id, (users.fname || COALESCE(' ' || users.minit, '') || ' '
|| users.lname) as atty_name,
    users.lname,
    COALESCE (
    (SELECT if_addendee_conflict(users.user_id, 3272, '2003-04-15 10:00', '1
days'::INTERVAL,
        events.event_id, events.event_date, events.duration, event_cats.status,
        '30 minutes', staff_id) as cflt
        FROM event_types, event_cats, event_days, events, event_staff
        WHERE events.event_id = event_days.event_id
            and events.etype_id = event_types.etype_id
            AND event_types.ecat_id = event_cats.ecat_id
            AND event_days.event_day BETWEEN '2003-04-15' AND '2003-04-16 10:00'
            AND events.event_id <> 3272
            AND events.event_id = event_staff.event_id
            AND event_staff.staff_id = users.user_id
            AND event_cats.status IN (1,3)
        ORDER BY cflt LIMIT 1),
    (SELECT 'LEAVE'::TEXT
     FROM event_types, event_cats, event_days, events
     WHERE events.event_id = event_days.event_id
            and events.etype_id = event_types.etype_id
            AND event_types.ecat_id = event_cats.ecat_id
            AND event_days.event_day BETWEEN '2003-04-15' AND '2003-04-16 10:00'
            AND events.event_id <> 3272
            AND event_cats.status = 4)
         ) AS conflict,
    (SELECT (staff_id > 0) FROM event_staff
        WHERE event_id = 3272
        AND staff_id = users.user_id) as assigned
FROM users
WHERE EXISTS (SELECT teams_users.user_id FROM teams_users JOIN teams_tree
    ON teams_users.team_id = teams_tree.team_id WHERE teams_tree.treeno
    BETWEEN 3 and 4 AND teams_users.user_id = users.user_id)
    AND users.status > 0
ORDER BY conflict, users.lname, atty_name;

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco