> > Using Firebird 2.1.1 for Windows here. I have a this PICKUP table with
> two
> > indexes:
> >
> > 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
> and
> > CURRENT_DATE)
> >
> > 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
> column)
> > and I think it should be using the PICKUP_CLIFEC index, which starts
> with
> > 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
> distinct FECHAREC?

Well actually right now there is only 2 or 3 records on the PICKUP table. All share the same CLIENTE and FECHAREC. Also it might be worth noting that CLIENTE is also a Foreign Key column.

Perhaps the optimizer will "think" different with enough normal day-to-day records in the table?