In IBObjects@yahoogroups.com, "Enrico Raviglione" wrote:

> [ 01/06/2005 18.00.12 ]
> PREPARE STATEMENT
> TR_HANDLE = 16871616
> STMT_HANDLE = 16871428
>
> SELECT I.RDB$RELATION_NAME
> , I.RDB$INDEX_NAME
> , I.RDB$UNIQUE_FLAG
> , I.RDB$INDEX_TYPE
> , I.RDB$FOREIGN_KEY
> , ( SELECT C.RDB$CONSTRAINT_NAME
> FROM RDB$RELATION_CONSTRAINTS C
> WHERE C.RDB$INDEX_NAME = I.RDB$INDEX_NAME ) AS CNST
> , ( SELECT R.RDB$UPDATE_RULE
> FROM RDB$REF_CONSTRAINTS R
> WHERE R.RDB$CONSTRAINT_NAME =
> ( SELECT C.RDB$CONSTRAINT_NAME
> FROM RDB$RELATION_CONSTRAINTS C
> WHERE C.RDB$INDEX_NAME = I.RDB$INDEX_NAME )) AS UR
> , ( SELECT R.RDB$DELETE_RULE
> FROM RDB$REF_CONSTRAINTS R
> WHERE R.RDB$CONSTRAINT_NAME =
> ( SELECT C.RDB$CONSTRAINT_NAME
> FROM RDB$RELATION_CONSTRAINTS C
> WHERE C.RDB$INDEX_NAME = I.RDB$INDEX_NAME )) AS DR
> FROM RDB$INDICES I
> WHERE NOT I.RDB$RELATION_NAME STARTING WITH 'RDB$'
>
> PLAN (C INDEX (RDB$INDEX_43))
> PLAN (R INDEX (RDB$INDEX_13))
> PLAN (C INDEX (RDB$INDEX_43))
> PLAN (R INDEX (RDB$INDEX_13))
> PLAN (C INDEX (RDB$INDEX_43))
> PLAN (I NATURAL)
>
> ====>> This query seem to be the problem !!! <<====
>
> PLAN (I NATURAL) here the index are not used because the expression
> are where NOT I.RDB$RELATION_NAME starting with 'RDB$'
>
> [ 01/06/2005 18.00.17 ]

True Enrico, this query doesn't benefit from any indexes you may
define on rdb$indices.

I don't know the internals of Firebird good enough, if the subselects
may return 0 rows, then this query cannot be improved, if there's
always a match then the query can be rewritten, even though I doubt
the improvement would be too noticable.

What appears to me to be the problem, is that you have defined lots of
indexes within your database. Did you think carefully before adding
each index or did you add some 'just in case'? I guess the query would
be quicker if you had defined less indexes.

Set

*********************************
Firebird, the sloganless database
-without a subslogan
*********************************