| Subject | Re: [firebird-support] Re: Index not picked up by optimizer |
|---|---|
| Author | Svein Erling Tysvaer |
| Post date | 2007-03-02T23:31Z |
No, not strange at all. I assumed there were no other index and that the
choice was between NATURAL and this index. There's no way an ascending
index can be used with not equal or less than (at least not in Fb 1.5),
so you cannot hope for anything more than the index being used for the
CUSTOMER comparison. It is used when you use >0 because it thinks that
will eliminate records that are less than 0, but I guess the vast
majority of your invoices fulfil this criteria anyway so there's no
point in using this index in this particular case, there's nothing
quicker than using NATURAL if you cannot eliminate any records (though a
descending index if you were looking for those few invoices with
INVOICETOTAL < 0 could be very useful). So, with your additional
information about the foreign key, I'd say that the optimizers' choice
is not strange at all and that it is the optimal plan for your query. It
cannot be further optimized in Firebird 1.5.
As for lots of records affecting the plan, well, if there are only a
handful of records (say you only put in records for customer 123 and one
or two more customers) the index selectivity would be lousy and the
optimizer would be likely to prefer another index if available. With one
million customers the selectivity is likely to be considerably better
and the likelyhood for the index to be chosen would be bigger.
Set
dr_bentonquest wrote:
choice was between NATURAL and this index. There's no way an ascending
index can be used with not equal or less than (at least not in Fb 1.5),
so you cannot hope for anything more than the index being used for the
CUSTOMER comparison. It is used when you use >0 because it thinks that
will eliminate records that are less than 0, but I guess the vast
majority of your invoices fulfil this criteria anyway so there's no
point in using this index in this particular case, there's nothing
quicker than using NATURAL if you cannot eliminate any records (though a
descending index if you were looking for those few invoices with
INVOICETOTAL < 0 could be very useful). So, with your additional
information about the foreign key, I'd say that the optimizers' choice
is not strange at all and that it is the optimal plan for your query. It
cannot be further optimized in Firebird 1.5.
As for lots of records affecting the plan, well, if there are only a
handful of records (say you only put in records for customer 123 and one
or two more customers) the index selectivity would be lousy and the
optimizer would be likely to prefer another index if available. With one
million customers the selectivity is likely to be considerably better
and the likelyhood for the index to be chosen would be bigger.
Set
dr_bentonquest wrote:
> Strange, isn't it? The index is picked up when using "greater than
> zero", but it is NOT when using "different than zero" (<> symbol) or
> "not equal to zero" (not field_name=0) or with your suggestion
> (field_name+0 <> 0)
>
> I should add this: When using "<>0" and "not field_name=0" the proper
> index is NOT picked up, but another index is. In this cases, the
> optimizer chooses an index that contains only the first field,
> CUSTOMER, because this field is a FK on the table and a RDB$FOREIGN
> index exists for it. This is the index picked up by the optimizer. But
> of course this is not optimal.
>
> I should also add that at this time the table has a few records only,
> it's part of a new project with no user-entered data yet.
>
> I know it sounds strange, but could it be that the problem solves once
> a lot of records are added to the table? I'd be surprised.
>
> Thanks,
>
> -Benton