>> 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/
