I have a database query that is designed to use an index (to keep it fast and efficient). The query works fine here in the office (using SET EXPLAIN ON we see the estimated cost is 6).
On our customer's site, they also have the same table structure, the same indexes, keys etc.. (confirmed by comparing the output of DBSCHEMA) - however whenever they run the query the Informix optimiser forces the database to perform a sequential scan.
We can force Informix to use the specified index (with the select --+INDEX syntax). When we do this on the customer site, we can see why the optimiser chose to use a sequential scan. The results were as follows:
Estimated Cost - Sequential Scan: 2,201,032
Estimated Cost - Forcing to use index: 9,964,601
So, I then asked the customer to run UPDATE STATISTICS HIGH ON <TABLE> This then changed the estimated costs to:
Estimated Cost - Sequential Scan: 1,999,814
Estimated Cost - Forcing to use index: 11,360,077
There are currently 12,123,678 records in the table (on the customer site, far fewer on ours, but still a considerable number).
Any ideas on how to either find out more, or how to fix this?