|Subject||RE: [firebird-support] Optimizer not optimizing|
> Using Firebird 2.1.1 for Windows here. I have a this PICKUP table withtwo
> CREATE INDEX PICKUP_CLIFEC ON PICKUP (CLIENTE, FECHAREC);
> CREATE INDEX PICKUP_FECHAREC ON PICKUP (FECHAREC);
> Hint: CLIENTE column is varchar(38), FECHAREC column is date.
> Now in a JOIN query that uses the following WHERE clause:
> where (A.CLIENTE=:clienteid) and (A.FECHAREC between CURRENT_DATE-31
> I get this plan:
> PLAN SORT (JOIN (A INDEX (PICKUP_FECHAREC), B INDEX (RDB$PRIMARY26), C
> INDEX (RDB$PRIMARY14)))
> It's using PICKUP_FECHAREC index (which is based only in the date
> and I think it should be using the PICKUP_CLIFEC index, which startswith
> the CLIENTE column just like the WHERE clause needs.It is reasonable to expect that the PICKUP_CLIFEC to be used, but it
depends on the _selectivity_ of the CLIENTE field.
How many distinct Cliente values are there compared to the number of