Which Firebird version are you using? I don't know about Firebird 2.0, but Firebird 1.5 only has information about the selectivity of the complete index, not of its individual fields. If so, I kind of think there are two possible reasons for not using the index containing both fields:

1) The index has so poor selectivity that NATURAL is a better choice (if so, there's nothing to improve)

2) The optimizer may not know that using an index on SRC_DIR_NAME, ID_CSYMBOL is equally good or better than an index using simply SRC_DIR_NAME (thinking about it, it is obvious, but if all you're doing is choosing between two indexes with a selectivity of 0.0000007 and 0.5, then you of course prefer the one with 0.0000007). If this is the case, you can try either:
a) Don't prepare until you have to (possibly even updating the statistics shortly before the prepare, I don't know whether that can help or not), hoping a better plan will be chosen when you have final data
b) Add NODE_ID to IX_ZLEVELS_DIR_SYM to increase selectivity hoping that would improve the plan
c) Remove NODE_ID from IX_ZLEVELS_DIR_NODE hoping that would improve the plan (though this could slow down UPDATEs and DELETEs)

Other things you should consider, is the selectivity of IX_STREETS_LINK_ALT and FK_STREETS_CSYMBOL, if FK_STREETS_CSYMBOL has considerably worse selectivity that IX_STREETS_LINK_ALT you may consider preventing FK_STREETS_CSYMBOL from being used. Another thing you also could consider, is whether it is best to start with SRC or STR first in the PLAN. It might be that
FROM STREETS STR LEFT JOIN ZLEVELS SRC is a better choice (but that depends on your data, the optimizer thinks that it is best to start with SRC, but it has no knowledge about the selectivity of the values 1360 vs 2000, 2010 and C1, C4).

Sorry for this answer more being about things to consider rather than direct solutions,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Milan Tomeš - Position
Sent: 16. april 2010 13:28
To: Firebird - support
Subject: [firebird-support] Plan question

Hi all,

I have this query:

SELECT ID_ZLEVELS
FROM ZLEVELS SRC
JOIN STREETS STR
ON (STR.LINK_ID = SRC.LINK_ID) AND
(STR.ALTSTREETS = 'F')
WHERE
(SRC.SRC_DIR_NAME IN ('C1', 'C4')) AND
(SRC.ID_CSYMBOL IN (2000, 2010)) AND
(STR.ID_CSYMBOL = 1360)

and these indexes on table ZLEVELS:

1. CREATE INDEX IX_ZLEVELS_DIR_NODE ON ZLEVELS (SRC_DIR_NAME, NODE_ID);
2. CREATE INDEX IX_ZLEVELS_DIR_SYM ON ZLEVELS (SRC_DIR_NAME, ID_CSYMBOL);

Why this query produces this plan:
PLAN JOIN (SRC INDEX (IX_ZLEVELS_DIR_NODE, IX_ZLEVELS_DIR_NODE), STR
INDEX (IX_STREETS_LINK_ALT, FK_STREETS_CSYMBOL))
?

I know about effect of index selectivity (in this case is selectivity of
index 1 0.0000007 and selectivity of index 2 0.5, but thats because I
haven't already final data - they are prepared in previous step) but I
can't understand why optimizer chooses first one instead of second one
which perfectly fits both where conditions.

Thanks for any hint

M. Tomes