| Subject | [firebird-support] Re: Why does the optimizer choose NATURAL for this tiny table? |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2014-11-13T11:39:35Z |
Hi Sean/Dmitry, I've mingled my answers to your suggestions.
Does the plan change to INDEX if the condition would be:
S2) on p.icd10 in (t.icd10, t.icd3)
S3) on p.icd10 = t.icd10 or p.icd10 = t.icd3
D2) ON p.icd10 = t.icd10 OR p.icd10 = t.icd10
No, these all keep the original plan:
PLAN JOIN (JOIN (JOIN (T KID INDEX (U_NPR_KRG_ID_FNRK), T N INDEX (I_NPR_KRG_ID), T T INDEX (I_NPR_TILSTAND_ID_NPR)), JOIN (P NATURAL, G INDEX (I_ICD10_GRUPPE_ICD10))), I INDEX (PK_ICD10))
D1) ON p.icd10 = t.icd10
S1/D3) ON p.icd10 = substring(t.icd10 from 1 for 3)
These change the plan to:
PLAN JOIN (MERGE (SORT (JOIN (P NATURAL, G INDEX (I_ICD10_GRUPPE_ICD10))), SORT (JOIN (T KID INDEX (U_NPR_KRG_ID_FNRK), T N INDEX (I_NPR_KRG_ID), T T INDEX (I_NPR_TILSTAND_ID_NPR)))), I INDEX (PK_ICD10))
The version is Firebird 2.5.1.
I doubt there are much to win by using an index, the ICD10_PURRES p (lookup) table contains only 112 rows, each of which contains one integer, one char(4) and one char(3) field.
Set
Does the plan change to INDEX if the condition would be:
S2) on p.icd10 in (t.icd10, t.icd3)
S3) on p.icd10 = t.icd10 or p.icd10 = t.icd3
D2) ON p.icd10 = t.icd10 OR p.icd10 = t.icd10
No, these all keep the original plan:
PLAN JOIN (JOIN (JOIN (T KID INDEX (U_NPR_KRG_ID_FNRK), T N INDEX (I_NPR_KRG_ID), T T INDEX (I_NPR_TILSTAND_ID_NPR)), JOIN (P NATURAL, G INDEX (I_ICD10_GRUPPE_ICD10))), I INDEX (PK_ICD10))
D1) ON p.icd10 = t.icd10
S1/D3) ON p.icd10 = substring(t.icd10 from 1 for 3)
These change the plan to:
PLAN JOIN (MERGE (SORT (JOIN (P NATURAL, G INDEX (I_ICD10_GRUPPE_ICD10))), SORT (JOIN (T KID INDEX (U_NPR_KRG_ID_FNRK), T N INDEX (I_NPR_KRG_ID), T T INDEX (I_NPR_TILSTAND_ID_NPR)))), I INDEX (PK_ICD10))
The version is Firebird 2.5.1.
I doubt there are much to win by using an index, the ICD10_PURRES p (lookup) table contains only 112 rows, each of which contains one integer, one char(4) and one char(3) field.
Set