From: Mike Adams <madams55075@comcast.net>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Refactored queries needing validation of syntactic equivalence
Date: Wed, 10 Oct 2007 18:25:53 -0500
Message-ID: <470D5F81.5090403@comcast.net> (raw)
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).
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...
Thank you in advance and I wish the application at work used postgresql
as it's backend!
Michael Adams
Attachments:legacy_vs_refactored.txt (text/plain, 6.5K) download
legacy | refactor
---------------------------------------------------------------------+--------------------------------------------------------------------
select m.co_cd, | select m.co_cd,
m.ve_cd, | m.ve_cd,
m.ivc_cd, | m.ivc_cd,
m.po_cd, | m.po_cd,
m.itm_cd, | m.itm_cd,
m.qty, | m.qty,
m.unit_cst, | m.unit_cst,
(m.qty*m.unit_cst) as ext_cst, | (m.qty*m.unit_cst) as ext_cst,
to_char(m.rcv_dt,'YYYY-MM-DD') as received, | to_char(m.rcv_dt,'YYYY-MM-DD') as received,
origin_cd, | m.origin_cd,
to_char(m.assigned_dt,'YYYY-MM-DD') as assigned | to_char(m.assigned_dt,'YYYY-MM-DD') as assigned
from rcv_mo m | from rcv_mo m, rcv_mo r
where ( m.origin_cd = 'MOM' ) | where ( m.origin_cd = 'MOM' )
and ( m.ASSIGNED_DT <= '31-Oct-2007' | and ( m.ASSIGNED_DT <= '31-Oct-2007'
or | or
m.ASSIGNED_DT is null | m.ASSIGNED_DT is null
) | )
and ( exists ( select 1 | and ( m.po_cd = r.po_cd )
from rcv_mo o | and ( m.itm_cd = r.itm_cd )
where ( m.po_cd = o.po_cd ) | and ( r.assigned_dt is null )
and ( m.itm_cd = o.itm_cd ) | and ( r.rcv_dt <= '31-Oct-2007')
and ( o.assigned_dt is null ) | order by m.VE_CD, m.po_cd, m.itm_cd
and ( o.rcv_dt <= '31-Oct-2007') |
) | -- 0 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
) | -- [Executed: 10/10/07 9:24:09 AM CDT ] [Execution: 937/ms]
order by m.VE_CD, m.po_cd, m.itm_cd |
|
-- 0 record(s) selected [Fetch MetaData: 16/ms] [Fetch Data: 0/ms] |
-- [Executed: 10/10/07 8:47:39 AM CDT ] [Execution: 2054333/ms] |
|
---------------------------------------------------------------------+--------------------------------------------------------------------
---------------------------------------------------------------------+--------------------------------------------------------------------
select o.co_cd, | select o.co_cd,
o.ve_cd, | o.ve_cd,
o.ivc_cd, | o.ivc_cd,
o.po_cd, | o.po_cd,
o.itm_cd, | o.itm_cd,
o.qty, | o.qty,
o.unit_cst, | o.unit_cst,
(o.qty*o.unit_cst) as ext_cst, | (o.qty*o.unit_cst) as ext_cst,
to_char(o.rcv_dt,'YYYY-MM-DD') as received, | to_char(o.rcv_dt,'YYYY-MM-DD') as received,
o.origin_cd, | o.origin_cd,
to_char(o.assigned_dt,'YYYY-MM-DD') as assigned | to_char(o.assigned_dt,'YYYY-MM-DD') as assigned
from rcv_mo o | from rcv_mo o, rcv_mo m
where ( o.assigned_dt is null ) | where ( o.assigned_dt is null )
and ( o.rcv_dt <= '31-Oct-2007' ) | and ( o.rcv_dt <= '31-Oct-2007' )
and ( exists ( select 1 | and ( m.origin_cd = 'MOM' )
from rcv_mo m | and ( o.po_cd = m.po_cd )
where ( m.origin_cd = 'MOM' ) | and ( o.itm_cd = m.itm_cd )
and ( o.po_cd = m.po_cd ) | and ( m.assigned_dt <= '31-Oct-2007'
and ( o.itm_cd = m.itm_cd ) | or
and ( m.assigned_dt <= '31-Oct-2007' | m.assigned_dt is null
or | )
m.assigned_dt is null | order by o.ve_cd, o.po_cd, o.itm_cd
) |
) | -- 0 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
) | -- [Executed: 10/10/07 9:32:03 AM CDT ] [Execution: 344/ms]
order by o.ve_cd, o.po_cd, o.itm_cd |
|
-- 0 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] |
-- [Executed: 10/10/07 9:31:28 AM CDT ] [Execution: 148562/ms] |
|
---------------------------------------------------------------------+---------------------------------------------------------------------
---------------------------------------------------------------------+---------------------------------------------------------------------
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: madams55075@comcast.net
Subject: Re: Refactored queries needing validation of syntactic equivalence
In-Reply-To: <470D5F81.5090403@comcast.net>
* 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