pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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: Thu, 10 Apr 2003 17:13:19 -0700
Message-ID: <200304101713.19953.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,

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




view thread (4+ messages)

Message-ID: <200304101713.19953.josh@agliodbs.com>
Permalink:  ../200304101713.19953.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200304101713.19953.josh@agliodbs.com

 · 

reply

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: <200304101713.19953.josh@agliodbs.com>
  References: <200304091715.19565.josh@agliodbs.com> <11860.1049935883@sss.pgh.pa.us> <200304101713.19953.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