Hello, > it is normal that firebird uses uses only indexes one of two tables in inner > join request ? > after some tests , i noticed that firebird uses the indexes of the table that > have the index with the best selectivity.
Correct. Firebird has a CBO (cost-based optimizer), which can do a good job, if index statistics are up-to-date. > this is a little exemple : > > two table A AND B with a relation 1-n A have 10000 lines and B have 5000000. > each line in A have 500 lines in B. > > Structures : > > > CREATE TABLE A ( > ID INTEGER NOT NULL, > DATEDOC TIMESTAMP NOT NULL, > WHEREDOC VARCHAR(22) NOT NULL > ); > > > ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (ID); > > > CREATE INDEX A_IDX1 ON A (DATEDOC); > CREATE INDEX A_IDX2 ON A (WHEREDOC); > > > CREATE TABLE B ( > REFA INTEGER NOT NULL, > NBWORD INTEGER NOT NULL, > REFWROD VARCHAR(50) NOT NULL, > DATEWORD TIMESTAMP NOT NULL > ); > > > CREATE INDEX B_IDX1 ON B (REFA); > CREATE INDEX B_IDX2 ON B (NBWORD); > CREATE INDEX B_IDX3 ON B (REFWROD); > CREATE INDEX B_IDX4 ON B (DATEWORD); > > PROCEDURES TO POPULATE : > > > create or alter procedure POPULATE_A_B > AS > declare variable nba integer; > declare variable nbb integer; > declare variable nbc integer; > declare variable tmptime timestamp; > declare variable rf1 varchar(5); > declare variable rf2 varchar(5); > declare variable rf3 varchar(5); > declare variable rf4 varchar(5); > declare variable rf5 varchar(5); > declare variable rf6 varchar(50); > > begin > nba = 0; > nbb = 0; > rf1 = 'qw'; > rf2 = 'er'; > rf3 = 'ty'; > rf4 = 'ui'; > rf5 = 'op'; > while (nba < 10000) do begin > tmptime = 'now'; > tmptime = tmptime + (1.0000*nba)/(24*60); > insert into A (ID, DATEDOC, WHEREDOC) > values (:nba, :tmptime, 'nowhere'); > nbb = 0; > while (nbb < 500) do begin > nbc = mod(nbb,5); > if (nbc = 0) then > rf6 = rf1; > if (nbc = 1) then > rf6 = rf2; > if (nbc = 2) then > rf6 = rf3; > if (nbc = 3) then > rf6 = rf4; > if (nbc = 4) then > rf6 = rf5; > insert into B (REFA, NBWORD, REFWROD, DATEWORD) > values (:nba, 1, :rf6, 'NOW'); > nbb = nbb + 1; > end > nba = nba + 1; > > end > end > > REQUEST : > > select * from a inner join b on ID = refa > where a.datedoc >= '14.08.2012 07:20:00' and a.datedoc <= '14.08.2012 > 08:20:00' and b.refwrod = 'qw' > > PLAN : > > PLAN JOIN (A INDEX (A_IDX1), B INDEX (B_IDX1)) 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. > SELECTIVITY : > > A_IDX1 => 0.00009999 > A_IDX2 => 1 > B_IDX1 => 0.0000999 > B_IDX2 => 1 > B_IDX3 => 0.2 > B_IDX4 => 0.00001111 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. > As you can see the plan don't use the index on b.refwrod so for each line > read into table A, all line with relation with B are read > yet there is an index on refwrod but it doesn't take it. So with this > kind of request a big number of line are read. 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? -- With regards, Thomas Steinmaurer http://www.upscene.com/ > an other exemple : > > For select someword from other_table where date > :begin and date < > :end goup by refwrod into :word do begin > select sum(nbword) from A INNER JOIN B on id = refa where a.datedoc >> = :begin and a.datedoc <= :end and b.refwrod = :word into :nbwords; > end > > so in this case if there are 5000 line in A between :begin and :end > Date there will be 2500000 read in table B for each word. > > i don't understand why it don't take the index on refwrod > on table B ? > > on the other hand if the selectivity of refwrod's index on table B is better > than datedoc's index on Table A it takes only this one (refwrod's index) , > never it takes the indexes of both table. > > what do you tink about that ? > > > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > > Also search the knowledgebases at http://www.ibphoenix.com > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Yahoo! Groups Links > > >
