Hi again Norbert,
nice to hear that it worked.

There are several ways to treat the optimizer. The best way is to
fully understand it, but amongst those frequenting this list only Arno
does that.

The rest of us have to settle for observing its behaviour. Two things
that doesn't take long to learn, is that the optimizer sometimes uses
indexes that slows down queries rather than speed them up, and that
replacing a field with an expression prevents Firebird from using any
index on that field (I think Firebird 2 has expression indexes, but I
hope Firebird still is unable to see that x+0 = x).

In your case, I just took a look at the second plan, saw that the
first index that was used was FK_T110_REF_T100, an index that points
to T110.T100_ID. Eliminating the possibility for the plan to use this
index, I simply hoped that it would choose the other possible plan
rather than go NATURAL twice.

Nothing magical or even difficult, and I don't know whether it is
documented or not.

Thanks for a good problem description!
Set

--- In firebird-support@yahoogroups.com, "Norbert Nemeth" wrote:
> Hi Set,
>
> Thank you for your prompt reply.
>
> I have changed my query as you have suggested to me
> ("ON T100.T100_ID = T110.T100_ID + 0") and it getting faster.
>
> Is the "+ 0" an undocumented future? How can I get more information
> about the method of the optimalisation?
>
> Regards,
> Norbert
>
> > Hi Norbert!
> >
> > It isn't a matter of there being data in the tables, but a matter
> > of which data in the tables. The optimizer simply thinks that with
> > the current content of the tables, then plan B is better than plan
> > A. Change the contents of the tables (e.g. add records to the
> > table with the least records) and your plan may change. If you
> > want to prevent the optimizer from choosing the plan it thinks is
> > the best, do this change to your query:
> > "ON T100.T100_ID = T110.T100_ID + 0"
> >
> > Though I'm not certain which plan is the best in your case.
> >
> > Set
> >
> > --- In firebird-support@yahoogroups.com, "Norbert Nemeth" wrote:
> >> Hi,
> >>
> >> I have 2 tables (relation: T100 and T110 ==> 1:n)
> >>
> >> CREATE TABLE T100 (
> >> T100_ID INTEGER NOT NULL,
> >> T100_COL1 VARCHAR(40),
> >> T100_COL2 SMALLINT,
> >> CONSTRAINT PK_T100 PRIMARY KEY (T100_ID)
> >> );
> >>
> >> CREATE TABLE T110 (
> >> T110_ID INTEGER NOT NULL,
> >> T100_ID INTEGER NOT NULL,
> >> T110_NR INTEGER NOT NULL,
> >> CONSTRAINT PK_T110 PRIMARY KEY (T110_ID)
> >> );
> >>
> >> CREATE UNIQUE DESC INDEX IDX_T110 ON T110 (T100_ID,T110_NR);
> >>
> >> ALTER TABLE T110
> >> ADD CONSTRAINT FK_T110_REF_T100 FOREIGN KEY (T100_ID)
> >> REFERENCES T100 (T100_ID);
> >>
> >>
> >> Select:
> >> SELECT T100.T100_COL1
> >> , T100.T100_COL2
> >> , T110.T110_NR
> >> FROM T110
> >> JOIN T100
> >> ON T100.T100_ID = T110.T100_ID
> >> ORDER BY T110.T100_ID DESC, T110.T110_NR DESC
> >>
> >> 1. If the tables are empty, than:
> >> PLAN JOIN (T110 ORDER IDX_T110,T100 INDEX (PK_T100))
> >>
> >> 2. If it contains records, than:
> >> PLAN SORT (JOIN (T100 NATURAL,T110 INDEX (FK_T110_REF_T100)))
> >> ^^^^^^^^^^^^^^ why?
> >>
> >> Why does not the server (FB 1.5.2.4731) use the indexes
> >> according to the first plan (in case of empty tables)?
> >>
> >> Regards,
> >> Norbert