This query (among others) is causing some problems for a colleague. I
get lost when tables are used more than once in a query and in trying to
understand why the optimiser makes what appear to be questionable decisions.

If anyone can help with this query and give some pointers to
understanding the firebird optimiser I'd be grateful.

cheers and thanks,

Bradley.
----------------

select * from DMPage
inner join DMDoc Doc1
on Doc1.docref = DMPage.docref
inner join dmdocsetdoclink DL2
on DL2.docref = Doc1.Docref
inner join dmDocSet DS1
on DS1.docsetref = DL2.docsetref
inner join dmdocsetdoclink DL1
on DL1.docsetref = DS1.docsetref
Where DL1.docref = 'XG00067062'
and Doc1.DocTypeVersionRef = 'XN00000138'
-----

this is the plan
-----
PLAN JOIN (DL2 NATURAL,DOC1 INDEX
(RDB$PRIMARY2,DMDOC_DOCTYPEVERSIONREF),DL1 INDEX
(DMDOCSETDOCLINK_DOCSETREF,DMDOCSETDOCLINK_DOCREF),DS1 INDEX
(RDB$PRIMARY9),DMPAGE INDEX (DMPAGE_DOCREF))
----
Notice the nasty natural join


now try this query its very similar
------
select * from DMPage
inner join DMDoc Doc1
on Doc1.docref = DMPage.docref
inner join dmdocsetdoclink DL2
on DL2.docref = Doc1.Docref
inner join dmDocSet DS1
on DS1.docsetref = DL2.docsetref
inner join dmdocsetdoclink DL1
on DL1.docsetref = DS1.docsetref
Where DL1.docref = 'XG00067062'
------
the query plan is nice and it runs fast
-----
PLAN JOIN (DL1 INDEX (DMDOCSETDOCLINK_DOCREF),DS1 INDEX
(RDB$PRIMARY9),DL2 INDEX (DMDOCSETDOCLINK_DOCSETREF),DOC1 INDEX
(RDB$PRIMARY2),DMPAGE INDEX (DMPAGE_DOCREF))
----

Now force the query plan on the first query using the query plan from
the second query
-----
select * from DMPage
inner join DMDoc Doc1
on Doc1.docref = DMPage.docref
inner join dmdocsetdoclink DL2
on DL2.docref = Doc1.Docref
inner join dmDocSet DS1
on DS1.docsetref = DL2.docsetref
inner join dmdocsetdoclink DL1
on DL1.docsetref = DS1.docsetref
Where DL1.docref = 'XG00067062'
and Doc1.DocTypeVersionRef = 'XN00000138'
PLAN JOIN (DL1 INDEX (DMDOCSETDOCLINK_DOCREF),DS1 INDEX
(RDB$PRIMARY9),DL2 INDEX (DMDOCSETDOCLINK_DOCSETREF),DOC1 INDEX
(RDB$PRIMARY2),DMPAGE INDEX (DMPAGE_DOCREF))
-----

How can we make the query run fast without the query plan, do you have
any ideas what is going wrong?

Thanks