Refactored queries needing validation of syntactic equivalence
От | Mike Adams |
---|---|
Тема | Refactored queries needing validation of syntactic equivalence |
Дата | |
Msg-id | 470D5F81.5090403@comcast.net обсуждение исходный текст |
Ответы |
Re: Refactored queries needing validation of syntactic equivalence
(Richard Huxton <dev@archonet.com>)
|
Список | pgsql-sql |
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 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] [FetchData: 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] [FetchData: 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] | | ---------------------------------------------------------------------+--------------------------------------------------------------------- ---------------------------------------------------------------------+---------------------------------------------------------------------
В списке pgsql-sql по дате отправления: