IDS 7.31.FD7
Solaris 9


We have a query that rans in <1 sec using a 2 column composite index (id &
date). I fragmented the table (it was getting close to the 16777215 page
limit) and added another index (date). The query now takes >5 mins to run, a
set explain confirmed the optimiser was using the new index. Adding an
optimiser directive to use the original index solved the problem.

BTW Update Statistics was run after the fragmentation and index build.

My question is this:
Why would the optimiser use the new index on date only when using the index
on id and date is obviously better?

I have a vague recollection about Informix selecting the latest created
index when a column appears in more than one index but can't remember which
version of OnLine it was.

All observations gratefully received




Regards

Colin

There are 10 types of people in the world, those that understand binary and
those that don't
sending to informix-list

1/ Bug in the optimiser

2/ The Update Statistics strategy you used did not obtain appropriate
information to provide the optimser with enough detail to choose the
right index

Colin Dawson wrote:
> IDS 7.31.FD7
> Solaris 9
>
>
> We have a query that rans in <1 sec using a 2 column composite index (id &
> date). I fragmented the table (it was getting close to the 16777215 page
> limit) and added another index (date). The query now takes >5 mins to run, a
> set explain confirmed the optimiser was using the new index. Adding an
> optimiser directive to use the original index solved the problem.
>
> BTW Update Statistics was run after the fragmentation and index build.
>
> My question is this:
> Why would the optimiser use the new index on date only when using the index
> on id and date is obviously better?
>
> I have a vague recollection about Informix selecting the latest created
> index when a column appears in more than one index but can't remember which
> version of OnLine it was.
>
> All observations gratefully received
>
>
>
>
> Regards
>
> Colin
>
> There are 10 types of people in the world, those that understand binary and
> those that don't
> sending to informix-list


One possible reason is some kind of bug.
Another has to do with statistics... Datetimes tend to cause this. You
probably have a lot more occurrences with a certain id than within a
certain time interval.

You could try to increment/decrment the detail of the histograms created
for the second index...

It has to do with estimated costs, and estimated number of rows... as
the name suggests they're just an estimate... not the real thing and
this can lead to situations like this.

Adding 0 seconds to the datetime column may also work around the problem.

Regards.

Colin Dawson wrote:
> Colin Dawson wrote:
> IDS 7.31.FD7
> Solaris 9
>
>
> We have a query that rans in <1 sec using a 2 column composite index (id &
> date). I fragmented the table (it was getting close to the 16777215 page
> limit) and added another index (date). The query now takes >5 mins to run, a
> set explain confirmed the optimiser was using the new index. Adding an
> optimiser directive to use the original index solved the problem.
>
> BTW Update Statistics was run after the fragmentation and index build.
>
> My question is this:
> Why would the optimiser use the new index on date only when using the index
> on id and date is obviously better?
>
> I have a vague recollection about Informix selecting the latest created
> index when a column appears in more than one index but can't remember which
> version of OnLine it was.
>
> All observations gratefully received
>
>> The 1st query was after drop and creae of both indexes, 2nd query is after
>> update statistics high for 1st column in all indices and update statistics
>> low for all other columns in all indices
>>
>> QUERY:
>> ------
[snip]
>> where
>> j.cr_date between '2005-09-20 08:00:00' AND
>> '2005-09-20 16:00:00'
^^^^^^^^^^^^^^^^^^^^^

This is a datetime year to second, not a date.

Because the number of values this can take is so finely grained, the
optimiser thinks that it must be highly selective.

--
rh