Subject Re: [firebird-support] Re: Query plan insists on NOT USING an index
Author Alexandre Benson Smith
Franz J Fortuny wrote:
> --- In, Dmitry Yemanov <dimitr@...>
> wrote:
>> What is the reported plan? Do you have an index for tor_equiv.idsub1?
> The reported plan uses indexes in every single relation, EXCEPT where
> it should be using the primary key of table SUBARTIS, which is a very
> huge table (126,000 rows or more).
> TOR_EQUIV.IDSUB1 AND ...IDSUB2 are BOTH columns that reference the
> primary key of SUBARTIS.IDSUBARTI.
> It scans 6 times all 126,000 rows in order to generate the 6 rows from
> SUBARTIS that the IDSUB2 column of TOR_EQUIV indicates. IDSUB2 is a
> column that has as foreign key the primary key of SUBARTIS.
> The index it is NOT USING is the one corresponding to table SUBARTIS
> (as reported by the PLAN). If I try to force it to use the index that
> it should use, it responds that IT CAN NOT USE THAT INDEX IN THAT
> QUERY. I don't see why not!
> FJFortuny

If you had read carefully what others have said, you would notice that
the query with IN (sub-select) is transformed internally to a correlated
EXISTS where the first part would use NATURAL.

Try the suggestion that Set gave to you, I am sure it will be a lot faster.

see you !

Alexandre Benson Smith
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil