thanks for your reponse,
> You haven't mentioned your used Firebird server version, but with 2.5 I
> get the following execution plan "out-of-the box".
>
> PLAN JOIN (A INDEX (A_IDX1), B INDEX (B_IDX1, B_IDX3))
>
> And the query is executed in < 50ms returning 0 rows.
>
i use the same server on windows 7 64 with the 32 bit version.
i obtain the same plan if i don't recompute index.
>
> But after recomputing index statistics, I get the same selectivity as
> stated above and your mentioned execution plan as well:
>
> PLAN JOIN (A INDEX (A_IDX1), B INDEX (B_IDX1))
>
> but again, the query is executed in < 50ms returning 0 rows.
>
ok it retruns 0 rows because in my request i use date (14.08.2012 07:20:00)
that i have generated this morning with the stored proc populate_a_b ('NOW')
and for you the date should be different.
if you change the date you could see my problem in the number of read in table B
>
> No. IMHO, the optimizer is doing a good job. It uses indexes with a good
> selectivity and first horizontally filter table A by using A_IDX1 and
> uses that already filtered "stream" to join table B via index B_IDX1.
>
> Just for the records: Firebird can join two indexes on the same table
> via a bitmap vector (as shown in the execution plan before re-computing
> index statistics), but in your case, the optimizer uses a different
> path, which executes the query pretty fast.
>
> Do you have any real performance issues to solve?
>
yes i have a big problem in the production database because i have a big number
of read on table B because the second index is not used so each time it found a
line in A, it reads all relational lines in TABLE B.
in production database i have one million lines in table a and near 10 millions
in table B.
