Message-ID: <470DCFC7.3090901@archonet.com>
Date: Thu, 11 Oct 2007 08:24:55 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.6 (X11/20070728)
MIME-Version: 1.0
To: Mike Adams <madams55075@comcast.net>
CC: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Refactored queries needing validation of syntactic equivalence
References: <470D5F81.5090403@comcast.net>
In-Reply-To: <470D5F81.5090403@comcast.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Mike Adams wrote:
> Hello! I'm a long time lurker who has become responsible for maintaining 
> / updating utility queries at work.  I've reworked two queries (as text 
> attachment as they are wide lines) to enhance the planner's chance of 
> speeding up the queries (Oracle8i's).

Well, I can't say it's standard procedure to look at Oracle queries, but 
  if you don't tell anyone I won't :-)

> I'm looking for someone to eyeball them and let me know if I've folded 
> the sub-selects up correctly (I'm the ONLY sql speaking person at work 
> so having a coworker do so is unfortunately not possible).
> 
> Also unfortunately, there currently aren't any issues in the database 
> that these queries are designed to find.  All I can say for sure is (as 
> you can see below each query) my refactored queries *at the least* 
> return *no* data faster than the legacy queries...

Test data time then. No alternative to testing these things.

> Thank you in advance and I wish the application at work used postgresql 
> as it's backend!

OK, you've substituted and EXISTS check against a sub-query with a 
self-join. The key question of course is whether your join can return 
more than one match from "rcv_mo m" for each row in "rcv_mo o". I can't 
say without knowing more about your schema, and even then I'd want to 
test it.

-- 
   Richard Huxton
   Archonet Ltd