Informix has a huge cost for query when using an index


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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

what is the cardinality of the index? Having an index does not guarantee speedy queries if the index has a low number of distinct values. You also have to base your query on a predicate that the optimiser can use, for example if your where clause is where colname=1, and colname is not part of the index, then you will get sequential or table scan.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jamesnoeAuthor Commented:

It turns out that the problem was because of the data.  The index was not unique (I knew that - but assumed it wasn't the problem).  I asked the customer to do a grouped count select on each of the indexed columns and found that there were over 11,000,000 records in one count - thereby making the index useless.

The problem actually appears to be an Informix one though as the optimiser should have use an earlier part of the where clause and not used this OR statement - taking the OR part out made the estimated cost 5.

We have some customers that will keep the OR statement and this is now controlled by a configuration flag
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.