Skip site navigation (1) Skip section navigation (2)

Re: Help analyzing 7.2.4 EXPLAIN

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Pgsql-Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Help analyzing 7.2.4 EXPLAIN
Date: 2003-04-10 03:39:00
Message-ID: 200304092039.00376.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

pgsql-performance by date

Next:From: Josh BerkusDate: 2003-04-10 03:45:29
Subject: Re: Caching (was Re: choosing the right platform)
Previous:From: Matthew NuzumDate: 2003-04-10 01:16:36
Subject: Caching (was Re: choosing the right platform)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group