Den 19.12.2015 20:38, skrev 'Daniel Miller' dmil...@amfes.com [firebird-support]: > With correction for one or two typos - those both worked, thank you! > Had to read, and read, and read - to try to understand HOW these work. > Which was exactly what I was hoping for - to better understand usage of > Firebird SQL. Great to hear, Daniel! > Looking at the query plans, it appears Alternative 2 - which uses the > two exists(select) constructs instead of two joins appears to be > slightly more efficient - at least the query plan appears slightly > smaller. So I'll file Alternate 1 for future testing against a larger > dataset - and use Alternate 2 for now. The performance of these two ought to be similar. Though if your real query include more tables, then you should normally try to put your LEFT JOINs after your [inner] JOINs. That way, the optimizer has a greater choice regarding which plan to build. There are of course exceptions to this, sometimes you may prefer the LEFT JOIN before normal inner JOIN for optimization purposes, and sometimes there's no option but to have the LEFT JOIN before the JOIN (e.g. with SELECT ... FROM CHILD LEFT JOIN FATHER F ... LEFT JOIN MOTHER M ... JOIN GRANDPARENT GP on GP.ID IN (M.FATHER, M.MOTHER, F.FATHER, M.MOTHER) there's no way to rearrange so that the JOIN to GRANDPARENT gets before the LEFT JOINs of FATHER or MOTHER ). But archiving alternative 1 and go with alternative 2 and then compare them at a later time sounds like a good plan. > I'm assuming there would be no benefit, and would probably be worse, to > add indexes for ROUTE_INDEX, or NETCON, or QUALITY? Because they > represent a small range of possible values that is duplicated for each > node? That's next to impossible for me to guess. I often relate this to cancer. For a table containing breast cancer, an index on GENDER would be useful when trying to find men, but not women, since 99% of breast cancers occur amongst women. Hence, if only 1% have r.NETCON>5 or r.QUALITY>3, then an index could in some cases be of benefit, although I doubt it would be useful in your case since NODE_ID probably is rather selective already.
Set
