I don't know Fb 2.0 well enough, but try adding +0 like this (that's at
least what I do when I've problems with Fb 1.5):

SELECT FIRST 10 SKIP 0 T.K_TARGET, T.K_PHONE, T.V_NAME, T.D_CREATION
FROM TARGETS T JOIN TARGET_GROUPS TG ON TG.K_TARGET = T.K_TARGET WHERE
T.K_USER=2262 AND T.C_TYPE=0 AND TG.K_GROUP+0 = 2610 ORDER BY T.V_NAME

Hopefully, this will get a plan that satisfy your requirements. If it
still insists on having TG as the first table in the plan, try changing
the JOIN to ON TG.K_TARGET = T.K_TARGET+0 (either in addition to or
instead of the +0 above).

Report back the result to this list, I'm curious whether this will work
in Fb 2.0.

HTH,
Set

Juan Pedro López wrote:

> I have the following tables:
>
> CREATE TABLE TARGETS (
> K_TARGET INTEGER,
> K_PHONE VARCHAR(16) NOT NULL COLLATE ES_ES,
> K_USER INTEGER,
> D_CREATION TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
> C_NETWORK VARCHAR(5) NOT NULL COLLATE ES_ES,
> C_TYPE SMALLINT NOT NULL,
> V_NAME VARCHAR(40) DEFAULT '' NOT NULL COLLATE ES_ES);
>
> CREATE TABLE TARGET_GROUPS (
> K_TARGET INTEGER,
> K_GROUP INTEGER,
> K_USER INTEGER);
>
> Primary keys
>
> ALTER TABLE TARGETS ADD CONSTRAINT PK_TARGETS PRIMARY KEY (K_TARGET);
> ALTER TABLE TARGET_GROUPS ADD CONSTRAINT PK_TARGET_GROUPS PRIMARY KEY
> (K_TARGET, K_GROUP, K_USER);
>
> Unique keys
>
> ALTER TABLE TARGETS ADD CONSTRAINT UK_TARGETS UNIQUE (K_USER, C_TYPE,
> K_PHONE);
>
> Foreign keys
>
> ALTER TABLE TARGETS ADD CONSTRAINT FK_TARGETS FOREIGN KEY (K_USER)
> REFERENCES USERS (K_USER) ON DELETE CASCADE ON UPDATE CASCADE;
> ALTER TABLE TARGET_GROUPS ADD CONSTRAINT FK01_TARGET_GROUPS FOREIGN KEY
> (K_GROUP) REFERENCES GROUPS (K_GROUP) ON DELETE CASCADE ON UPDATE
> CASCADE;
> ALTER TABLE TARGET_GROUPS ADD CONSTRAINT FK02_TARGET_GROUPS FOREIGN KEY
> (K_USER) REFERENCES USERS (K_USER) ON DELETE CASCADE ON UPDATE CASCADE;
> ALTER TABLE TARGET_GROUPS ADD CONSTRAINT FK_TARGET_GROUPS FOREIGN KEY
> (K_TARGET) REFERENCES TARGETS (K_TARGET) ON DELETE CASCADE ON UPDATE
> CASCADE;
>
> Additional indices
>
> CREATE INDEX NAME ON TARGETS (V_NAME);
>
> The following query executed in FB 2.0.1
>
> SELECT FIRST 10 SKIP 0 T.K_TARGET, T.K_PHONE, T.V_NAME, T.D_CREATION
> FROM TARGETS T JOIN TARGET_GROUPS TG ON TG.K_TARGET = T.K_TARGET WHERE
> T.K_USER=2262 AND T.C_TYPE=0 AND TG.K_GROUP = 2610 ORDER BY T.V_NAME
>
> PLAN SORT (JOIN (TG INDEX (RDB$FOREIGN61), T INDEX (RDB$PRIMARY20)))
>
> takes much more time to execute than in FB 1.0.3
>
> I guess the difference is in the selected plan. In FB 1.0.3 the plan is
>
> PLAN JOIN (T ORDER NAME,TG INDEX (RDB$PRIMARY22))
>
> so index NAME is used to sort the results. Also TARGET_GROUPS's pk is
> used.
>
> If I set this plan while executing the query in FB 2.0.1, execution time
> is the same as in FB 1.0.3.
>
> Selectivity of NAME index is quite poor, so I tested to replace it with
> a composite one between V_NAME and K_TARGET fields. In this case the
> index was unique because K_TARGET is primary key.
>
> Setting the query to "ORDER BY T.V_NAME, T.K_TARGET" doesn't show any
> change in the selected plan.
>
> Restoring the original NAME index ant testing the following query
>
> SELECT FIRST 10 SKIP 0 T.K_TARGET, T.K_PHONE, T.V_NAME, T.D_CREATION
> FROM TARGETS T WHERE T.K_USER=2262 AND T.C_TYPE=0 ORDER BY T.V_NAME
>
> turned out that the optimizer was using the following plan
>
> PLAN (T ORDER NAME INDEX (RDB$53))
>
> So in this case the poor selectivity index is used anyway and results
> are the same as in FB 1.0.3
>
> What can I do to improve performance with FB 2.0.1 for the first query?
>
> I'm using superserver for Linux.
>
> Thank you very much,
>
> Juan Pedro López