You have upgraded to Informix 11.70.xC3 or higher version. After upgrade encountered some of your queries running slow. The common observation, a query was using index path prior to upgrade and running faster. After an upgrade the same query is doing a sequential scan. Using an index directive the query uses the index and runs much faster than the sequential scan. However, the index path has a higher estimated cost associated with it than the sequential scan.
Also, after an upgrade it could be seen that the optimizer chooses a different index in the newer version of Informix than it did in the prior version and causing the query to run slower.
A newer costing functionality was added in 11.70.FC3 and higher in the 11.70 family. This functionality was added to account for the seek time required to traverse an index (especially large indexes). There were situations where queries using an index would take longer to return than a sequential scan because of all the I/O involved.
You can control the new costing functionality using the OPT_SEEK_FACTOR configuration parameter. This parameter allows to set the "weight" of an I/O seek cost. The range is 0 to 25 and default is 6. Making it lower causes the seek cost to be lower which lowers the estimated cost of using an index path.
You can revert to the old costing method by setting OPT_SEEK_FACTOR to 0 in the ONCONFIG file.
- Grant Flander