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-11 00:13:19
Message-ID: 200304101713.19953.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

pgsql-performance by date

Next:From: scott.marloweDate: 2003-04-11 15:14:00
Subject: Re: Caching (was Re: choosing the right platform)
Previous:From: Ron MayerDate: 2003-04-10 23:06:40
Subject: Re: Caching (was Re: choosing the right platform)

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