>> 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.
>
> i begin to understand why i obtain this plan and why it's normal. I decomposed my inner join request in two "for select" request and i obtain the same number of read and nearly the same execution time.
> After some search i see that some other database merge index to use several index on same table but this method is cost time effective and in some case it's preferable to do much read that use several index.
>
> but i need your help to have a better understand of how works the optimizer with indexes (maybe a document exists ?) because i think i have put to much indexes that don't serve in my database.

You can run gstat, or if you like it visual, FB TraceManager, FBAnalyst
etc. to query/show index statistics and imply their usefullness. E.g.
the worst index is if the max duplicates is 100% compared to the number
of indexed rows, because this basically means that you store only one
distinct value, for which an index makes absolutely no sense, so this is
a candidate to be dropped immediately and write operations benefit from
that. Another thing is to keep the index b-tree depth small (<=3). >3
usually happens on a largish table with a small page size, so increasing
the page size helps in that area.

I have seen a lot of databases in various consulting gigs now, which
were purely "over-indexed" and a small page size (in this particular
case 1K *g*) was some kind of worse scenario. Beside removing such
useless indexes, inspect I/O statistics and try to keep the cache hit
ratio high, thus minimize disk I/O.

AFAIK, some presentations on the optimizer are available from older
Firebird conferences somewhere. E.g:

http://www.slideshare.net/ibsurgeon/undestandung-firebird-optimizer-by-dmitry-yemanov-in-english
http://www.slideshare.net/ibsurgeon/firebird-costbased-optimization-and-statistics-by-dmitry-yemanov-in-english

I'm afraid not that much help for newbies without the speakers voice.



--
With regards,
Thomas Steinmaurer
http://www.upscene.com/