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: Wed, 9 Apr 2003 20:39:00 -0700
Message-ID: <200304092039.00376.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,
> 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 majority
(95%) of the runs of if_attendee_conflict will return NULL. But we can't
know that until we run the test, which is a bit too complex for a case
statement.
Now, if I could figure out a way to stop testing for a particular user the
first time if_attendee_conflict returned a particular result, that could cut
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?
Almost all of it. The question being answered by the query is "Please give me
the list of all users, plus which of them have a conflict for that particular
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
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?
No. <grin> the users aren't given a choice about what they want to attend --
the purpose of the query is to supply the calendar staff with a list of who's
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,
and I'll rework the query if so. Thanks for the advice!
--
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: <200304092039.00376.josh@agliodbs.com>
References: <200304091715.19565.josh@agliodbs.com> <11860.1049935883@sss.pgh.pa.us> <200304092039.00376.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