Re[5]: Optimizer Flaws in 7.23 - Simple example

First of all Folks please do not truncate previous threads (no matter how long and how irrelevant YOU may think they are). It is important for people to get the full picture without scrambling to find previous emails. They may at their own leisure ignore the previous threads but give them that option. Thanks Tony for responding. Now in answer to Tony: The "id" column is indexed but if it weren't it would make no difference. The 'joined columns' is where any index would be used. Any temp table should be built after the joins are used for selection. The outer join should result in no more than 3,500 records selected (and an equivalent COST). (The nbr of recs in dunsoft + dup phones). Alan Tony writes: ______________________________ Reply Separator _________________________________ Subject: Optimizer Flaws in 7.23 - Simple example Author: alan.cowan@autodesk.com (ALAN COWAN) at INTERNET Date: 8/7/97 5:26 PM Might I suggest that this bit of SQL will force a sequential scan of the dunsoft table because you have asked for unique values of a.id and there is no index on this column. To get the distinct values the engine will need to build a temporary table. If you put an index on a.id it will go faster. The outer join combined with the 'or' means that the number of possible joined rows that must be looked at is very high, not least because if a dunau row matched both on zip and tel it would appear twice in the output. Outer joins, particularly when associated with and or or can seriously slow the engine. I would suggested that there is very little wrong with the Informix optimiser. It would be interesting to try the same query on the same data on some other products...... The problem with these sorts of things usually lies in the design of the database tables and the types of queries we try to force through them. ______________________________ Reply Separator _________________________________ Subject: Optimizer Flaws in 7.23 - Simple example Author: alan.cowan@autodesk.com (ALAN COWAN) at INTERNET Date: 8/7/97 5:26 PM Thanks Heiko but: After: SET OPTIMIZATION FIRST_ROWS; DBACCESS: QUERY: (RESPONSE TIME OPTIMIZATION) <<<<< This is in the sqexplain.out ------ select DISTINCT a.id from dunsoft a, OUTERdunau b where b.zip = a.zip or b.tel = a.tel Estimated Cost: 36716488 <<<<<<<<<<<<<< No better Estimated # of Rows Returned: 1 <<<< Should be >= nbr rows in dunsoft It's an OUTER. 1) eadmin.a: SEQUENTIAL SCAN 2) eadmin.b: SEQUENTIAL SCAN Filters: (eadmin.b.zip = eadmin.a.zip OR eadmin.b.tel = eadmin.a.tel ) ----------------------------------------------------------------------- And in the 4gl prog: SET OPTIMIZATION FIRST_ROWS |_______________________^ | | A grammatical error has been found on line 15, character 25. | The construct is not understandable in its context. | See error number -4373. |__________________________________^ Does Informix realise this is a Chapter 11 type of bug? It is totally unacceptable for this SQL to take 7 hours. dunau 88,000 recs dunsoft 3,400 Index name Owner Type Cluster Columns i_dunau_zip eadmin dupls No zip i_dunau_tel eadmin dupls No tel i_dunsoft_zip eadmin dupls No zip i_dunsoft_tel eadmin dupls No tel Alan ______________________________ Reply Separator _______________________ Subject: Re: Optimizer Flaws in 7.23 - Simple example Author: Heiko Giesselmann <heikog@informix.com> at INTERNET Date: 8/8/97 1:32 PM Alan, though I am an Informix employee I am definitely no optimizer guru (unfortunately I also don't have time to check your test case). Anyway, you may try to use the following statement before running your sql statement: SET OPTIMIZATION FIRST_ROWS; You can switch back to the default optimization strategy using SET OPTIMIZATION ALL_ROWS; This is undocumented (at least for 7.23) and I am not sure if it will help, but it may be worth a try. Please let me know if this helps. Regards, Heiko ALAN COWAN wrote: ______________________________ Reply Separator _______________________ Subject: Re: Optimizer Flaws in 7.23 - Simple example Author: Heiko Giesselmann <heikog@informix.com> at INTERNET Date: 8/8/97 <<<<<<<<<<<<<<<<< See previous email "Serious flaws...." The Optimizer does not use indexes even if EVERY join is an indexed field. Informix 5 did not have these problems. NOTE in example there are 10,7534 Blank "tel" columns In QUERY 2: Although a DYNAMIC HASH JOIN is chosen instead of INDEX, cannot argue as cost is still low. QUERY 1: Runs forever. ------ select DISTINCT a.id from dunsoft a, OUTER dunau b where ( b.tel = a.tel or b.zip = a.zip ) <<<<<<< Extra join Estimated Cost: 36716488 <<<<<<<<<<<<<<<<<<<<<<<<<<<<< Estimated # of Rows Returned: 1 <<<<< Should Estimate 1,000 + 1) eadmin.a: SEQUENTIAL SCAN 2) eadmin.b: SEQUENTIAL SCAN <<<<<<< Should use INDEXES Filters: (eadmin.b.tel = eadmin.a.tel OR eadmin.b.fax = eadmin.a.fax ) QUERY 2: ------ select DISTINCT a.id from dunsoft a, OUTER dunau b where ( b.tel = a.tel ) <<<<<<<<<<<<<< Only 1 join Estimated Cost: 9994 Estimated # of Rows Returned: 1 1) eadmin.a: SEQUENTIAL SCAN 2) eadmin.b: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: eadmin.a.tel = eadmin.b.tel ----------------------------------------------------- Distributions: DBSCHEMA Schema Utility INFORMIX-SQL Version 7.23.UC1 Copyright (C) Informix Software, Inc., 1984-1997 Software Serial Number AAB#J989985 Distribution for eadmin.dunau.tel <<<<<<<<<<<<<<<<<<<<<<<<<<<< Constructed on 08/07/1997 High Mode, 0.500000 Resolution --- DISTRIBUTION --- ( ) 1: ( 444, 277, 0234577942 ) 2: ( 444, 291, 0425270631 ) 3: ( 444, 297, 0822441935 ) 4: ( 444, 326, 2014289363 ) 5: ( 444, 338, 2018335525 ) 6: ( 444, 349, 2033198919 ) ..... .... .... 171: ( 444, 324, 9187235423 ) 172: ( 444, 309, 9197340460 ) 173: ( 444, 354, 9414817218 ) 174: ( 444, 374, 9702481999 ) 175: ( 401, 352, 9949716606 ) --- OVERFLOW --- 1: ( 381, NULL) 2: ( 10753, ) <<<<< Blank tel Distribution for eadmin.dunau.zip <<<<<<<<<<<<<<<<<<< Constructed on 08/07/1997 High Mode, 0.500000 Resolution --- DISTRIBUTION --- ( ) 1: ( 444, 179, 01089 ) 2: ( 444, 183, 01740-1034 ) 3: ( 444, 111, 01886 ) 4: ( 444, 131, 02115 ) 5: ( 444,