From: Richard Huxton <dev@archonet.com>
To: Mike Adams <madams55075@comcast.net>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Refactored queries needing validation of syntactic equivalence
Date: Thu, 11 Oct 2007 08:24:55 +0100
Message-ID: <470DCFC7.3090901@archonet.com> (raw)
In-Reply-To: <470D5F81.5090403@comcast.net>
References: <470D5F81.5090403@comcast.net>
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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, madams55075@comcast.net
Subject: Re: Refactored queries needing validation of syntactic equivalence
In-Reply-To: <470DCFC7.3090901@archonet.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