Changing plan of query when using 2 always TRUE conditions

[CyberMaxRu]

SELECT RF.* FROM RDB$RELATIONS R INNER JOIN RDB$RELATION_FIELDS RF ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME WHERE R.RDB$RELATION_NAME = 'RDB$RELATIONS'

Plan: PLAN JOIN (RF INDEX (RDB$INDEX_4), R INDEX (RDB$INDEX_0))

SELECT RF.* FROM RDB$RELATIONS R INNER JOIN RDB$RELATION_FIELDS RF ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME WHERE R.RDB$RELATION_NAME = 'RDB$RELATIONS' AND (TRUE) AND (TRUE)

Plan: PLAN JOIN (R INDEX (RDB$INDEX_0), RF INDEX (RDB$INDEX_4))

In my case, Plan can change from: PLAN JOIN (BT ORDER FK_INF$ABONENT$BT_PERIOD INDEX (FK_INF$ABONENT$BT_SERVICE), ABONENT INDEX (PK_DIR$ABONENT$ABONENT), HOUSE INDEX (PK_DIR$HOUSE$HOUSE), UNIT INDEX (PK_DIR$UNIT$UNIT)) to: PLAN SORT (JOIN (UNIT NATURAL, HOUSE INDEX (FK_DIR$HOUSE$HOUSE_UNIT), ABONENT INDEX (FK_DIR$ABONENT$ABONENT_HOUSE), BT INDEX (UNQ_INF$ABONENT$BT)))

and executing 2,5 sec instead of 0.1 sec.

[sim1984]

Please specify the exact version of Firebird on which this is being reproduced.

[CyberMaxRu]

3.0.8. For 4.0.2.2770 the same.

[dyemanov]

Every condition is treated as a filter and reduces the expected cardinality, thus affecting the cost. For joins it may mean a different join order, as in examples above. And the engine generally has no idea whether condition is sane or dumb.

This case can be improved for constant conditions but it's not that easy if it gonna look like R.RDB$RELATION_NAME = R.RDB$RELATION_NAME.

[dyemanov]

Closed (as duplicated) in favor of the older #6941.