From: Josh Berkus <josh@agliodbs.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pgsql-Performance <pgsql-performance@postgresql.org>
Subject: Re: Help analyzing 7.2.4 EXPLAIN
Date: Thu, 10 Apr 2003 17:13:19 -0700
Message-ID: <200304101713.19953.josh@agliodbs.com> (raw)
In-Reply-To: <11860.1049935883@sss.pgh.pa.us>
References: <200304091715.19565.josh@agliodbs.com>
<11860.1049935883@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, tgl@sss.pgh.pa.us
Subject: Re: Help analyzing 7.2.4 EXPLAIN
In-Reply-To: <200304101713.19953.josh@agliodbs.com>
References: <200304091715.19565.josh@agliodbs.com> <11860.1049935883@sss.pgh.pa.us> <200304101713.19953.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox