X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C4C48474E42 for ; Thu, 10 Apr 2003 20:13:34 -0400 (EDT) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 03A24474E4F for ; Thu, 10 Apr 2003 20:13:30 -0400 (EDT) Received: from [216.135.165.74] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2955916; Thu, 10 Apr 2003 17:13:26 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Tom Lane Subject: Re: Help analyzing 7.2.4 EXPLAIN Date: Thu, 10 Apr 2003 17:13:19 -0700 User-Agent: KMail/1.4.3 Cc: Pgsql-Performance References: <200304091715.19565.josh@agliodbs.com> <11860.1049935883@sss.pgh.pa.us> In-Reply-To: <11860.1049935883@sss.pgh.pa.us> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200304101713.19953.josh@agliodbs.com> X-Spam-Status: No, hits=-22.7 required=5.0 tests=BAYES_01,IN_REP_TO,REFERENCES,USER_AGENT_KMAIL autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/120 X-Sequence-Number: 1626 Tom, If you're interested, here's the query I ended up with. It's much uglier t= han=20 the original query, but gives me slightly more data (one bit of information= =20 is seperated into 2 columns rather than rolled up), is 100ms faster, and=20 should not slow down much with the growth of the tables: SELECT users.user_id, (users.fname || COALESCE(' ' || users.minit, '') || '= '=20 || users.lname) as atty_name, users.lname, COALESCE ( (SELECT if_addendee_conflict(users.user_id, 3272, '2003-04-15 10:00', '1= =20 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 =3D event_days.event_id and events.etype_id =3D event_types.etype_id AND event_types.ecat_id =3D 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 =3D event_staff.event_id AND event_staff.staff_id =3D 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 =3D event_days.event_id and events.etype_id =3D event_types.etype_id AND event_types.ecat_id =3D 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 =3D 4) ) AS conflict, (SELECT (staff_id > 0) FROM event_staff WHERE event_id =3D 3272 AND staff_id =3D 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 =3D teams_tree.team_id WHERE teams_tree.treeno BETWEEN 3 and 4 AND teams_users.user_id =3D users.user_id) AND users.status > 0 ORDER BY conflict, users.lname, atty_name; --=20 -Josh Berkus Aglio Database Solutions San Francisco