X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D4E33475AE5 for ; Wed, 9 Apr 2003 23:39:33 -0400 (EDT) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 01C73474E42 for ; Wed, 9 Apr 2003 23:39:32 -0400 (EDT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2954218; Wed, 09 Apr 2003 20:39:31 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Tom Lane Subject: Re: Help analyzing 7.2.4 EXPLAIN Date: Wed, 9 Apr 2003 20:39:00 -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 Message-Id: <200304092039.00376.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-32.2 required=5.0 tests=BAYES_10,IN_REP_TO,QUOTED_EMAIL_TEXT,QUOTE_TWICE_1, REFERENCES,REPLY_WITH_QUOTES,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/102 X-Sequence-Number: 1608 Tom, > Keep in mind that in the subqueries, the "actual time" shown is the time > per iteration --- you should multiply by the "loops" value to get an > accurate idea of where the time is going. With that in mind, it's real > clear that the first subplan is eating the bulk of the time. Thanks, that's what I thought, but I wanted confirmation. > The first thing that pops to mind is whether you really need the *first* > conflict, or would it be enough to find any old conflict? If you could > dispense with the ORDER BY then at least some evaluations of > if_addendee_conflict() could be saved. The problem is that I need the lowest-sorted non-NULL conflict. The major= ity=20 (95%) of the runs of if_attendee_conflict will return NULL. But we can't= =20 know that until we run the test, which is a bit too complex for a case=20 statement. Now, if I could figure out a way to stop testing for a particular user the= =20 first time if_attendee_conflict returned a particular result, that could cu= t=20 the number of subquery loops by 1/3. Any ideas? > Realistically, though, I think you're going to have to refactor the work > to make this perform reasonably. How much of what > if_addendee_conflict() does is actually dependent on the user_id?=20=20 Almost all of it. The question being answered by the query is "Please give= me=20 the list of all users, plus which of them have a conflict for that particul= ar=20 date and time and what kind of conflict it is". >Could > you separate out tests that depend only on the event, and do that in a > separate pass that is done only once per event, instead once per > event*user? If you could reduce the number of events that need to be > examined for any given user, you could get somewhere. Regrettably, no. We have to run it for each user. I was acutally hoping = to=20 come up with a way of running for less events, acutally .... > > Also, I don't see where this query checks to see if the user is actually > interested in attending the event. Is that one of the things > if_addendee_conflict checks?=20 No. the users aren't given a choice about what they want to attend = --=20 the purpose of the query is to supply the calendar staff with a list of who= 's=20 available so the users can be assigned -- whether they want to or not. Well, we'll see if the current incarnation bogs down in a couple of months,= =20 and I'll rework the query if so. Thanks for the advice! --=20 Josh Berkus Aglio Database Solutions San Francisco