Optimizer not using index

Hello, we´ve some strange problems with index selection on queries on our Digital Alpha server with Online dynamic server running (7.24 FC6) and 4GL developement tools (7.20 UD6). You can look at the example below. Only when using the select with the "where" conditions literally, the optimizer chooses the index. In the two other cases a sequential scan is done. This happens even after an UPDATE STATISTICS HIGH for the complete databases. We have similar tables which are accessed via index (over about 2 to 5 columns). The problem with the sequential scan happens not on all of these tables. Greetings, Joerg ================================================================================================================ DATABASE invekos@dge1lokal MAIN DEFINE l_bearb_id INTEGER , l_bearb CHAR(3) , l_afa SMALLINT , l_query CHAR(2000) #END DEFINE LET l_query = "SELECT bearb_id FROM r_bearb WHERE kuerzel = ? AND dienst_id = ?" PREPARE sc_bearb FROM l_query DECLARE q_bearb CURSOR FOR sc_bearb SET EXPLAIN ON LET l_bearb_id = f_get_bearbid("bau", 0) DISPLAY l_bearb_id SELECT bearb_id INTO l_bearb_id FROM r_bearb WHERE kuerzel = "bau" AND dienst_id = 0 DISPLAY l_bearb_id LET l_bearb = "bau" LET l_afa = 0 OPEN q_bearb USING l_bearb, l_afa FETCH q_bearb INTO l_bearb_id CLOSE q_bearb DISPLAY l_bearb_id END MAIN FUNCTION f_get_bearbid(p_bearb, p_afa) DEFINE l_bearb_id INTEGER , p_bearb CHAR(3) , p_afa SMALLINT #END DEFINE SELECT bearb_id INTO l_bearb_id FROM r_bearb WHERE kuerzel = p_bearb AND dienst_id = p_afa RETURN l_bearb_id END FUNCTION #f_get_bearbid QUERY: ------ select bearb_id from r_bearb where kuerzel = ? and dienst_id = ? Estimated Cost: 74 Estimated # of Rows Returned: 1 Maximum Threads: 0 1) invekos.r_bearb: SEQUENTIAL SCAN Filters: (invekos.r_bearb.kuerzel = 'bau' AND invekos.r_bearb.dienst_id = 0 ) QUERY: ------ select bearb_id from r_bearb where kuerzel = "bau" and dienst_id = 0 Estimated Cost: 1 Estimated # of Rows Returned: 1 Maximum Threads: 0 1) invekos.r_bearb: INDEX PATH (1) Index Keys: kuerzel dienst_id Lower Index Filter: (invekos.r_bearb.kuerzel = 'bau' AND invekos.r_bearb.dienst_id = 0 ) QUERY: ------ SELECT bearb_id FROM r_bearb WHERE kuerzel = ? AND dienst_id = ? Estimated Cost: 74 Estimated # of Rows Returned: 1 Maximum Threads: 0 1) invekos.r_bearb: SEQUENTIAL SCAN Filters: (invekos.r_bearb.kuerzel = 'bau' AND invekos.r_bearb.dienst_id = 0 )

Joerg Spilker wrote: > Hello, > > we´ve some strange problems with index selection on queries on our > Digital Alpha server with Online dynamic server running (7.24 FC6) and > 4GL developement tools (7.20 UD6). > > You can look at the example below. Only when using the select with the > "where" conditions literally, the optimizer chooses the index. In the > two other cases a sequential scan is done. > > This happens even after an UPDATE STATISTICS HIGH for the complete > databases. > > We have similar tables which are accessed via index (over about 2 to 5 > columns). The problem with the sequential scan happens not on all of > these tables. > > Greetings, Joerg > ================================================================================================================ > > DATABASE invekos@dge1lokal > MAIN > DEFINE > l_bearb_id INTEGER > , l_bearb CHAR(3) > , l_afa SMALLINT > , l_query CHAR(2000) > #END DEFINE > > LET l_query = "SELECT bearb_id FROM r_bearb WHERE kuerzel = ? AND > dienst_id = ?" > PREPARE sc_bearb FROM l_query > DECLARE q_bearb CURSOR FOR sc_bearb > > SET EXPLAIN ON> > LET l_bearb_id = f_get_bearbid("bau", 0) > DISPLAY l_bearb_id > > SELECT bearb_id INTO l_bearb_id FROM r_bearb > WHERE kuerzel = "bau" AND > dienst_id = 0 > DISPLAY l_bearb_id > > LET l_bearb = "bau" > LET l_afa = 0 > OPEN q_bearb USING l_bearb, l_afa > FETCH q_bearb INTO l_bearb_id > CLOSE q_bearb > > DISPLAY l_bearb_id > > END MAIN > > FUNCTION f_get_bearbid(p_bearb, p_afa) > DEFINE > l_bearb_id INTEGER > , p_bearb CHAR(3) > , p_afa SMALLINT > #END DEFINE > > SELECT bearb_id INTO l_bearb_id FROM r_bearb > WHERE kuerzel = p_bearb AND > dienst_id = p_afa > > RETURN l_bearb_id > > END FUNCTION #f_get_bearbid > > QUERY: > ------ > select bearb_id from r_bearb where kuerzel = ? and dienst_id = ?> > Estimated Cost: 74 > Estimated # of Rows Returned: 1 > Maximum Threads: 0 > > 1) invekos.r_bearb: SEQUENTIAL SCAN > > Filters: (invekos.r_bearb.kuerzel = 'bau' AND > invekos.r_bearb.dienst_id = 0 ) > > QUERY: > ------ > select bearb_id from r_bearb where kuerzel = "bau" and dienst_id = 0> > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > Maximum Threads: 0 > > 1) invekos.r_bearb: INDEX PATH > > (1) Index Keys: kuerzel dienst_id > Lower Index Filter: (invekos.r_bearb.kuerzel = 'bau' AND > invekos.r_bearb.dienst_id = 0 ) > > QUERY: > ------ > SELECT bearb_id FROM r_bearb WHERE kuerzel = ? AND dienst_id = ?> > Estimated Cost: 74 > Estimated # of Rows Returned: 1 > Maximum Threads: 0 > > 1) invekos.r_bearb: SEQUENTIAL SCAN > > Filters: (invekos.r_bearb.kuerzel = 'bau' AND > invekos.r_bearb.dienst_id = 0 ) Hi, Sometimes the optimizer's behaviour is strange. We had a similar problem when a table had several indexes defined which had some starting columns in common (.e. idx1: f1, f2, f3; idx2: f1, f2, f4; ...) The problem disappeared only after installation of IDS 7.31 and specifying OPT_GOAL = 0 (optimization for first rows) Regards -- Helmut Leininger Bull AG / Vienna Unix Support Email: h.leininger@bull.at helmut.leininger@bull.net This opinion is mine and not necessarily that of my employer. No guarantees whatsoever.

Jeorg's problem is the replaceable parameters in the 4GL statement. The optimizer has to guess at the average values for these parameters and try to determine the best query path for this average values. When the query is run with literal values for both columns the data distributions show that the index based query is faster but with the parameters the engine's best guess is that the sequential scan will be better. You can work around this in 7.24 only by adjusting the stats DOWN to MEDIUM or even LOW with no distributions (heresy I know, especially from me ;-)) or to rewrite the app to build the query with literal values instead of parameters and re-prepare it each time through. Art S. Kagel Helmut Leininger wrote: > > Joerg Spilker wrote: > > > Hello, > > > > we´ve some strange problems with index selection on queries on our > > Digital Alpha server with Online dynamic server running (7.24 FC6) and > > 4GL developement tools (7.20 UD6). > > > > You can look at the example below. Only when using the select with the > > "where" conditions literally, the optimizer chooses the index. In the > > two other cases a sequential scan is done. > > > > This happens even after an UPDATE STATISTICS HIGH for the complete > > databases. > > > > We have similar tables which are accessed via index (over about 2 to 5 > > columns). The problem with the sequential scan happens not on all of > > these tables. > > > > Greetings, Joerg > > ================================================================================================================ > > > > DATABASE invekos@dge1lokal > > MAIN > > DEFINE > > l_bearb_id INTEGER > > , l_bearb CHAR(3) > > , l_afa SMALLINT > > , l_query CHAR(2000) > > #END DEFINE > > > > LET l_query = "SELECT bearb_id FROM r_bearb WHERE kuerzel = ? AND > > dienst_id = ?" > > PREPARE sc_bearb FROM l_query > > DECLARE q_bearb CURSOR FOR sc_bearb > > > > SET EXPLAIN ON> > > > LET l_bearb_id = f_get_bearbid("bau", 0) > > DISPLAY l_bearb_id > > > > SELECT bearb_id INTO l_bearb_id FROM r_bearb > > WHERE kuerzel = "bau" AND > > dienst_id = 0 > > DISPLAY l_bearb_id > > > > LET l_bearb = "bau" > > LET l_afa = 0 > > OPEN q_bearb USING l_bearb, l_afa > > FETCH q_bearb INTO l_bearb_id > > CLOSE q_bearb > > > > DISPLAY l_bearb_id > > > > END MAIN > > > > FUNCTION f_get_bearbid(p_bearb, p_afa) > > DEFINE > > l_bearb_id INTEGER > > , p_bearb CHAR(3) > > , p_afa SMALLINT > > #END DEFINE > > > > SELECT bearb_id INTO l_bearb_id FROM r_bearb > > WHERE kuerzel = p_bearb AND > > dienst_id = p_afa > > > > RETURN l_bearb_id > > > > END FUNCTION #f_get_bearbid > > > > QUERY: > > ------ > > select bearb_id from r_bearb where kuerzel = ? and dienst_id = ?> > > > Estimated Cost: 74 > > Estimated # of Rows Returned: 1 > > Maximum Threads: 0 > > > > 1) invekos.r_bearb: SEQUENTIAL SCAN > > > > Filters: (invekos.r_bearb.kuerzel = 'bau' AND > > invekos.r_bearb.dienst_id = 0 ) > > > > QUERY: > > ------ > > select bearb_id from r_bearb where kuerzel = "bau" and dienst_id = 0> > > > Estimated Cost: 1 > > Estimated # of Rows Returned: 1 > > Maximum Threads: 0 > > > > 1) invekos.r_bearb: INDEX PATH > > > > (1) Index Keys: kuerzel dienst_id > > Lower Index Filter: (invekos.r_bearb.kuerzel = 'bau' AND > > invekos.r_bearb.dienst_id = 0 ) > > > > QUERY: > > ------ > > SELECT bearb_id FROM r_bearb WHERE kuerzel = ? AND dienst_id = ?> > > > Estimated Cost: 74 > > Estimated # of Rows Returned: 1 > > Maximum Threads: 0 > > > > 1) invekos.r_bearb: SEQUENTIAL SCAN > > > > Filters: (invekos.r_bearb.kuerzel = 'bau' AND > > invekos.r_bearb.dienst_id = 0 ) > > Hi, > > Sometimes the optimizer's behaviour is strange. We had a similar problem when a table had several indexes defined > which had some starting columns in common (.e. idx1: f1, f2, f3; idx2: f1, f2, f4; ...) > > The problem disappeared only after installation of IDS 7.31 and specifying OPT_GOAL = 0 (optimization for first > rows) > > Regards > > -- > Helmut Leininger > > Bull AG / Vienna > Unix Support > Email: h.leininger@bull.at > helmut.leininger@bull.net > > This opinion is mine and not necessarily that of my employer. > No guarantees whatsoever.

Hello Art, > Jeorg's problem is the replaceable parameters in the 4GL statement. The > optimizer has to guess at the average values for these parameters and try > to determine the best query path for this average values. so the execution path of even a prepared SELECT statement is based not on current data available when opening the cursor but on "unspecific" unformation when doing the prepare? Strange. More strange, just about two hours after i wrote this messages, i started the same test again and now all of the three examples take the INDEX PATH. We have another table with about 20000 rows where we´re doing an update and the where condition exactly meets the index rows. The optimizer does not take the index and every update on a row takes about 4 seconds :-(( Greetings, Joerg

Joerg Spilker wrote in message <37DE72B7.F02E0E60@jetsys.de>... > Hello Art, >> Jeorg's problem is the replaceable parameters in the 4GL statement. The >> optimizer has to guess at the average values for these parameters and try >> to determine the best query path for this average values. > so the execution path of even a prepared SELECT statement is based not > on current data available when opening the cursor but on "unspecific" > unformation when doing the prepare? Strange. V7.3 actually improved the situation and defers some part of the optimization if there are parameters until open time, but I have not seen any real improvement in the query paths with parameters. > More strange, just about two hours after i wrote this messages, i > started the same test again and now all of the three examples take the > INDEX PATH. > We have another table with about 20000 rows where we're doing an update > and the where condition exactly meets the index rows. The optimizer does > not take the index and every update on a row takes about 4 seconds :-(( I know you originally stated that the stats and distributions are up-to-date but this behavior just sounds like outdated or insufficiently detailed stats to me. Time to run dostats.ec or something similar. Art S. Kagel

"Art S. Kagel" wrote: Hello Art, > > More strange, just about two hours after i wrote this messages, i > > started the same test again and now all of the three examples take the > > INDEX PATH. > > > We have another table with about 20000 rows where we´re doing an update > > and the where condition exactly meets the index rows. The optimizer does > > not take the index and every update on a row takes about 4 seconds :-(( > > I know you originally stated that the stats and distributions are up-to-date > but this behavior just sounds like outdated or insufficiently detailed stats > to me. Time to run dostats.ec or something similar. yes, we really did update the statistics for the table first, then tried it for the complete database. I did run my test script immediately after the updates again with no changes (seq scan on the queries with parameters again). About some ours later i was about to prepare a message to informix and started the script again and the seq scan disappeared. We´ve more problems with IDS 7.24: Replication doesn´t work. We could setup replication but when trying to start we get messages about remote database not existing. Greetings, Joerg

Joerg Spilker wrote: > > "Art S. Kagel" wrote: > > Hello Art, [SNIP] > disappeared. We´ve more problems with IDS 7.24: Replication doesn´t > work. We could setup replication but when trying to start we get > messages about remote database not existing. The word around is that replication is not rock solid until 7.31UC2 or later. That version finally looks reliable and fast so we are actually giving it another try (gave it up with 7.14). Art S. Kagel