Strange Optimization

Hi All, We haven't had any "Why is the Optimizer using this query path?" questions in a while, so I'll throw one in. What caught me is that the optimizer is actually choosing to sequentially scan a linked table that is being aggregated -- when would that EVER be the table to drive from? Maybe Art has an easy answer, but I sure don't see one. Informix 12.1.FC4 running on Solaris 10 Here are the table stats: hist_user: 42665 rows, 620 size, index on hu_id (serial); other columns: ua_id, uea_id, nm_id user_ein_assoc: 40132 rows, 545 size, index on uea_id(serial), ua_id (integer), ein (varchar 10); user_acct: 40130 rows, 219 size, index on ua_id (serial), nm_id (integer); name_dtl: 1842706 rows, 2716 size, index on nm_id (serial), full_nm (BTS, varchar) Here's the query and plan: SELECT ua.ua_id, ua.user_id, uea.ein, uea.role_nm, uea.firm_nm, uea.email, MIN(hu.start_dtm) start_date, n.first_nm, n.middle_nm, n.last_nm FROM user_ein_assoc uea, user_acct ua, hist_user hu, name_dtl n WHERE uea.ein = '66-9876152' AND uea.record_status_cd = 'A' AND uea.ua_id=ua.ua_id AND ua.ua_id=hu.ua_id AND n.nm_id=ua.nm_id GROUP BY ua_id, user_id, ein, role_nm, firm_nm, email, first_nm, middle_nm, last_nm ORDER BY last_nm, first_nm ; Estimated Cost: 42577 Estimated # of Rows Returned: 1024 Temporary Files Required For: Order By Group By 1) devdba.hu: SEQUENTIAL SCAN 2) devdba.ua: INDEX PATH (1) Index Name: informix.pk_user_acct Index Keys: ua_id (Serial, fragments: ALL) Lower Index Filter: devdba.ua.ua_id = devdba.hu.ua_id NESTED LOOP JOIN 3) devdba.uea: INDEX PATH Filters: (devdba.uea.record_status_cd = 'A' AND devdba.uea.ein = '66-987 6152' ) (1) Index Name: informix.fk_ua_uae Index Keys: ua_id (Serial, fragments: ALL) Lower Index Filter: devdba.uea.ua_id = devdba.ua.ua_id NESTED LOOP JOIN 4) devdba.n: INDEX PATH (1) Index Name: informix.pk_name_dtl Index Keys: nm_id (Serial, fragments: ALL) Lower Index Filter: devdba.n.nm_id = devdba.ua.nm_id NESTED LOOP JOIN Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 hu t2 ua t3 uea t4 n type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 42665 42665 42665 00:00.38 4892 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t2 42665 40129 42665 00:01.75 0 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 42665 74931 00:02.21 16229 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t3 0 401 6453698 02:00.61 0 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 0 1395 02:02.87 36280 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t4 0 1842690 0 00:00.00 1 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 0 1396 02:02.87 37377 type rows_prod est_rows rows_cons time est_cost ------------------------------------------------------------ group 0 1025 0 02:02.87 2968 type rows_sort est_rows rows_cons time est_cost ------------------------------------------------------------ sort 0 1025 0 02:02.87 648 We see that the slowest table isn't the Sequential Scan 'hu' table, but it's the 'uea' table. Interestingly enough, removing the 'hu' table (and associated aggregate) from the query, and the Optimizer correctly chooses to drive off the 'uea' table, and utilizes the index for ein: 1) devdba.uea: INDEX PATH Filters: devdba.uea.record_status_cd = 'A' (1) Index Name: informix.uea_ein_idx Index Keys: ein (Serial, fragments: ALL) Lower Index Filter: devdba.uea.ein = '66-9876152' Update statistics has been run on all table. Any thoughts on why the Optimizer is working this way, and how do fix the query to work efficiently? Thanks, Michael Hoffman

Michael: OK, I CAN tell you why the optimizer chose the path it chose and there are several reasons that all add up: - There are no indexes on the uea table on the filter columns ein and record_status_cd. The probably MEDIUM mode distributions on those columns have led the optimizer to guess that there are only about 401 rows that match these combined filters. A sequential scan on a 42000 row table to filter out over 99% of the rows when only 3 rows fit on a page is too costly. - The nested loop join on uea, however, is adding up to 6 IOs per row compared and since the number of rows in eau and in the joined results of ua and hu have nearly the same count it looks like the engine had to join 42000+ rows using index lookups which is why it took a bit over 2 minutes to complete that second join. - Eliminating hu from the query changes the whole game. The initial join has to scan either eau or ua which again have about the same number of rows so basically a toss up ignoring the filters. However by scanning eau and joining to eu the optimizer knows that it will only have to join about 401 times versus 42000 joins if it scans eu. No brainer. Comparing this back to the original query is an apples to shoes comparison. You can't do that and gain any understanding of what happened. Best to play with the original query if you want to understand. What to do: 1. You could add an ORDERED optimizer directive to the original query so that it will scan the eau table first (since it is listed first in the FROM clause). 2. You can try reducing the cost that the optimizer places on index lookups by setting OPT_SEEK_FACTOR to a lower value in the ONCONFIG file (the default is 6 if it is not present) which will cause the optimizer to behave more like the v11.50 optimizer did (earlier optimizer releases did not take index lookup cost into consideration - so setting it to zero reverts the optimizer to 11.50 levels). 3. You could add singleton indexes on the two filter columns and enable multi-index scanning so the engine will start with an indexed scan of eau. 4. Improve the quality of the data distributions on the filter columns to HIGH or MEDIUM with a higher SAMPLING SIZE or RESOLUTION or both. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 Mon, May 1, 2017 at 5:16 PM, MICHAEL HOFFMAN <offdisc@gmail.com> wrote: > Hi All, > We haven't had any "Why is the Optimizer using this query path?" questions > in > a while, so I'll throw one in. > What caught me is that the optimizer is actually choosing to sequentially > scan > a linked table that is being aggregated -- when would that EVER be the > table > to drive from? > Maybe Art has an easy answer, but I sure don't see one. > > Informix 12.1.FC4 running on Solaris 10 > > Here are the table stats: > hist_user: 42665 rows, 620 size, index on hu_id (serial); other columns: > ua_id, uea_id, nm_id > user_ein_assoc: 40132 rows, 545 size, index on uea_id(serial), ua_id > (integer), ein (varchar 10); > user_acct: 40130 rows, 219 size, index on ua_id (serial), nm_id (integer); > name_dtl: 1842706 rows, 2716 size, index on nm_id (serial), full_nm (BTS, > varchar) > > Here's the query and plan: > SELECT > ua.ua_id, ua.user_id, uea.ein, uea.role_nm, uea.firm_nm, uea.email, > MIN(hu.start_dtm) start_date, > n.first_nm, n.middle_nm, n.last_nm > FROM user_ein_assoc uea, user_acct ua, > hist_user hu, > name_dtl n > WHERE uea.ein = '66-9876152' > AND uea.record_status_cd = 'A' > AND uea.ua_id=ua.ua_id > AND ua.ua_id=hu.ua_id > AND n.nm_id=ua.nm_id > GROUP BY ua_id, user_id, ein, role_nm, firm_nm, email, > > first_nm, middle_nm, last_nm > ORDER BY last_nm, first_nm > ; > > Estimated Cost: 42577 > Estimated # of Rows Returned: 1024 > Temporary Files Required For: Order By Group By > > 1) devdba.hu: SEQUENTIAL SCAN > > 2) devdba.ua: INDEX PATH > > (1) Index Name: informix.pk_user_acct > > Index Keys: ua_id (Serial, fragments: ALL) > > Lower Index Filter: devdba.ua.ua_id = devdba.hu.ua_id > NESTED LOOP JOIN > > 3) devdba.uea: INDEX PATH > > Filters: (devdba.uea.record_status_cd = 'A' AND devdba.uea.ein = '66-987 > 6152' ) > > (1) Index Name: informix.fk_ua_uae > > Index Keys: ua_id (Serial, fragments: ALL) > > Lower Index Filter: devdba.uea.ua_id = devdba.ua.ua_id > NESTED LOOP JOIN > > 4) devdba.n: INDEX PATH > > (1) Index Name: informix.pk_name_dtl > > Index Keys: nm_id (Serial, fragments: ALL) > > Lower Index Filter: devdba.n.nm_id = devdba.ua.nm_id > NESTED LOOP JOIN > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 hu > t2 ua > t3 uea > t4 n > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 42665 42665 42665 00:00.38 4892 > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t2 42665 40129 42665 00:01.75 0 > > type rows_prod est_rows time est_cost > ------------------------------------------------- > nljoin 42665 74931 00:02.21 16229 > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t3 0 401 6453698 02:00.61 0 > > type rows_prod est_rows time est_cost > ------------------------------------------------- > nljoin 0 1395 02:02.87 36280 > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t4 0 1842690 0 00:00.00 1 > > type rows_prod est_rows time est_cost > ------------------------------------------------- > nljoin 0 1396 02:02.87 37377 > > type rows_prod est_rows rows_cons time est_cost > ------------------------------------------------------------ > group 0 1025 0 02:02.87 2968 > > type rows_sort est_rows rows_cons time est_cost > ------------------------------------------------------------ > sort 0 1025 0 02:02.87 648 > > We see that the slowest table isn't the Sequential Scan 'hu' table, but > it's > the 'uea' table. Interestingly enough, removing the 'hu' table (and > associated > aggregate) from the query, and the Optimizer correctly chooses to drive off > the 'uea' table, and utilizes the index for ein: > 1) devdba.uea: INDEX PATH > > Fi

Other ideas: - Replace the join to the eau table with a derived table that has the filtered results from eau. That will force a join of 0 rows (but still the estimated join to 401 rows) which may convince the optimizer to reorder the tables. - Join eau to a derived table that results from joining the remaining three tables. All of these ideas will get you to examine many of the alternative queries that will give you the desired results. One of these or some other form of the query will be the best performer and it may NOT be the one you think it should be. Remember Kagel's First Law of SQL: Any SELECT statement can be rewritten many different ways and return the same results. If you haven't tried them all you may not be using the best performer! Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 Mon, May 1, 2017 at 5:50 PM, Art Kagel <art.kagel@gmail.com> wrote: > Michael: > > OK, I CAN tell you why the optimizer chose the path it chose and there are > several reasons that all add up: > > - There are no indexes on the uea table on the filter columns ein and > record_status_cd. The probably MEDIUM mode distributions on those columns > have led the optimizer to guess that there are only about 401 rows that > match these combined filters. A sequential scan on a 42000 row table to > filter out over 99% of the rows when only 3 rows fit on a page is too > costly. > - The nested loop join on uea, however, is adding up to 6 IOs per row > compared and since the number of rows in eau and in the joined results of > ua and hu have nearly the same count it looks like the engine had to join > 42000+ rows using index lookups which is why it took a bit over 2 minutes > to complete that second join. > - Eliminating hu from the query changes the whole game. The initial > join has to scan either eau or ua which again have about the same number of > rows so basically a toss up ignoring the filters. However by scanning eau > and joining to eu the optimizer knows that it will only have to join about > 401 times versus 42000 joins if it scans eu. No brainer. Comparing this > back to the original query is an apples to shoes comparison. You can't do > that and gain any understanding of what happened. Best to play with the > original query if you want to understand. > > What to do: > > 1. You could add an ORDERED optimizer directive to the original query > so that it will scan the eau table first (since it is listed first in the > FROM clause). > 2. You can try reducing the cost that the optimizer places on index > lookups by setting OPT_SEEK_FACTOR to a lower value in the ONCONFIG file > (the default is 6 if it is not present) which will cause the optimizer to > behave more like the v11.50 optimizer did (earlier optimizer releases did > not take index lookup cost into consideration - so setting it to zero > reverts the optimizer to 11.50 levels). > 3. You could add singleton indexes on the two filter columns and > enable multi-index scanning so the engine will start with an indexed scan > of eau. > 4. Improve the quality of the data distributions on the filter columns > to HIGH or MEDIUM with a higher SAMPLING SIZE or RESOLUTION or both. > > Art > > > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.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 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 Mon, May 1, 2017 at 5:16 PM, MICHAEL HOFFMAN <offdisc@gmail.com> wrote: > >> Hi All, >> We haven't had any "Why is the Optimizer using this query path?" >> questions in >> a while, so I'll throw one in. >> What caught me is that the optimizer is actually choosing to sequentially >> scan >> a linked table that is being aggregated -- when would that EVER be the >> table >> to drive from? >> Maybe Art has an easy answer, but I sure don't see one. >> >> Informix 12.1.FC4 running on Solaris 10 >> >> Here are the table stats: >> hist_user: 42665 rows, 620 size, index on hu_id (serial); other columns: >> ua_id, uea_id, nm_id >> user_ein_assoc: 40132 rows, 545 size, index on uea_id(serial), ua_id >> (integer), ein (varchar 10); >> user_acct: 40130 rows, 219 size, index on ua_id (serial), nm_id (integer); >> name_dtl: 1842706 rows, 2716 size, index on nm_id (serial), full_nm (BTS, >> varchar) >> >> Here's the query and plan: >> SELECT >> ua.ua_id, ua.user_id, uea.ein, uea.role_nm, uea.firm_nm, uea.email, >> MIN(hu.start_dtm) start_date, >> n.first_nm, n.middle_nm, n.last_nm >> FROM user_ein_assoc uea, user_acct ua, >> hist_user hu, >> name_dtl n >> WHERE uea.ein = '66-9876152' >> AND uea.record_status_cd = 'A' >> AND uea.ua_id=ua.ua_id >> AND ua.ua_id=hu.ua_id >> AND n.nm_id=ua.nm_id >> GROUP BY ua_id, user_id, ein, role_nm, firm_nm, email, >> >> first_nm, middle_nm, last_nm >> ORDER BY last_nm, first_nm >> ; >> >> Estimated Cost: 42577 >> Estimated # of Rows Returned: 1024 >> Temporary Files Required For: Order By Group By >> >> 1) devdba.hu: SEQUENTIAL SCAN >> >> 2) devdba.ua: INDEX PATH >> >> (1) Index Name: informix.pk_user_acct >> >> Index Keys: ua_id (Serial, fragments: ALL) >> >> Lower Index Filter: devdba.ua.ua_id = devdba.hu.ua_id >> NESTED LOOP JOIN >> >> 3) devdba.uea: INDEX PATH >> >> Filters: (devdba.uea.record_status_cd = 'A' AND devdba.uea.ein = '66-987 >> 6152' ) >> >> (1) Index Name: informix.fk_ua_uae >> >> Index Keys: ua_id (Serial, fragments: ALL) >> >> Lower Index Filter: devdba.uea.ua_id = devdba.ua.ua_id >> NESTED LOOP JOIN >> >> 4) devdba.n: INDEX PATH >> >> (1) Index Name: informix.pk_name_dtl >> >> Index Keys: nm_id (Serial, fragments: ALL) >> >> Lower Index Filter: devdba.n.nm_id = devdba.ua.nm_id >> NESTED LOOP JOIN >> >> Query statistics: >> ----------------- >> >> Table map : >> ---------------------------- >> Internal name Table name >> ---------------------------- >> t1 hu >> t2 ua >> t3 uea >> t4 n >> >> type table rows_prod est_rows rows_scan time est_cost >> ------------------------------------------------------------------- >> scan t1 42665 42665 42665 00:00.38 4892 >> >> type table rows_prod est_rows rows_scan time est_cost >> -------------------------------