Aggregates

Over the weekend we migrated production to v11.70.FC4 from 11.50.FC5W2 ( OS HP-UX 11.31 ia64). On v11.70 some queries are running slow ( minutes compared to 2 secs in v11.50). SET Explain does INDEX scans on both, though different indexes are used in the subselect with the aggregrate. The common thread is that these queries do aggregates. Here is a sample query: select * from fi_debt_price dp where dp.settl_date = (select max(dpw.settl_date) from fi_debt_price dpw where dpw.nike_secrty_id = dp.nike_secrty_id and dpw.settl_date <= :settl_date(#(1/25/2012))) and dp.as_of_date = (select max(dpw.as_of_date) from fi_debt_price dpw where dpw.nike_secrty_id = dp.nike_secrty_id and dpw.as_of_date <= :as_of_date(#(1/20/2012))) and nike_secrty_id > 94000 SET EXPLAIN (v11.70): Subquery: --------- Estimated Cost: 291 Estimated # of Rows Returned: 1 1) muralip.dpw: INDEX PATH (1) Index Name: dba.ev_debt_price_ix Index Keys: settl_date as_of_date nike_secrty_id (Key-Only) (Revers e) (Aggregate) (Serial, fragments: ALL) Lower Index Filter: muralip.dpw.settl_date <= 01/25/2012 Index Key Filters: (muralip.dpw.nike_secrty_id = muralip.dp.nike_sec rty_id ) Subquery: --------- Estimated Cost: 544 Estimated # of Rows Returned: 1 1) muralip.dpw: INDEX PATH Filters: muralip.dpw.nike_secrty_id = muralip.dp.nike_secrty_id (1) Index Name: dba.ev_debt_price_as_of_ix Index Keys: as_of_date (Reverse) (Aggregate) (Serial, fragments: A LL) Lower Index Filter: muralip.dpw.as_of_date <= 01/20/2012 SET EXPLAIN (v11.50): Subquery: --------- Estimated Cost: 717 Estimated # of Rows Returned: 1 1) muralip.dpw: INDEX PATH Filters: muralip.dpw.settl_date <= 01/25/2012 (1) Index Name: dba.ev_debt_price_secrty_ix Index Keys: nike_secrty_id (Serial, fragments: ALL) Lower Index Filter: muralip.dpw.nike_secrty_id = muralip.dp.nike_secrty_id Subquery: --------- Estimated Cost: 717 Estimated # of Rows Returned: 1 1) muralip.dpw: INDEX PATH Filters: muralip.dpw.as_of_date <= 01/20/2012 (1) Index Name: dba.ev_debt_price_secrty_ix Index Keys: nike_secrty_id (Serial, fragments: ALL) Lower Index Filter: muralip.dpw.nike_secrty_id = muralip.dp.nike_secrty_id I have tried these simple steps: 1. Upd stats high on table, indexes; low on table drop distributions 2. Drop-reload table, stats 3. onmode -wf DS_NONPDQ_QUERY_MEM>= 25% DS_TOTAL_MEMORY Not sure what to try next.

Questions and suggestions: - Did you drop all distributions, rebuild them, then recompile all stored procedures after the upgrade to 11.70? - This looks like it is part of a stored procedure. Did you recompile the procedure recently? - Exactly what sequence and order of UPDATE STATISTICS commands did you run? - The SET EXPLAIN output shows an estimated row count of one. What is the actual number of rows returned? - Try this version of the query using derived tables: select * from fi_debt_price dp, ( select nike_secrty_id, max( dpw.settl_date ) as settl_date from fi_debt_price dpw where dpw.settl_date <= :settl_date(#(1/25/2012)) ) as max_settle, ( select nike_secrty_id, max(dpw.as_of_date) as as_of_date from fi_debt_price dpw where dpw.as_of_date <= :as_of_date(#(1/20/2012)) ) as max_as_of where dp.nike_secrty_id = max_settle.nike_secrty_id and dp.nike_secrty_id = max_as_of.nike_secrty_id and dp.settl_date = max_settle.settle_date and dp.as_of_date = max_as_of.as_of_date and dp.nike_secrty_id > 94000; 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 Tue, Jan 24, 2012 at 4:40 PM, MURALI PAZHAYANNUR < pmurali@ftportfolios.com> wrote: > Over the weekend we migrated production to v11.70.FC4 from 11.50.FC5W2 ( OS > HP-UX 11.31 ia64). > > On v11.70 some queries are running slow ( minutes compared to 2 secs in > v11.50). SET Explain does INDEX scans on both, though different indexes are > used in the subselect with the aggregrate. > > The common thread is that these queries do aggregates. Here is a sample > query: > > select * from fi_debt_price dp > where dp.settl_date = > (select max(dpw.settl_date) > from fi_debt_price dpw > where dpw.nike_secrty_id = dp.nike_secrty_id > and dpw.settl_date <= :settl_date(#(1/25/2012))) > and dp.as_of_date = > (select max(dpw.as_of_date) > from fi_debt_price dpw > where dpw.nike_secrty_id = dp.nike_secrty_id > and dpw.as_of_date <= :as_of_date(#(1/20/2012))) > and nike_secrty_id > 94000> > SET EXPLAIN (v11.70):> > Subquery: > > --------- > > Estimated Cost: 291 > > Estimated # of Rows Returned: 1 > > 1) muralip.dpw: INDEX PATH > > (1) Index Name: dba.ev_debt_price_ix > > Index Keys: settl_date as_of_date nike_secrty_id (Key-Only) (Revers > e) (Aggregate) (Serial, fragments: ALL) > > Lower Index Filter: muralip.dpw.settl_date <= 01/25/2012 > > Index Key Filters: (muralip.dpw.nike_secrty_id = muralip.dp.nike_sec > rty_id ) > > Subquery: > > --------- > > Estimated Cost: 544 > > Estimated # of Rows Returned: 1 > > 1) muralip.dpw: INDEX PATH > > Filters: muralip.dpw.nike_secrty_id = muralip.dp.nike_secrty_id > > (1) Index Name: dba.ev_debt_price_as_of_ix > > Index Keys: as_of_date (Reverse) (Aggregate) (Serial, fragments: A > LL) > > Lower Index Filter: muralip.dpw.as_of_date <= 01/20/2012 > > SET EXPLAIN (v11.50):> > Subquery: > > --------- > > Estimated Cost: 717 > > Estimated # of Rows Returned: 1 > > 1) muralip.dpw: INDEX PATH > > Filters: muralip.dpw.settl_date <= 01/25/2012 > > (1) Index Name: dba.ev_debt_price_secrty_ix > > Index Keys: nike_secrty_id (Serial, fragments: ALL) > > Lower Index Filter: muralip.dpw.nike_secrty_id = muralip.dp.nike_secrty_id > > Subquery: > > --------- > > Estimated Cost: 717 > > Estimated # of Rows Returned: 1 > > 1) muralip.dpw: INDEX PATH > > Filters: muralip.dpw.as_of_date <= 01/20/2012 > > (1) Index Name: dba.ev_debt_price_secrty_ix > > Index Keys: nike_secrty_id (Serial, fragments: ALL) > > Lower Index Filter: muralip.dpw.nike_secrty_id = muralip.dp.nike_secrty_id > > I have tried these simple steps: > > 1. Upd stats high on table, indexes; low on table drop distributions > 2. Drop-reload table, stats > 3. onmode -wf DS_NONPDQ_QUERY_MEM>= 25% DS_TOTAL_MEMORY > > Not sure what to try next. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340f9da66e7704b74db33d

Very interesting situation.... But before I loose myself in my thoughts, can you make sure you run proper (no offense) update stats on the tables (something like Art's dostats). If not,can you change the queries? I know that's not what you want to hear/read, but if it's possible I'd advise you to do it at least as a workaround... The change I propose is something to make the engine avoid using the current indexes. For example apply the function DATE() to the date fields in the sub-query equalities: select * from fi_debt_price dp where dp.settl_date = ( select max(dpw.settl_date) from fi_debt_price dpw where dpw.nike_secrty_id = dp.nike_secrty_id and DATE(dpw.settl_date) <= :settl_date(#(1/25/2012)) ) and dp.as_of_date = ( select max(dpw.as_of_date) from fi_debt_price dpw where dpw.nike_secrty_id = dp.nike_secrty_id and DATE(dpw.as_of_date) <= :as_of_date(#(1/20/2012)) ) and nike_secrty_id > 94000 Now, my thoughts: I'm assuming this is a prepared query? and ":as_of_date(#(1/20/2012))" is a form of expressing possible values? If that's the case you're asking the engine to do something tricky: It must calculate the query plan without knowing the values you will use. Don't get me wrong... That's what happens in all prepared statements... But your case has another tricky detail: You're using date/datetimes fields... and apparently you're giving it values that are probably true for most cases. <disclaimer on> Yes, I have the feeling that the engine gives too much preference to indexes with datetimes. Yes, in a recent upgrade to 11.7 I got the feeling this has become more evident <disclaimer off> Having said this, I did search for reported problems and what I found was the opposite. This "trend" (in general, not in 11.7 specifically) was recognized and some attempt to overcome it was created (but I believe it's only scheduled for xC5). So, in short I found no facts that supported my "feeling" that 11.7 was more sensitive to this than previous versions... One thing you can try before running the stats: onmode -wf USTLOW_SAMPLE=0 Finally, although I don't know the table schema and distributions, the most effective indexes for that query would probably be: nike_secrty_id, as_of_date DESC and nike_secrty_id, settl_date DESC Naturally this is a quick guess that would require validation On Tue, Jan 24, 2012 at 9:40 PM, MURALI PAZHAYANNUR < pmurali@ftportfolios.com> wrote: > Over the weekend we migrated production to v11.70.FC4 from 11.50.FC5W2 ( OS > HP-UX 11.31 ia64). > > On v11.70 some queries are running slow ( minutes compared to 2 secs in > v11.50). SET Explain does INDEX scans on both, though different indexes are > used in the subselect with the aggregrate. > > The common thread is that these queries do aggregates. Here is a sample > query: > > select * from fi_debt_price dp > where dp.settl_date = > (select max(dpw.settl_date) > from fi_debt_price dpw > where dpw.nike_secrty_id = dp.nike_secrty_id > and dpw.settl_date <= :settl_date(#(1/25/2012))) > and dp.as_of_date = > (select max(dpw.as_of_date) > from fi_debt_price dpw > where dpw.nike_secrty_id = dp.nike_secrty_id > and dpw.as_of_date <= :as_of_date(#(1/20/2012))) > and nike_secrty_id > 94000> > SET EXPLAIN (v11.70):> > Subquery: > > --------- > > Estimated Cost: 291 > > Estimated # of Rows Returned: 1 > > 1) muralip.dpw: INDEX PATH > > (1) Index Name: dba.ev_debt_price_ix > > Index Keys: settl_date as_of_date nike_secrty_id (Key-Only) (Revers > e) (Aggregate) (Serial, fragments: ALL) > > Lower Index Filter: muralip.dpw.settl_date <= 01/25/2012 > > Index Key Filters: (muralip.dpw.nike_secrty_id = muralip.dp.nike_sec > rty_id ) > > Subquery: > > --------- > > Estimated Cost: 544 > > Estimated # of Rows Returned: 1 > > 1) muralip.dpw: INDEX PATH > > Filters: muralip.dpw.nike_secrty_id = muralip.dp.nike_secrty_id > > (1) Index Name: dba.ev_debt_price_as_of_ix > > Index Keys: as_of_date (Reverse) (Aggregate) (Serial, fragments: A > LL) > > Lower Index Filter: muralip.dpw.as_of_date <= 01/20/2012 > > SET EXPLAIN (v11.50):> > Subquery: > > --------- > > Estimated Cost: 717 > > Estimated # of Rows Returned: 1 > > 1) muralip.dpw: INDEX PATH > > Filters: muralip.dpw.settl_date <= 01/25/2012 > > (1) Index Name: dba.ev_debt_price_secrty_ix > > Index Keys: nike_secrty_id (Serial, fragments: ALL) > > Lower Index Filter: muralip.dpw.nike_secrty_id = muralip.dp.nike_secrty_id > > Subquery: > > --------- > > Estimated Cost: 717 > > Estimated # of Rows Returned: 1 > > 1) muralip.dpw: INDEX PATH > > Filters: muralip.dpw.as_of_date <= 01/20/2012 > > (1) Index Name: dba.ev_debt_price_secrty_ix > > Index Keys: nike_secrty_id (Serial, fragments: ALL) > > Lower Index Filter: muralip.dpw.nike_secrty_id = muralip.dp.nike_secrty_id > > I have tried these simple steps: > > 1. Upd stats high on table, indexes; low on table drop distributions > 2. Drop-reload table, stats > 3. onmode -wf DS_NONPDQ_QUERY_MEM>= 25% DS_TOTAL_MEMORY > > Not sure what to try next. > > > > ******************************************************************************* > 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... --485b397dd69585b42e04b74e4bf0

These were great suggestions. Art's recommendation improved the query time from minutes to 30 secs. Fernando's tips beat that. The query now runs in < 2 secs. I tried 3 approaches listed below and implemented the one that made most sense based on what SET EXPLAIN showed. 1. Informix Directives to force the use of a specific index. I chose this based on what the SET EXPLAIN from v11.50 told me 2. Fernando's updated query using the date() function. This change must be effective for this version. 3. Dropped and created the Index as Fernando suggested, with the date column desc. Then run the original slow query. It was important to create the index on (nike_secrty_id, date field desc) as the 'date field desc' alone was not sufficient. I implemented option 3 as it was least disruptive to the application.

Great news... I think we could do better with the date/time indexes... But it really is very hard to show this in practice. Regards. On Thu, Jan 26, 2012 at 6:22 PM, MURALI PAZHAYANNUR < pmurali@ftportfolios.com> wrote: > These were great suggestions. Art's recommendation improved the query time > from minutes to 30 secs. Fernando's tips beat that. The query now runs in > < 2 > secs. I tried 3 approaches listed below and implemented the one that made > most > sense based on what SET EXPLAIN showed. > > 1. Informix Directives to force the use of a specific index. I chose this > based on what the SET EXPLAIN from v11.50 told me > 2. Fernando's updated query using the date() function. This change must be > effective for this version. > 3. Dropped and created the Index as Fernando suggested, with the date > column > desc. Then run the original slow query. It was important to create the > index > on (nike_secrty_id, date field desc) as the 'date field desc' alone was not > sufficient. > > I implemented option 3 as it was least disruptive to the application. > > > > ******************************************************************************* > 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... --485b397dd69563d41a04b773135f