--- In firebird-support@yahoogroups.com, André Dostal <ad7@g...> wrote:

> Dear Alan, dear Alexander,
> I've compared the metadata of the origin and the backuped database, and
> they are eqaul. So I assume that the indices didn't get lost.
>
> Then I've looked on the plans, and they do differ. So I tried to provide
> the plan to the firebird server, and that worked perfekt:
> time on origin db : ~ 4 seconds
> time on backup db : ~ 4 minutes!! (no typo,
> it's real, belive me)
> time on backup*db with plan from origin db : ~ 3 seconds (here again, no
> typo)
>
> Here is the SQL-Statement and plans that the firebird server used, if I
> don't provide the plan:
>
> SELECT
> POSGRP."POSID",
> GRPREL."PARENTID",
> GRPREL."CATEGORY"
> FROM
> "T_POSGROUPINGS" POSGRP,
> "T_PRODUCT_GROUP_RELATION" GRPREL,
> "T_INVLINE" LIN,
> "T_COSTITEM" CI
> WHERE
> ( POSGRP."RECHID" = 14 ) AND /*Index*/
> ( POSGRP."GROUPID" = GRPREL."CHILDID" ) AND /*Second part of primary
> key & foreign key to another table T - foreign key to same table T*/
> ( POSGRP."POSID" = LIN."ID" ) AND /*First part of primary
> key & foreign key to lin.id - Primary Key*/
> ( CI."ID" = LIN."COSTITEMID") /*primary key - foreign key
> to ci.id*/
> ORDER BY
> CI."COSTITEM", /*index*/
> LIN."PRODWGRPID",
> LIN."DATEFROM",
> LIN."ID" /*primary key*/
>
> restored db:
> PLAN SORT (JOIN (LIN NATURAL,CI INDEX (RDB$PRIMARY22),POSGRP INDEX
> (RDB$PRIMARY49,RDB$FOREIGN122),GRPREL INDEX (RDB$FOREIGN109)))
>
> original db:
> PLAN SORT (JOIN (POSGRP INDEX (RDB$FOREIGN122),LIN INDEX
> (RDB$PRIMARY31),CI
> INDEX (RDB$PRIMARY22),GRPREL INDEX (RDB$FOREIGN109)))
>
> where comes the natural join from?

André, (sorry, your name did'nt passed charset convertion correctly
in browser) from optimizer. If Arno Brinkman have time to look this
list now, he can be more precise. Seems FB1.5 optimizer pays more
attention to uniqueness of indices and sometimes as a result choices
wrong sequence of tables in join. Here on table POSGRP it prefered to
use RDB$PRIMARY49 instead of RDB$FOREIGN122 only and you see result. I
reported several queries of this kind to Dmitry Yemanov, who have
entire metadata of my database and can trace optimizer on this queries
and he said he at last found the reason but can't fix it on the fly,
seems 1.5.1 will not have this fix. Why plan is different on the same
server but different instances of database - I can only build vague
guesses around indices statistics. Check it, I recall one or two
reports on beta stage about empty statistics after restore. If all
indices have 0, recalculate it (I don't know what interactive tool do
you use but believe all of them support this function). If not, you
are doomed to check your queries and optimize slow ones by hands.
Preferable trick is to introduce dummy expressions on columns indices
on which you do not want to be used, here, assuming POSID is integer,
you can try

POSGRP."POSID"+0 = LIN."ID"

but this will help only if RDB$FOREIGN122 starts wirh POSGRP."RECHID"
or POSGRP."GROUPID". If it starts with POSGRP."POSID" too, most
probably you'll get plan

PLAN SORT (JOIN (POSGRP Natural, LIN INDEX
(RDB$PRIMARY31),CI
INDEX (RDB$PRIMARY22),GRPREL INDEX (RDB$FOREIGN109)))

or not POSGRP Natural but index created on one of mentioned columns.

If this plan will be slow too, you have'nt another choices except
usage of explicit plans. Note it is not recommended to use pre-FB1.5
system indices in explicit plans, because their enumeration can be
changed if you'll drop/create constraints. Possible way is to create
database from script and pump data, in this case FB1.5 by default will
use names you give to constraints to create indices which support them
and you can safely use this names in plans.

Best regards,
Alexander.