>PLAN SORT (SORT (JOIN (TR INDEX (IDX_TREE3_2, IDX_TREE3_3), ART INDEX (RDB$PRIMARY2))))

Hmm, I don't think you want the index for IDX_TREE3_2 to be used here. Being the first table in the plan and noticing that Tr.iditem is only used in JOIN ... ON art.idarticle = tr.iditem, I think Firebird actually "changes" your query to use tr.iditem > 0 rather than art.IDARTICLE > 0 and my hunch is that most rows satisfy this criterion.

>SELECT FIRST 30 SKIP 0 distinct tr.IdLink, tr.IdTargetLink, tr.IdItem, tr.NodePosition
>FROM Tree3 tr
>inner join article3_1 art on art.idarticle = Tr.iditem
>WHERE tr.ItemType = 2 AND tr.Market1 = 1
>AND ((art.IDARTICLE > 0) AND ( (LOWER(art.Artikelnummer) like '%a4 papier%' ) OR ( (LOWER(art.Artikelnummer) like 'a4' )
>AND (LOWER(art.Artikelnummer) like 'papier')) OR (LOWER(art.Artikelnaam1) like '%a4 papier%' ) OR ( (LOWER(art.Artikelnaam1) like '%a4%' )
>AND (LOWER(art.Artikelnaam1) like '%papier%')) OR (LOWER(art.Artikelnaam2) like '%a4 papier%' ) OR ( (LOWER(art.Artikelnaam2) like '%a4%' )
>AND (LOWER(art.Artikelnaam2) like '%papier%')) OR (LOWER(art.Artikelnr_leverancier) like '%a4 papier%' ) OR ( (LOWER(art.Artikelnr_leverancier) like '%a4%' )
>AND (LOWER(art.Artikelnr_leverancier) like '%papier%')) OR (LOWER(art.Merk) like '%a4 papier%' ) OR ( (LOWER(art.Merk) like '%a4%' )
>AND (LOWER(art.Merk) like '%papier%')) OR (LOWER(art.EAN) like '%a4 papier%' ) OR ( (LOWER(art.EAN) like '%a4%' )
>AND (LOWER(art.EAN) like '%papier%')) OR (LOWER(art.AltArtnr1) like '%a4 papier%' ) OR ( (LOWER(art.AltArtnr1) like '%a4%' )
>AND (LOWER(art.AltArtnr1) like '%papier%')) OR (LOWER(art.AltArtnr2) like '%a4 papier%' ) OR ( (LOWER(art.AltArtnr2) like '%a4%' )
>AND (LOWER(art.AltArtnr2) like '%papier%')) ))
>AND tr.NODELEVEL =5 and tr.LINKTYPE <> 5
>ORDER BY tr.NodePosition

You use so many parenthesis that I had to restructure your query to easier understand it. Doing that made me notice a minor error. I think the below query is identical to your original query:

SELECT distinct tr.IdLink, tr.IdTargetLink, tr.IdItem, tr.NodePosition
FROM Tree3 tr
inner join article3_1 art on art.idarticle = Tr.iditem
WHERE tr.ItemType = 2
AND tr.Market1 = 1
AND art.IDARTICLE+0 > 0
AND (LOWER(art.Artikelnummer) like '%a4 papier%'
OR (LOWER(art.Artikelnummer) like 'a4'
AND LOWER(art.Artikelnummer) like 'papier')
OR (art.Artikelnaam1 containing 'a4' AND art.Artikelnaam1 containing 'papier')
OR (art.Artikelnaam2 containing 'a4' AND art.Artikelnaam2 containing 'papier')
OR (art.Artikelnr_leverancier containing 'a4' AND art.Artikelnr_leverancier containing 'papier')
OR (art.Merk containing 'a4' AND art.Merk containing 'papier')
OR (art.EAN containing 'a4' AND art.EAN containing 'papier')
OR (art.AltArtnr1 containing 'a4' AND art.AltArtnr1 containing 'papier')
OR (art.AltArtnr2 containing 'a4' AND art.AltArtnr2 containing 'papier'))
AND tr.NODELEVEL = 5
AND tr.LINKTYPE <> 5
ORDER BY tr.NodePosition
ROWS 1 TO 30

I didn't change art.Artikelnummer, to highlight that you don't have wildcards for a4 and papier here. Change it to be like the others if you intended to have wildcards, remove the OR statement entirely if not since it cannot be equal to both a4 and papier. Elsewhere, I've generally ignored "like '%a4 papier%'" since it is a subset of "like '%a4%' or like '%papier%'". Furthermore, I find using CONTAINING to be simpler than using LOWER() or UPPER().

Unfortunately, excepting the addition of +0, I don't think the modifications I've done helps you too much speedwise (but you should of course try it and report the plan to this list). How many records and how large a proportion of the records in Tree3 has Market1 = 1? Are there many duplicates if you remove DISTINCT from your query?

If papier and a4 are fixed values to be used in this query, you may consider adding a trigger populated Booleanish column (indexed) that you set to 1 if any of the mentioned fields contain a4 or papier. I assume the number of rows satisfying such a criteria to be far smaller than Market1 = 1.

Set