--- In firebird-support@yahoogroups.com, Dimitry Sibiryakov <sd@...> wrote:

>
> > As you see, i load all records, sort it by date and fetch only the first one, which seems very unfriendly for the server.
>
> Not at all. You load only records which have ArticleId = 31. Look at
> PLAN.
>
> SY, SD.
>

Thanks Dimitri,
Yes you are right, but they even i limit it with articleId=31, they are too much. And as far this is part of a sub select, the things goes worst. In some situation, i am getting the error Too many Contexts of Relation/Procedure/Views (maximum is 255)
Looking at the history for the example query from first post, i have 3000 (indexed) reads for getting this single record, which is too much i think.
Unfortunately i am little unfamiliar with the PLAN. Here is it:

Plan
PLAN (TSG TP1 RDB$DATABASE NATURAL)
PLAN (TSG TP2 RDB$DATABASE NATURAL)
PLAN (TSG DTP RDB$DATABASE NATURAL)
PLAN (TSG STO RDB$DATABASE NATURAL)
PLAN SORT (JOIN (TSD INDEX (RDB$FOREIGN178), JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (TSG SUP INDEX (RDB$PRIMARY77), TSG PTP tPaymentType INDEX (PK_tPaymentType), TSG OTP INDEX (RDB$PRIMARY52), TSG OWN tOwner INDEX (RDB$PRIMARY53)), TSG TPTM INDEX (PK_tPaymentTypeMain)), JOIN (TSG TP1 P INDEX (RDB$PRIMARY55), JOIN (JOIN (TSG TP1 K INDEX (PK_tKey), TSG TP1 KU INDEX (FK_tKeyUser_Key)), TSG TP1 U INDEX (UNQ1_UCS_USERS)))), JOIN (TSG TP2 P INDEX (RDB$PRIMARY55), JOIN (JOIN (TSG TP2 K INDEX (PK_tKey), TSG TP2 KU INDEX (FK_tKeyUser_Key)), TSG TP2 U INDEX (UNQ1_UCS_USERS)))), JOIN (TSG DTP T INDEX (RDB$3), JOIN (TSG DTP R INDEX (FK_tDocTypeUserRightsDok), TSG DTP U INDEX (UNQ1_UCS_USERS)))), JOIN (TSG STO T INDEX (RDB$PRIMARY72), JOIN (TSG STO R INDEX (FK_tStorageUserRightsStor), TSG STO U INDEX (UNQ1_UCS_USERS)))), TOP INDEX (RDB$PRIMARY52))))

Adapted Plan
PLAN (TSG TP1 RDB$DATABASE NATURAL) PLAN (TSG TP2 RDB$DATABASE NATURAL) PLAN (TSG DTP RDB$DATABASE NATURAL) PLAN (TSG STO RDB$DATABASE NATURAL) PLAN SORT (JOIN (TSD INDEX (INTEG_488), JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (TSG SUP INDEX (INTEG_387), TSG PTP tPaymentType INDEX (PK_tPaymentType), TSG OTP INDEX (INTEG_362), TSG OWN tOwner INDEX (INTEG_363)), TSG TPTM INDEX (PK_tPaymentTypeMain)), JOIN (TSG TP1 P INDEX (INTEG_365), JOIN (JOIN (TSG TP1 K INDEX (PK_tKey), TSG TP1 KU INDEX (FK_tKeyUser_Key)), TSG TP1 U INDEX (UNQ1_UCS_USERS)))), JOIN (TSG TP2 P INDEX (INTEG_365), JOIN (JOIN (TSG TP2 K INDEX (PK_tKey), TSG TP2 KU INDEX (FK_tKeyUser_Key)), TSG TP2 U INDEX (UNQ1_UCS_USERS)))), JOIN (TSG DTP T INDEX (INTEG_313), JOIN (TSG DTP R INDEX (FK_tDocTypeUserRightsDok), TSG DTP U INDEX (UNQ1_UCS_USERS)))), JOIN (TSG STO T INDEX (INTEG_382), JOIN (TSG STO R INDEX (FK_tStorageUserRightsStor), TSG STO U INDEX (UNQ1_UCS_USERS)))), TOP INDEX (INTEG_362))))