Q: How to speed up a simple join query in Firebird 2.5.1 When executing a simple inner join query on two large tables the result is returned after several seconds in Firebird 2.5.1 In other databases the result is returned after several milliseconds. The question is how to speed up this query and why is Firebird using A NATURAL instead of the index?
The query is:
SELECT FIRST 20 * FROM A INNER JOIN B ON A.ID = B.AID;

Resulting plan is:
PLAN JOIN (A NATURAL, B INDEX (IDX_B_1))

Statistics:

Table and index metadata:
CREATE TABLE "A" 
(
  "ID"   INTEGER NOT NULL,
CONSTRAINT "PK_A" PRIMARY KEY ("ID")
);


CREATE TABLE "B" 
(
  "ID"   INTEGER NOT NULL,
  "AID"  INTEGER,
CONSTRAINT "PK_B" PRIMARY KEY ("ID")
);


CREATE INDEX "IDX_B_1" ON "B"("AID");

To populate you can use this procedure:
declare variable nba integer;
declare variable nbb integer;
declare variable nbc integer;

begin
  nba = 0;
  nbb = 0;
  nbc = 0;
  while (nba < 100000) do begin
    insert into A (ID) values (:nba);
    nbc = 0;
    while (nbc < 50) do begin
      insert into B (ID, AID)
      values (:nbb, :nba);
      nbb = nbb + 1;
      nbc = nbc + 1;
    end
    nba = nba + 1;
  end
end

