Poorly Optimized SQL
A user on IDS 11.50.FC6 (Solaris) asked why an UPDATE whose SET clause used a correlated subquery against a 3M-row temp table took over two hours, with the explain plan showing ~49.8 billion rows scanned on that table despite an index path on v_id. Suggestions included a VARCHAR-vs-INTEGER cast mismatch (the poster said dropping the cast didn't help), a possible optimizer-costing APAR (IC86189), using MERGE, and an UPDATE...FROM rewrite (not valid in Informix). Fernando Nunes suspected many duplicate v_id rows in the big table inflating the reads and asked the poster to verify; the thread ends there with no resolution recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All,
Can someone explain why Informix is going out to lunch on the 'set' subquery?
The accessed table is the same one as in the main query. Almost immediately
prior to running this statement, that table was "update statistics low drop
distributions" and "update statistics high on v_id", so stats should be fine.
Running Informix 11.50.FC6 on Solaris 10.
I'm interested in *why* the Optimizer is basically ignoring the index on 'm1'
and taking so long to run that subquery (as opposed to configuration changes
to make it run faster; there are plenty of tuning issues to be addressed).
v_inq is a permanent table with 8000 rows, residing in one dbspace. (v_id
integer, primary key)
mthly_info (s / s1) is a temp table created by selecting from an external
table, 3M rows. (v_id varchar(50), indexed)
mthly_updts (m) is a very small temp table holding a subset of ids and
statuses, 9365 rows. (v_id varchar(50))
One Temp dbspace configured (I know, but this is a dev box, short on space).
PDQ Priority = 100 -- not in use, so not relevant.
I would expect the Query to run in this fashion (please correct if I'm wrong):
1) Select v_ids between tables 'm' and 's'.
2) Update 'v_inq' records for those v_ids,
3) pulling associated cancel_dt from 's1' matching by v_id.
So, why is the optimizer hitting s1 *49.8 Billion* times? (see Explain Plan)
Even if it sequentially scanned s1 for every v_inq record, that is only 24B
reads (8K * 3M).
Thanks,
Mike
Here is the Explain Plan:
QUERY: (OPTIMIZATION TIMESTAMP: 08-05-2013 16:17:20)
------
update v_inq
set reg_cncl_dt = (select unique cancel_dt from mthly_info s1
where s1.v_id = to_char(v_inq.v_id))
where v_id in (
select unique m.v_id::integer from mthly_updts m, mthly_info s
where m.v_id = s.v_id
and m.typ = 'E' and m.cit = 'U'
and s.status = 'C' )
and vg_stat <> 'C'
Estimated Cost: 546
Estimated # of Rows Returned: 1
Maximum Threads: 0
1) devdba.v_inq: INDEX PATH
Filters: devdba.v_inq.vg_stat != 'C'
(1) Index Name: devdba.vi_vid_idx
Index Keys: v_id (Parallel, fragments: ALL)
Lower Index Filter: devdba.v_inq.v_id = ANY <subquery>
Subquery:
---------
Estimated Cost: 122589
Estimated # of Rows Returned: 1
Maximum Threads: 1
1) devdba.s1: INDEX PATH
(1) Index Name: mthly_info_idx1
Index Keys: v_id (Key-First) (Parallel, fragments: ALL)
Index Key Filters: (devdba.s1.v_id = TO_CHAR (devdba.v_inq.v_id ) )
Subquery:
---------
Estimated Cost: 544
Estimated # of Rows Returned: 1
Maximum Threads: 5
1) devdba.m: SEQUENTIAL SCAN
Filters: (devdba.m.cit = 'U' AND devdba.m.typ = 'E' )
2) devdba.s: INDEX PATH
Filters: devdba.s.status = 'C'
(1) Index Name: mthly_info_idx1
Index Keys: v_id (Parallel, fragments: ALL)
Lower Index Filter: devdba.m.v_id = devdba.s.v_id
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 voter_inquiry
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 179 1 188 00:00.24 547
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 m
t2 s
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 997 94 9365 00:00.01 411
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 188 275859 997 00:00.25 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 188 9 00:00.32 545
type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 183 1 188 00:00.10
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 s1
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 16110 1 49808753010 10155:54.95 122590
type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 179 1 179 113:03.41
I'd need to look into it much more carefully... But aren't you trying to
match a VARCHAR(50) against a CAST to INTEGER?
Regards
On Tue, Aug 6, 2013 at 5:14 PM, MICHAEL HOFFMAN <mrh@panix.com> wrote:
> Hi All,
> Can someone explain why Informix is going out to lunch on the 'set'
> subquery?
> The accessed table is the same one as in the main query. Almost immediately
> prior to running this statement, that table was "update statistics low drop
> distributions" and "update statistics high on v_id", so stats should be
> fine.
>
> Running Informix 11.50.FC6 on Solaris 10.
>
> I'm interested in *why* the Optimizer is basically ignoring the index on
> 'm1'
> and taking so long to run that subquery (as opposed to configuration
> changes
> to make it run faster; there are plenty of tuning issues to be addressed).
>
> v_inq is a permanent table with 8000 rows, residing in one dbspace. (v_id
> integer, primary key)
> mthly_info (s / s1) is a temp table created by selecting from an external
> table, 3M rows. (v_id varchar(50), indexed)
> mthly_updts (m) is a very small temp table holding a subset of ids and
> statuses, 9365 rows. (v_id varchar(50))
>
> One Temp dbspace configured (I know, but this is a dev box, short on
> space).
> PDQ Priority = 100 -- not in use, so not relevant.
>
> I would expect the Query to run in this fashion (please correct if I'm
> wrong):
> 1) Select v_ids between tables 'm' and 's'.
> 2) Update 'v_inq' records for those v_ids,
> 3) pulling associated cancel_dt from 's1' matching by v_id.
>
> So, why is the optimizer hitting s1 *49.8 Billion* times? (see Explain
> Plan)
> Even if it sequentially scanned s1 for every v_inq record, that is only 24B
> reads (8K * 3M).
>
> Thanks,
> Mike
>
> Here is the Explain Plan:
> QUERY: (OPTIMIZATION TIMESTAMP: 08-05-2013 16:17:20)
> ------
> update v_inq
> set reg_cncl_dt = (select unique cancel_dt from mthly_info s1
>
> where s1.v_id = to_char(v_inq.v_id))
> where v_id in (
> select unique m.v_id::integer from mthly_updts m, mthly_info s>
> where m.v_id = s.v_id
>
> and m.typ = 'E' and m.cit = 'U'
>
> and s.status = 'C' )
> and vg_stat <> 'C'
>
> Estimated Cost: 546
> Estimated # of Rows Returned: 1
> Maximum Threads: 0
>
> 1) devdba.v_inq: INDEX PATH
>
> Filters: devdba.v_inq.vg_stat != 'C'
>
> (1) Index Name: devdba.vi_vid_idx
>
> Index Keys: v_id (Parallel, fragments: ALL)
>
> Lower Index Filter: devdba.v_inq.v_id = ANY <subquery>
>
> Subquery:
>
> ---------
>
> Estimated Cost: 122589
>
> Estimated # of Rows Returned: 1
>
> Maximum Threads: 1
>
> 1) devdba.s1: INDEX PATH
>
> (1) Index Name: mthly_info_idx1
>
> Index Keys: v_id (Key-First) (Parallel, fragments: ALL)
>
> Index Key Filters: (devdba.s1.v_id = TO_CHAR (devdba.v_inq.v_id ) )
>
> Subquery:
>
> ---------
>
> Estimated Cost: 544
>
> Estimated # of Rows Returned: 1
>
> Maximum Threads: 5
>
> 1) devdba.m: SEQUENTIAL SCAN
>
> Filters: (devdba.m.cit = 'U' AND devdba.m.typ = 'E' )
>
> 2) devdba.s: INDEX PATH
>
> Filters: devdba.s.status = 'C'
>
> (1) Index Name: mthly_info_idx1
>
> Index Keys: v_id (Parallel, fragments: ALL)
>
> Lower Index Filter: devdba.m.v_id = devdba.s.v_id
> NESTED LOOP JOIN
>
> Query statistics:
> -----------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 voter_inquiry
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 179 1 188 00:00.24 547
>
> Subquery statistics:
> --------------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 m
> t2 s
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 997 94 9365 00:00.01 411
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t2 188 275859 997 00:00.25 1
>
> type rows_prod est_rows time est_cost
> -------------------------------------------------
> nljoin 188 9 00:00.32 545
>
> type rows_sort est_rows rows_cons time
> -------------------------------------------------
> sort 183 1 188 00:00.10
>
> Subquery statistics:
> --------------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 s1
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 16110 1 49808753010 10155:54.95 122590
>
> type rows_sort est_rows rows_cons time
> -------------------------------------------------
> sort 179 1 179 113:03.41
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--20cf3071c8869782d704e34b7e16
Yes, but it doesn't matter; I tried without the cast and got the same result. The query ran super-slow. The reason I showed the query with the cast is that I had a full Explain Plan (I killed the non-cast query before it finished, after 25 minutes!) If I remove the 'set' subquery and use a default date, the update runs in a couple seconds. So the 'cast' isn't causing the problem. Informix would be doing the casting anyway. Oh, I cannot cast the *other* direction because some of the rows have letters in the id field. Obviously these don't match anything, but they cause a Conversion error.
I remember I saw some APAR about varchar fields in subqueries, with wrong
optimizer costs..... if someone could help, it would be nice. I also think it
fits just your case, in 11.50.FC6 engine.
Maybe it´s related to IC86189 ?
Regards.
Alexandre Marini
(cel) +55 11 97603-0358
> To: ids@iiug.org
> From: domusonline@gmail.com
> Subject: Re: Poorly Optimized SQL [31053]
> Date: Tue, 6 Aug 2013 14:21:40 -0400
>
> I'd need to look into it much more carefully... But aren't you trying to
> match a VARCHAR(50) against a CAST to INTEGER?
> Regards
>
> On Tue, Aug 6, 2013 at 5:14 PM, MICHAEL HOFFMAN <mrh@panix.com> wrote:
>
> > Hi All,
> > Can someone explain why Informix is going out to lunch on the 'set'
> > subquery?
> > The accessed table is the same one as in the main query. Almost immediately
> > prior to running this statement, that table was "update statistics low drop
> > distributions" and "update statistics high on v_id", so stats should be
> > fine.
> >
> > Running Informix 11.50.FC6 on Solaris 10.
> >
> > I'm interested in *why* the Optimizer is basically ignoring the index on
> > 'm1'
> > and taking so long to run that subquery (as opposed to configuration
> > changes
> > to make it run faster; there are plenty of tuning issues to be addressed).
> >
> > v_inq is a permanent table with 8000 rows, residing in one dbspace. (v_id
> > integer, primary key)
> > mthly_info (s / s1) is a temp table created by selecting from an external
> > table, 3M rows. (v_id varchar(50), indexed)
> > mthly_updts (m) is a very small temp table holding a subset of ids and
> > statuses, 9365 rows. (v_id varchar(50))
> >
> > One Temp dbspace configured (I know, but this is a dev box, short on
> > space).
> > PDQ Priority = 100 -- not in use, so not relevant.
> >
> > I would expect the Query to run in this fashion (please correct if I'm
> > wrong):
> > 1) Select v_ids between tables 'm' and 's'.
> > 2) Update 'v_inq' records for those v_ids,
> > 3) pulling associated cancel_dt from 's1' matching by v_id.
> >
> > So, why is the optimizer hitting s1 *49.8 Billion* times? (see Explain
> > Plan)
> > Even if it sequentially scanned s1 for every v_inq record, that is only 24B
> > reads (8K * 3M).
> >
> > Thanks,
> > Mike
> >
> > Here is the Explain Plan:
> > QUERY: (OPTIMIZATION TIMESTAMP: 08-05-2013 16:17:20)
> > ------
> > update v_inq
> > set reg_cncl_dt = (select unique cancel_dt from mthly_info s1
> >
> > where s1.v_id = to_char(v_inq.v_id))
> > where v_id in (
> > select unique m.v_id::integer from mthly_updts m, mthly_info s> >
> > where m.v_id = s.v_id
> >
> > and m.typ = 'E' and m.cit = 'U'
> >
> > and s.status = 'C' )
> > and vg_stat <> 'C'
> >
> > Estimated Cost: 546
> > Estimated # of Rows Returned: 1
> > Maximum Threads: 0
> >
> > 1) devdba.v_inq: INDEX PATH
> >
> > Filters: devdba.v_inq.vg_stat != 'C'
> >
> > (1) Index Name: devdba.vi_vid_idx
> >
> > Index Keys: v_id (Parallel, fragments: ALL)
> >
> > Lower Index Filter: devdba.v_inq.v_id = ANY <subquery>
> >
> > Subquery:
> >
> > ---------
> >
> > Estimated Cost: 122589
> >
> > Estimated # of Rows Returned: 1
> >
> > Maximum Threads: 1
> >
> > 1) devdba.s1: INDEX PATH
> >
> > (1) Index Name: mthly_info_idx1
> >
> > Index Keys: v_id (Key-First) (Parallel, fragments: ALL)
> >
> > Index Key Filters: (devdba.s1.v_id = TO_CHAR (devdba.v_inq.v_id ) )
> >
> > Subquery:
> >
> > ---------
> >
> > Estimated Cost: 544
> >
> > Estimated # of Rows Returned: 1
> >
> > Maximum Threads: 5
> >
> > 1) devdba.m: SEQUENTIAL SCAN
> >
> > Filters: (devdba.m.cit = 'U' AND devdba.m.typ = 'E' )
> >
> > 2) devdba.s: INDEX PATH
> >
> > Filters: devdba.s.status = 'C'
> >
> > (1) Index Name: mthly_info_idx1
> >
> > Index Keys: v_id (Parallel, fragments: ALL)
> >
> > Lower Index Filter: devdba.m.v_id = devdba.s.v_id
> > NESTED LOOP JOIN
> >
> > Query statistics:
> > -----------------
> >
> > Table map :
> > ----------------------------
> > Internal name Table name
> > ----------------------------
> > t1 voter_inquiry
> >
> > type table rows_prod est_rows rows_scan time est_cost
> > -------------------------------------------------------------------
> > scan t1 179 1 188 00:00.24 547
> >
> > Subquery statistics:
> > --------------------
> >
> > Table map :
> > ----------------------------
> > Internal name Table name
> > ----------------------------
> > t1 m
> > t2 s
> >
> > type table rows_prod est_rows rows_scan time est_cost
> > -------------------------------------------------------------------
> > scan t1 997 94 9365 00:00.01 411
> >
> > type table rows_prod est_rows rows_scan time est_cost
> > -------------------------------------------------------------------
> > scan t2 188 275859 997 00:00.25 1
> >
> > type rows_prod est_rows time est_cost
> > -------------------------------------------------
> > nljoin 188 9 00:00.32 545
> >
> > type rows_sort est_rows rows_cons time
> > -------------------------------------------------
> > sort 183 1 188 00:00.10
> >
> > Subquery statistics:
> > --------------------
> >
> > Table map :
> > ----------------------------
> > Internal name Table name
> > ----------------------------
> > t1 s1
> >
> > type table rows_prod est_rows rows_scan time est_cost
> > -------------------------------------------------------------------
> > scan t1 16110 1 49808753010 10155:54.95 122590
> >
> > type rows_sort est_rows rows_cons time
> > -------------------------------------------------
> > sort 179 1 179 113:03.41
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --20cf3071c8869782d704e34b7e16
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Looks like a great place for an update merge
j.
On Aug 6, 2013, at 2:44 PM, Alexandre Marini <alexandre@briug.org> =
wrote:
> I remember I saw some APAR about varchar fields in subqueries, with =
wrong=20
> optimizer costs..... if someone could help, it would be nice. I also =
think it=20
> fits just your case, in 11.50.FC6 engine.=20
> Maybe it=B4s related to IC86189 ?=20
>=20
> Regards.=20
>=20
> Alexandre Marini=20
> (cel) +55 11 97603-0358=20
>=20
>> To: ids@iiug.org=20
>> From: domusonline@gmail.com=20
>> Subject: Re: Poorly Optimized SQL [31053]=20
>> Date: Tue, 6 Aug 2013 14:21:40 -0400=20
>>=20
>> I'd need to look into it much more carefully... But aren't you trying =
to=20
>> match a VARCHAR(50) against a CAST to INTEGER?=20
>> Regards=20
>>=20
>> On Tue, Aug 6, 2013 at 5:14 PM, MICHAEL HOFFMAN <mrh@panix.com> =
wrote:=20
>>=20
>>> Hi All,=20
>>> Can someone explain why Informix is going out to lunch on the 'set'=20=
>>> subquery?=20
>>> The accessed table is the same one as in the main query. Almost=20
> immediately=20
>>> prior to running this statement, that table was "update statistics =
low=20
> drop=20
>>> distributions" and "update statistics high on v_id", so stats should =
be=20
>>> fine.=20
>>>=20
>>> Running Informix 11.50.FC6 on Solaris 10.=20
>>>=20
>>> I'm interested in *why* the Optimizer is basically ignoring the =
index on=20
>>> 'm1'=20
>>> and taking so long to run that subquery (as opposed to configuration=20=
>>> changes=20
>>> to make it run faster; there are plenty of tuning issues to be =
addressed).=20
>>>=20
>>> v_inq is a permanent table with 8000 rows, residing in one dbspace. =
(v_id=20
>>> integer, primary key)=20
>>> mthly_info (s / s1) is a temp table created by selecting from an =
external=20
>>> table, 3M rows. (v_id varchar(50), indexed)=20
>>> mthly_updts (m) is a very small temp table holding a subset of ids =
and=20
>>> statuses, 9365 rows. (v_id varchar(50))=20
>>>=20
>>> One Temp dbspace configured (I know, but this is a dev box, short on=20=
>>> space).=20
>>> PDQ Priority =3D 100 -- not in use, so not relevant.=20
>>>=20
>>> I would expect the Query to run in this fashion (please correct if =
I'm=20
>>> wrong):=20
>>> 1) Select v_ids between tables 'm' and 's'.=20
>>> 2) Update 'v_inq' records for those v_ids,=20
>>> 3) pulling associated cancel_dt from 's1' matching by v_id.=20
>>>=20
>>> So, why is the optimizer hitting s1 *49.8 Billion* times? (see =
Explain=20
>>> Plan)=20
>>> Even if it sequentially scanned s1 for every v_inq record, that is =
only=20
> 24B=20
>>> reads (8K * 3M).=20
>>>=20
>>> Thanks,=20
>>> Mike=20
>>>=20
>>> Here is the Explain Plan:=20
>>> QUERY: (OPTIMIZATION TIMESTAMP: 08-05-2013 16:17:20)=20
>>> ------=20
>>> update v_inq=20
>>> set reg_cncl_dt =3D (select unique cancel_dt from mthly_info s1=20
>>>=20
>>> where s1.v_id =3D to_char(v_inq.v_id))=20
>>> where v_id in (=20
>>> select unique m.v_id::integer from mthly_updts m, mthly_info s=20>>>=20
>>> where m.v_id =3D s.v_id=20
>>>=20
>>> and m.typ =3D 'E' and m.cit =3D 'U'=20
>>>=20
>>> and s.status =3D 'C' )=20
>>> and vg_stat <> 'C'=20
>>>=20
>>> Estimated Cost: 546=20
>>> Estimated # of Rows Returned: 1=20
>>> Maximum Threads: 0=20
>>>=20
>>> 1) devdba.v_inq: INDEX PATH=20
>>>=20
>>> Filters: devdba.v_inq.vg_stat !=3D 'C'=20
>>>=20
>>> (1) Index Name: devdba.vi_vid_idx=20
>>>=20
>>> Index Keys: v_id (Parallel, fragments: ALL)=20
>>>=20
>>> Lower Index Filter: devdba.v_inq.v_id =3D ANY <subquery>=20
>>>=20
>>> Subquery:=20
>>>=20
>>> ---------=20
>>>=20
>>> Estimated Cost: 122589=20
>>>=20
>>> Estimated # of Rows Returned: 1=20
>>>=20
>>> Maximum Threads: 1=20
>>>=20
>>> 1) devdba.s1: INDEX PATH=20
>>>=20
>>> (1) Index Name: mthly_info_idx1=20
>>>=20
>>> Index Keys: v_id (Key-First) (Parallel, fragments: ALL)=20
>>>=20
>>> Index Key Filters: (devdba.s1.v_id =3D TO_CHAR (devdba.v_inq.v_id ) =
)=20
>>>=20
>>> Subquery:=20
>>>=20
>>> ---------=20
>>>=20
>>> Estimated Cost: 544=20
>>>=20
>>> Estimated # of Rows Returned: 1=20
>>>=20
>>> Maximum Threads: 5=20
>>>=20
>>> 1) devdba.m: SEQUENTIAL SCAN=20
>>>=20
>>> Filters: (devdba.m.cit =3D 'U' AND devdba.m.typ =3D 'E' )=20
>>>=20
>>> 2) devdba.s: INDEX PATH=20
>>>=20
>>> Filters: devdba.s.status =3D 'C'=20
>>>=20
>>> (1) Index Name: mthly_info_idx1=20
>>>=20
>>> Index Keys: v_id (Parallel, fragments: ALL)=20
>>>=20
>>> Lower Index Filter: devdba.m.v_id =3D devdba.s.v_id=20
>>> NESTED LOOP JOIN=20
>>>=20
>>> Query statistics:=20
>>> -----------------=20
>>>=20
>>> Table map :=20
>>> ----------------------------=20
>>> Internal name Table name=20
>>> ----------------------------=20
>>> t1 voter_inquiry=20
>>>=20
>>> type table rows_prod est_rows rows_scan time est_cost=20
>>> -------------------------------------------------------------------=20=
>>> scan t1 179 1 188 00:00.24 547=20
>>>=20
>>> Subquery statistics:=20
>>> --------------------=20
>>>=20
>>> Table map :=20
>>> ----------------------------=20
>>> Internal name Table name=20
>>> ----------------------------=20
>>> t1 m=20
>>> t2 s=20
>>>=20
>>> type table rows_prod est_rows rows_scan time est_cost=20
>>> -------------------------------------------------------------------=20=
>>> scan t1 997 94 9365 00:00.01 411=20
>>>=20
>>> type table rows_prod est_rows rows_scan time est_cost=20
>>> -------------------------------------------------------------------=20=
>>> scan t2 188 275859 997 00:00.25 1=20
>>>=20
>>> type rows_prod est_rows time est_cost=20
>>> -------------------------------------------------=20
>>> nljoin 188 9 00:00.32 545=20
>>>=20
>>> type rows_sort est_rows rows_cons time=20
>>> -------------------------------------------------=20
>>> sort 183 1 188 00:00.10=20
>>>=20
>>> Subquery statistics:=20
>>> --------------------=20
>>>=20
>>> Table map :=20
>>> ----------------------------=20
>>> Internal name Table name=20
>>> ----------------------------=20
>>> t1 s1=20
>>>=20
>>> type table rows_prod est_rows rows_scan time est_cost=20
>>> -------------------------------------------------------------------=20=
>>> scan t1 16110 1 49808753010 10155:54.95 122590=20
>>>=20
>>> type rows_sort est_rows rows_cons time=20
>>> -------------------------------------------------=20
>>> sort 179 1 179 113:03.41=20
>>>=20
>>>=20
>>>=20
>>>=20
>>=20
> =
**************************************************************************=
*****=20
>>> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>>>=20
>>>=20
>>=20
>> --=20
>> Fernando Nunes=20
>> Portugal=20
>>=20
>> http://informix-technology.blogspot.com=20
>> My email works... but I don't check it frequently...=20
>>=20
>>
I can't test it, but I think this would accomplish the same task and be a bit
easier for the optimizer to digest.
--EEM
UPDATE q SET reg_cncl_dt = s1.cancel_dt
FROM v_inq q
INNER JOIN
(
SELECT m.v_id::INTEGER FROM mthly_updts m, mthly_info s
WHERE m.v_id = s.v_id
AND m.typ = 'E'
AND m.cit = 'U'
AND s.status = 'C'
) d
ON d.v_id = q.v_id
AND q.vg_stat <> 'C'
INNER JOIN
(
SELECT MAX(cancel_dt) cancel_date, v_id
FROM mthly_info s1
GROUP BY v_id
) s1 ON s1.v_id = TO_CHAR(q.v_id)
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> MICHAEL HOFFMAN
> Sent: Tuesday, August 06, 2013 11:14 AM
> To: ids@iiug.org
> Subject: Poorly Optimized SQL [31052]
>
> Hi All,
> Can someone explain why Informix is going out to lunch on the 'set'
> subquery?
> The accessed table is the same one as in the main query. Almost
> immediately prior to running this statement, that table was "update
> statistics low drop distributions" and "update statistics high on
> v_id", so stats should be fine.
>
> Running Informix 11.50.FC6 on Solaris 10.
>
> I'm interested in *why* the Optimizer is basically ignoring the index
> on 'm1'
> and taking so long to run that subquery (as opposed to configuration
> changes to make it run faster; there are plenty of tuning issues to be
> addressed).
>
> v_inq is a permanent table with 8000 rows, residing in one dbspace.
> (v_id integer, primary key) mthly_info (s / s1) is a temp table created
> by selecting from an external table, 3M rows. (v_id varchar(50),
> indexed) mthly_updts (m) is a very small temp table holding a subset of
> ids and statuses, 9365 rows. (v_id varchar(50))
>
> One Temp dbspace configured (I know, but this is a dev box, short on
> space).
> PDQ Priority = 100 -- not in use, so not relevant.
>
> I would expect the Query to run in this fashion (please correct if I'm
> wrong):
> 1) Select v_ids between tables 'm' and 's'.
> 2) Update 'v_inq' records for those v_ids,
> 3) pulling associated cancel_dt from 's1' matching by v_id.
>
> So, why is the optimizer hitting s1 *49.8 Billion* times? (see Explain
> Plan) Even if it sequentially scanned s1 for every v_inq record, that
> is only 24B reads (8K * 3M).
>
> Thanks,
> Mike
>
> Here is the Explain Plan:
> QUERY: (OPTIMIZATION TIMESTAMP: 08-05-2013 16:17:20)
> ------
> update v_inq
> set reg_cncl_dt = (select unique cancel_dt from mthly_info s1
>
> where s1.v_id = to_char(v_inq.v_id))
> where v_id in (
> select unique m.v_id::integer from mthly_updts m, mthly_info s>
> where m.v_id = s.v_id
>
> and m.typ = 'E' and m.cit = 'U'
>
> and s.status = 'C' )
> and vg_stat <> 'C'
>
> Estimated Cost: 546
> Estimated # of Rows Returned: 1
> Maximum Threads: 0
>
> 1) devdba.v_inq: INDEX PATH
>
> Filters: devdba.v_inq.vg_stat != 'C'
>
> (1) Index Name: devdba.vi_vid_idx
>
> Index Keys: v_id (Parallel, fragments: ALL)
>
> Lower Index Filter: devdba.v_inq.v_id = ANY <subquery>
>
> Subquery:
>
> ---------
>
> Estimated Cost: 122589
>
> Estimated # of Rows Returned: 1
>
> Maximum Threads: 1
>
> 1) devdba.s1: INDEX PATH
>
> (1) Index Name: mthly_info_idx1
>
> Index Keys: v_id (Key-First) (Parallel, fragments: ALL)
>
> Index Key Filters: (devdba.s1.v_id = TO_CHAR (devdba.v_inq.v_id ) )
>
> Subquery:
>
> ---------
>
> Estimated Cost: 544
>
> Estimated # of Rows Returned: 1
>
> Maximum Threads: 5
>
> 1) devdba.m: SEQUENTIAL SCAN
>
> Filters: (devdba.m.cit = 'U' AND devdba.m.typ = 'E' )
>
> 2) devdba.s: INDEX PATH
>
> Filters: devdba.s.status = 'C'
>
> (1) Index Name: mthly_info_idx1
>
> Index Keys: v_id (Parallel, fragments: ALL)
>
> Lower Index Filter: devdba.m.v_id = devdba.s.v_id NESTED LOOP JOIN
>
> Query statistics:
> -----------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 voter_inquiry
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 179 1 188 00:00.24 547
>
> Subquery statistics:
> --------------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 m
> t2 s
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 997 94 9365 00:00.01 411
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t2 188 275859 997 00:00.25 1
>
> type rows_prod est_rows time est_cost
> -------------------------------------------------
> nljoin 188 9 00:00.32 545
>
> type rows_sort est_rows rows_cons time
> -------------------------------------------------
> sort 183 1 188 00:00.10
>
> Subquery statistics:
> --------------------
>
> Table map :
> ----------------------------
> Internal name Table name
> ----------------------------
> t1 s1
>
> type table rows_prod est_rows rows_scan time est_cost
> -------------------------------------------------------------------
> scan t1 16110 1 49808753010 10155:54.95 122590
>
> type rows_sort est_rows rows_cons time
> -------------------------------------------------
> sort 179 1 179 113:03.41
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
Everett, That syntax doesn't work. I don't believe Informix allows either aliases on the updated table or "From" clauses in update statements. Always wished it did, but AFAIK, that SQL has never been implemented. Thanks, Mike
I'll look into the APAR. However, the incorrect costs don't change the fact that the subquery explodes the time to run the update. Is my logic for how the query *shold* process correct? The subquery seems to use the index (it's an INDEX PATH lookup in the plan). Thanks, Mike
I looked into it a bit more carefully. Some questions:
1- How many records will result from the "external" WHERE (the one that
defines how many records will be updated
2- You say the table being updated has 8000 rows. The table where the value
to update comes from has 3M. You're joining both which means that
eventually there will be a lot of records in S1 for each value of the table
being updated. And you're reading all, and grabbing a distinct value. This
process happens the number of times of 1). I'd say 179?
3- If you do a count in s1 where v_id in those 179 records, how much will
it answer?
Regards
On Tue, Aug 6, 2013 at 7:21 PM, Fernando Nunes <domusonline@gmail.com>wrote:
> I'd need to look into it much more carefully... But aren't you trying to
> match a VARCHAR(50) against a CAST to INTEGER?
> Regards
>
>
> On Tue, Aug 6, 2013 at 5:14 PM, MICHAEL HOFFMAN <mrh@panix.com> wrote:
>
>> Hi All,
>> Can someone explain why Informix is going out to lunch on the 'set'
>> subquery?
>> The accessed table is the same one as in the main query. Almost
>> immediately
>> prior to running this statement, that table was "update statistics low
>> drop
>> distributions" and "update statistics high on v_id", so stats should be
>> fine.
>>
>> Running Informix 11.50.FC6 on Solaris 10.
>>
>> I'm interested in *why* the Optimizer is basically ignoring the index on
>> 'm1'
>> and taking so long to run that subquery (as opposed to configuration
>> changes
>> to make it run faster; there are plenty of tuning issues to be addressed).
>>
>> v_inq is a permanent table with 8000 rows, residing in one dbspace. (v_id
>> integer, primary key)
>> mthly_info (s / s1) is a temp table created by selecting from an external
>> table, 3M rows. (v_id varchar(50), indexed)
>> mthly_updts (m) is a very small temp table holding a subset of ids and
>> statuses, 9365 rows. (v_id varchar(50))
>>
>> One Temp dbspace configured (I know, but this is a dev box, short on
>> space).
>> PDQ Priority = 100 -- not in use, so not relevant.
>>
>> I would expect the Query to run in this fashion (please correct if I'm
>> wrong):
>> 1) Select v_ids between tables 'm' and 's'.
>> 2) Update 'v_inq' records for those v_ids,
>> 3) pulling associated cancel_dt from 's1' matching by v_id.
>>
>> So, why is the optimizer hitting s1 *49.8 Billion* times? (see Explain
>> Plan)
>> Even if it sequentially scanned s1 for every v_inq record, that is only
>> 24B
>> reads (8K * 3M).
>>
>> Thanks,
>> Mike
>>
>> Here is the Explain Plan:
>> QUERY: (OPTIMIZATION TIMESTAMP: 08-05-2013 16:17:20)
>> ------
>> update v_inq
>> set reg_cncl_dt = (select unique cancel_dt from mthly_info s1
>>
>> where s1.v_id = to_char(v_inq.v_id))
>> where v_id in (
>> select unique m.v_id::integer from mthly_updts m, mthly_info s>>
>> where m.v_id = s.v_id
>>
>> and m.typ = 'E' and m.cit = 'U'
>>
>> and s.status = 'C' )
>> and vg_stat <> 'C'
>>
>> Estimated Cost: 546
>> Estimated # of Rows Returned: 1
>> Maximum Threads: 0
>>
>> 1) devdba.v_inq: INDEX PATH
>>
>> Filters: devdba.v_inq.vg_stat != 'C'
>>
>> (1) Index Name: devdba.vi_vid_idx
>>
>> Index Keys: v_id (Parallel, fragments: ALL)
>>
>> Lower Index Filter: devdba.v_inq.v_id = ANY <subquery>
>>
>> Subquery:
>>
>> ---------
>>
>> Estimated Cost: 122589
>>
>> Estimated # of Rows Returned: 1
>>
>> Maximum Threads: 1
>>
>> 1) devdba.s1: INDEX PATH
>>
>> (1) Index Name: mthly_info_idx1
>>
>> Index Keys: v_id (Key-First) (Parallel, fragments: ALL)
>>
>> Index Key Filters: (devdba.s1.v_id = TO_CHAR (devdba.v_inq.v_id ) )
>>
>> Subquery:
>>
>> ---------
>>
>> Estimated Cost: 544
>>
>> Estimated # of Rows Returned: 1
>>
>> Maximum Threads: 5
>>
>> 1) devdba.m: SEQUENTIAL SCAN
>>
>> Filters: (devdba.m.cit = 'U' AND devdba.m.typ = 'E' )
>>
>> 2) devdba.s: INDEX PATH
>>
>> Filters: devdba.s.status = 'C'
>>
>> (1) Index Name: mthly_info_idx1
>>
>> Index Keys: v_id (Parallel, fragments: ALL)
>>
>> Lower Index Filter: devdba.m.v_id = devdba.s.v_id
>> NESTED LOOP JOIN
>>
>> Query statistics:
>> -----------------
>>
>> Table map :
>> ----------------------------
>> Internal name Table name
>> ----------------------------
>> t1 voter_inquiry
>>
>> type table rows_prod est_rows rows_scan time est_cost
>> -------------------------------------------------------------------
>> scan t1 179 1 188 00:00.24 547
>>
>> Subquery statistics:
>> --------------------
>>
>> Table map :
>> ----------------------------
>> Internal name Table name
>> ----------------------------
>> t1 m
>> t2 s
>>
>> type table rows_prod est_rows rows_scan time est_cost
>> -------------------------------------------------------------------
>> scan t1 997 94 9365 00:00.01 411
>>
>> type table rows_prod est_rows rows_scan time est_cost
>> -------------------------------------------------------------------
>> scan t2 188 275859 997 00:00.25 1
>>
>> type rows_prod est_rows time est_cost
>> -------------------------------------------------
>> nljoin 188 9 00:00.32 545
>>
>> type rows_sort est_rows rows_cons time
>> -------------------------------------------------
>> sort 183 1 188 00:00.10
>>
>> Subquery statistics:
>> --------------------
>>
>> Table map :
>> ----------------------------
>> Internal name Table name
>> ----------------------------
>> t1 s1
>>
>> type table rows_prod est_rows rows_scan time est_cost
>> -------------------------------------------------------------------
>> scan t1 16110 1 49808753010 10155:54.95 122590
>>
>> type rows_sort est_rows rows_cons time
>> -------------------------------------------------
>> sort 179 1 179 113:03.41
>>
>>
>>
>>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--089e0149ca3a5fc12e04e34ddd4e
Fernando, 1) There will be 179 records found by the "external" WHERE. I think (2) and (3) can be answered together: in an ideal world, there will only be *1* match for each of the 179 v_ids. So my thinking is that once the 179 rows are found, the s1 (3M row) table will only be accessed 179 times, and using the index on v_id. If that is the case, this query should be blazing fast. Yet it takes over 2 hours! So, what am I missing? Thanks, Mike
Can you check if you live in an ideal world? On Aug 7, 2013 5:18 PM, "MICHAEL HOFFMAN" <mrh@panix.com> wrote: > Fernando, > 1) There will be 179 records found by the "external" WHERE. > I think (2) and (3) can be answered together: in an ideal world, there will > only be *1* match for each of the 179 v_ids. > > So my thinking is that once the 179 rows are found, the s1 (3M row) table > will > only be accessed 179 times, and using the index on v_id. > > If that is the case, this query should be blazing fast. Yet it takes over 2 > hours! So, what am I missing? > > Thanks, > Mike > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3071c88688dfe604e35e45ea
Fernando, we all know there is no ideal world. In this case, there are 192 rows returned, but the cancel_dates are the same for each duplicate, so the "unique" will work and return only one value. Still, even with duplicates, that "set subquery" should only be called 179 times, and in most cases only find one matching row. Only for 13 rows will a second row be located, and then the Unique would have to be enforced. None of this explains why the query is so slow. Mike
So here's an interesting little nugget that I just realized: The Explain Plan doesn't always print out the "Query Statistics" portion. The plan of tables and joins is printed, but not the stats breakdown. This section prints a long time after the query is started, so if I kill the query before completion, I get no stats. Is this due to the Optimizer trying to decide the best paths? Trying to gather statistics for the tables? If so, then maybe the query works just fine, but the Optimization time is way too long. How can I correct that? Thanks, Mike
The statistics table at the end is only printed once the query has completed its execution. That is when all of the actual timings and row counts are available. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Aug 7, 2013 at 2:22 PM, MICHAEL HOFFMAN <mrh@panix.com> wrote: > So here's an interesting little nugget that I just realized: > The Explain Plan doesn't always print out the "Query Statistics" portion. > The > plan of tables and joins is printed, but not the stats breakdown. This > section > prints a long time after the query is started, so if I kill the query > before > completion, I get no stats. > > Is this due to the Optimizer trying to decide the best paths? Trying to > gather > statistics for the tables? > > If so, then maybe the query works just fine, but the Optimization time is > way > too long. How can I correct that? > > Thanks, > Mike > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0115f216830ad604e35fc0a7
Have you tried adding another item to the where clause so it's only trying to update one row? It may be worth a try, even if you only use it to get the full query plan and stats. Also, have you tried using a group expression for the set clause instead of the unique expression? Looking at it it's a wash but it may not hurt to try: set reg_cncl_dt = (select MAX(cancel_dt) from mthly_info s1 where s1.v_id = to_char(v_inq.v_id)) There's an environment variable to change the optimizer behavior as far as how many paths it searches. I think it's OPTCOMPIND. You may want to try playing with that a bit. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > MICHAEL HOFFMAN > Sent: Wednesday, August 07, 2013 1:22 PM > To: ids@iiug.org > Subject: Re: Poorly Optimized SQL [31083] > > So here's an interesting little nugget that I just realized: > The Explain Plan doesn't always print out the "Query Statistics" > portion. The plan of tables and joins is printed, but not the stats > breakdown. This section prints a long time after the query is started, > so if I kill the query before completion, I get no stats. > > Is this due to the Optimizer trying to decide the best paths? Trying to > gather statistics for the tables? > > If so, then maybe the query works just fine, but the Optimization time > is way too long. How can I correct that? > > Thanks, > Mike > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.
I did not explain myself properly... I was asking you to check the count of rows of the join beteewn the rows in the updated table and s1. ideally it should be near 179... check please. I don't expect a surprise... but we need to be sure On Aug 7, 2013 7:18 PM, "MICHAEL HOFFMAN" <mrh@panix.com> wrote: > Fernando, > we all know there is no ideal world. In this case, there are 192 rows > returned, but the cancel_dates are the same for each duplicate, so the > "unique" will work and return only one value. > Still, even with duplicates, that "set subquery" should only be called 179 > times, and in most cases only find one matching row. Only for 13 rows will > a > second row be located, and then the Unique would have to be enforced. > > None of this explains why the query is so slow. > > Mike > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0160d2f4aba40f04e362ff2b
I still would like the answer to the previous question, but I have another and found something interesting. The new question is: What is "voter_inquiry"? Do you have synonyms or views? Please try to run the query without PDQ (set it to 0) and check the difference in running time and query plan. I'm able to get weird values in explain plan, but the running time did not change (my environment is not ideal for performance tests...) It would be nice if you can open a PMR and post the number. I can provide a test case showing this (bad explain stats). I couldn't find any perfect match in our KB, but I'd have to search more carefully before assuming it's new or known. In any case I cannot reproduce the slowness... Just the weird values. Regards On Wed, Aug 7, 2013 at 11:24 PM, Fernando Nunes <domusonline@gmail.com>wrote: > I did not explain myself properly... > I was asking you to check the count of rows of the join beteewn the rows in > the updated table and s1. ideally it should be near 179... check please. I > don't expect a surprise... but we need to be sure > On Aug 7, 2013 7:18 PM, "MICHAEL HOFFMAN" <mrh@panix.com> wrote: > > > Fernando, > > we all know there is no ideal world. In this case, there are 192 rows > > returned, but the cancel_dates are the same for each duplicate, so the > > "unique" will work and return only one value. > > Still, even with duplicates, that "set subquery" should only be called > 179 > > times, and in most cases only find one matching row. Only for 13 rows > will > > a > > second row be located, and then the Unique would have to be enforced. > > > > None of this explains why the query is so slow. > > > > Mike > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e0160d2f4aba40f04e362ff2b > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11c230163ba38504e366f49f
Another question... Since the query is running with PDQ... How many query
do you have running simultaneously? Did you check if they're waiting on MGM
resources (onstat -g mgm)
Thanks
On Thu, Aug 8, 2013 at 4:07 AM, Fernando Nunes <domusonline@gmail.com>wrote:
> I still would like the answer to the previous question, but I have another
> and found something interesting.
> The new question is: What is "voter_inquiry"? Do you have synonyms or
> views?
>
> Please try to run the query without PDQ (set it to 0) and check the
> difference in running time and query plan.
> I'm able to get weird values in explain plan, but the running time did not
> change (my environment is not ideal for performance tests...)
>
> It would be nice if you can open a PMR and post the number. I can provide a
> test case showing this (bad explain stats). I couldn't find any perfect
> match in our KB, but I'd have to search more carefully before assuming it's
> new or known. In any case I cannot reproduce the slowness... Just the weird
> values.
> Regards
>
> On Wed, Aug 7, 2013 at 11:24 PM, Fernando Nunes <domusonline@gmail.com
> >wrote:
>
> > I did not explain myself properly...
> > I was asking you to check the count of rows of the join beteewn the rows
> in
> > the updated table and s1. ideally it should be near 179... check please.
> I
> > don't expect a surprise... but we need to be sure
> > On Aug 7, 2013 7:18 PM, "MICHAEL HOFFMAN" <mrh@panix.com> wrote:
> >
> > > Fernando,
> > > we all know there is no ideal world. In this case, there are 192 rows
> > > returned, but the cancel_dates are the same for each duplicate, so the
> > > "unique" will work and return only one value.
> > > Still, even with duplicates, that "set subquery" should only be called
> > 179
> > > times, and in most cases only find one matching row. Only for 13 rows
> > will
> > > a
> > > second row be located, and then the Unique would have to be enforced.
> > >
> > > None of this explains why the query is so slow.
> > >
> > > Mike
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --089e0160d2f4aba40f04e362ff2b
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --001a11c230163ba38504e366f49f
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--20cf3071c8862e535504e3671d85
Fernando, I'll cover all your answers in one response: 1) voter_inquiry is the same table as v_inq. No synonym -- I editted the tables and columns in the Explain Plan to hide the true nature of the process. I just missed that line. It should have changed to read 'v_inq'. 2) There are exactly 179 rows matched between the 2 tables. Not distinctly, there are 179 records is v_inq which match 192 rows in s1. (s1 obviously contains some duplicates) 3) PDQ is set to 1, however it doesn't run parallel threads. This is do to the overall config settings. We haven't gotten around to running PDQ. The setting in this case was to confirm the *lack* of PDQ ability, and that proved true. It is a useless setting. Check out my other followup post --- one of your earlier posts led to a solution, and brought up another interesting Informix issue. Thanks, Mike
Hi Everett, I tried this, and originally, it had no effect. But it got me thinking. See my other post with my solution. Thanks, Mike
But have you tried to set PDQ to 0 and run the query? How did the query plan file looked? And how long did it take? P.S.: I didn't receive any other message from you yet. Regards. On Thu, Aug 8, 2013 at 8:52 PM, MICHAEL HOFFMAN <mrh@panix.com> wrote: > Fernando, > I'll cover all your answers in one response: > 1) voter_inquiry is the same table as v_inq. No synonym -- I editted the > tables and columns in the Explain Plan to hide the true nature of the > process. > I just missed that line. It should have changed to read 'v_inq'. > > 2) There are exactly 179 rows matched between the 2 tables. Not distinctly, > there are 179 records is v_inq which match 192 rows in s1. (s1 obviously > contains some duplicates) > > 3) PDQ is set to 1, however it doesn't run parallel threads. This is do to > the > overall config settings. We haven't gotten around to running PDQ. The > setting > in this case was to confirm the *lack* of PDQ ability, and that proved > true. > It is a useless setting. > > Check out my other followup post --- one of your earlier posts led to a > solution, and brought up another interesting Informix issue. > > Thanks, > Mike > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --089e0160d2f4cd0a4e04e377f77c
For what it's worth, I didn't see it either. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > Sent: Thursday, August 08, 2013 6:25 PM > To: ids@iiug.org > Subject: Re: Poorly Optimized SQL [31105] > > But have you tried to set PDQ to 0 and run the query? > How did the query plan file looked? And how long did it take? > > P.S.: I didn't receive any other message from you yet. > Regards. > > On Thu, Aug 8, 2013 at 8:52 PM, MICHAEL HOFFMAN <mrh@panix.com> wrote: > > > Fernando, > > I'll cover all your answers in one response: > > 1) voter_inquiry is the same table as v_inq. No synonym -- I editted > > the tables and columns in the Explain Plan to hide the true nature of > > the process. > > I just missed that line. It should have changed to read 'v_inq'. > > > > 2) There are exactly 179 rows matched between the 2 tables. Not > > distinctly, there are 179 records is v_inq which match 192 rows in > s1. > > (s1 obviously contains some duplicates) > > > > 3) PDQ is set to 1, however it doesn't run parallel threads. This is > > do to the overall config settings. We haven't gotten around to > running > > PDQ. The setting in this case was to confirm the *lack* of PDQ > > ability, and that proved true. > > It is a useless setting. > > > > Check out my other followup post --- one of your earlier posts led to > > a solution, and brought up another interesting Informix issue. > > > > Thanks, > > Mike > > > > > > > > > *********************************************************************** > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --089e0160d2f4cd0a4e04e377f77c > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.
Shoot! It got lost. Maybe because I added "Solved!" to the Subject? OK, so here goes another try: The query now runs in about 4 seconds! The whole process takes just about 18 minutes, 9 of which are creating the original index on the 3 million row temp table. THAT is a HUGE decrease in time, which my boss was plenty happy about (down from an original 6 hours!). What was the solution? Fernando started the thoughts, and Everett gave me the push to work it. Everett suggested looking for a single row --- it didn't help the query plan, or the speed. So I got to thinking about Fernando's comment about the to_char casting. Guess what? (I'm not going to brign back the SQL, I'm going to paraphrase it): Changing vid--varchar = to_char(vid--integer) to vid--varchar = vid--integer::varchar solved all the problems!!! Yep, use a CAST instead of the function. No clue why to_char causes the query to go nuts, but using the CAST certainly makes everything work like it should. I will show the new explain plan on Tuesaday when I'm back at work (they're about to turn the lights out on me). And I'll show Fernando a run with the *bad* way but with no PDQ setting. I do totally appreciate all the help you guys threw on this! Thanks for being there! Michael Hoffman
I will look into this. At first glance I don't get why it helped... As for the setting for PDQ, as I mentioned privately, makes the number in the query plan output to o crazy, but doesn't seem to impact the performance. A PMR would be nice as I think it's a bug. Can you be a bit more "specific" about your "varchar". What it the length defined? Thanks and regards On Sat, Aug 10, 2013 at 12:56 AM, MICHAEL HOFFMAN <mrh@panix.com> wrote: > Shoot! It got lost. Maybe because I added "Solved!" to the Subject? > > OK, so here goes another try: > The query now runs in about 4 seconds! The whole process takes just about > 18 > minutes, 9 of which are creating the original index on the 3 million row > temp > table. THAT is a HUGE decrease in time, which my boss was plenty happy > about > (down from an original 6 hours!). > > What was the solution? Fernando started the thoughts, and Everett gave me > the > push to work it. Everett suggested looking for a single row --- it didn't > help > the query plan, or the speed. So I got to thinking about Fernando's comment > about the to_char casting. > > Guess what? (I'm not going to brign back the SQL, I'm going to paraphrase > it): > Changing > vid--varchar = to_char(vid--integer) > to > vid--varchar = vid--integer::varchar > solved all the problems!!! > > Yep, use a CAST instead of the function. No clue why to_char causes the > query > to go nuts, but using the CAST certainly makes everything work like it > should. > > I will show the new explain plan on Tuesaday when I'm back at work (they're > about to turn the lights out on me). And I'll show Fernando a run with the > *bad* way but with no PDQ setting. > > I do totally appreciate all the help you guys threw on this! Thanks for > being > there! > > Michael Hoffman > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --bcaec548a723a635ae04e38d7fed
Run onstat -g ses and send the output.
Also
onstat -g stk
onstat -g tpf
for each thread.
Also run xtree and see what the query is doing.
Regards,
David.
On 08 August 2013 at 04:18 Fernando Nunes <domusonline@gmail.com> wrote:
> Another question... Since the query is running with PDQ... How many query
> do you have running simultaneously? Did you check if they're waiting on MGM
> resources (onstat -g mgm)
> Thanks
>
> On Thu, Aug 8, 2013 at 4:07 AM, Fernando Nunes <domusonline@gmail.com>wrote:
>
> > I still would like the answer to the previous question, but I have another
> > and found something interesting.
> > The new question is: What is "voter_inquiry"? Do you have synonyms or
> > views?
> >
> > Please try to run the query without PDQ (set it to 0) and check the
> > difference in running time and query plan.
> > I'm able to get weird values in explain plan, but the running time did not
> > change (my environment is not ideal for performance tests...)
> >
> > It would be nice if you can open a PMR and post the number. I can provide a
> > test case showing this (bad explain stats). I couldn't find any perfect
> > match in our KB, but I'd have to search more carefully before assuming it's
> > new or known. In any case I cannot reproduce the slowness... Just the weird
> > values.
> > Regards
> >
> > On Wed, Aug 7, 2013 at 11:24 PM, Fernando Nunes <domusonline@gmail.com
> > >wrote:
> >
> > > I did not explain myself properly...
> > > I was asking you to check the count of rows of the join beteewn the rows
> > in
> > > the updated table and s1. ideally it should be near 179... check please.
> > I
> > > don't expect a surprise... but we need to be sure
> > > On Aug 7, 2013 7:18 PM, "MICHAEL HOFFMAN" <mrh@panix.com> wrote:
> > >
> > > > Fernando,
> > > > we all know there is no ideal world. In this case, there are 192 rows
> > > > returned, but the cancel_dates are the same for each duplicate, so the
> > > > "unique" will work and return only one value.
> > > > Still, even with duplicates, that "set subquery" should only be called
> > > 179
> > > > times, and in most cases only find one matching row. Only for 13 rows
> > > will
> > > > a
> > > > second row be located, and then the Unique would have to be enforced.
> > > >
> > > > None of this explains why the query is so slow.
> > > >
> > > > Mike
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --089e0160d2f4aba40f04e362ff2b
> > >
> > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --
> > Fernando Nunes
> > Portugal
> >
> > http://informix-technology.blogspot.com
> > My email works... but I don't check it frequently...
> >
> > --001a11c230163ba38504e366f49f
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...
>
> --20cf3071c8862e535504e3671d85
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
PDQ=1 is not a useless setting, it allows parallel scans. Regards, David. On 08 August 2013 at 20:52 MICHAEL HOFFMAN <mrh@panix.com> wrote: > Fernando, > I'll cover all your answers in one response: > 1) voter_inquiry is the same table as v_inq. No synonym -- I editted the > tables and columns in the Explain Plan to hide the true nature of the process. > I just missed that line. It should have changed to read 'v_inq'. > > 2) There are exactly 179 rows matched between the 2 tables. Not distinctly, > there are 179 records is v_inq which match 192 rows in s1. (s1 obviously > contains some duplicates) > > 3) PDQ is set to 1, however it doesn't run parallel threads. This is do to the > overall config settings. We haven't gotten around to running PDQ. The setting > in this case was to confirm the *lack* of PDQ ability, and that proved true. > It is a useless setting. > > Check out my other followup post --- one of your earlier posts led to a > solution, and brought up another interesting Informix issue. > > Thanks, > Mike > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Thanks David. I am ashamed to admit, but I am still a PDQ novice.