Reread the answers you've been given Franz, IN <subquery> is very
different from IN <list of constants> - even if you think it should be
the same thing! Firebird translates IN <subquery> to EXISTS(<subquery>)
and cannot use an index for the field matching the subquery.

Sure, it is possible to change the design of Firebird so that these are
treated similarly, but I would expect (I'm not a Firebird developer,
though a long time Firebird user) that it would be at a cost that
probably includes making queries similar to yours execute slower (and
subselects referencing fields in the main select, simply cannot use an
index anyway).

Modifying your query so that it is a JOIN rather than an IN <subselect>
is likely to make the query fast again. The current implementation
cannot use the plan you think it should use, and modifying it like you
want would create both benefits and drawbacks (you obviously haven't
noticed the drawbacks). I would recommend you to change your coding
practises (it is easy to avoid using IN <subselect>), rather than saying
that it is a bug when Firebird tries to do exactly what you tell it to do.

By the way, you say that the subquery is executing fast. Are you aware
that this subquery is executed once for every row in A (126000 times)?

Set

Franz J Fortuny wrote:

> IN (QUERY):
>
> PLAN (TOR_EQUIV INDEX (UNQ_TOR_EQUIV_1))
> PLAN JOIN (AA INDEX (RDB$FOREIGN242, RDB$FOREIGN241), BB INDEX
> (RDB$PRIMARY119))
> PLAN JOIN (AA INDEX (RDB$FOREIGN242, RDB$FOREIGN241), BB INDEX
> (RDB$PRIMARY119))
> PLAN JOIN (JOIN
>
> (JOIN (A NATURAL,
>
> /* this is WRONG and this is what SHOULD NOT be happening! Why A
> (SUBARTIS) NATURAL? no way! That is what the index is for. If it does
> not work like that, it SHOULD work like that. To me that is a BUG. */

> C INDEX (RDB$PRIMARY131)), EX INDEX (RDB$FOREIGN229)), B INDEX
> (RDB$PRIMARY96))
>
> Now if IN (LIST) is used, then you get this plan:
>
> PLAN JOIN (AA INDEX (RDB$FOREIGN242, RDB$FOREIGN241), BB INDEX
> (RDB$PRIMARY119))
> PLAN JOIN (AA INDEX (RDB$FOREIGN242, RDB$FOREIGN241), BB INDEX
> (RDB$PRIMARY119))
> PLAN JOIN (JOIN (JOIN
>
> (A INDEX (RDB$PRIMARY179,
>
> RDB$PRIMARY179, RDB$PRIMARY179, RDB$PRIMARY179, RDB$PRIMARY179), C
> INDEX (RDB$PRIMARY131)), EX INDEX (RDB$FOREIGN229)), B INDEX
> (RDB$PRIMARY96))
>
> Here, as you can see, the plan is (A INDEX (RDB$PRIMARY179)...
>
> If I try to force that plan, then I get this:
>
> "index cannot be used in the specified plan.
> index RDB$PRIMARY179 cannot be used in the specified plan."
>
> Using plan # 2, the response is in miliseconds. Using plan # 1, the
> response is in 5 seconds, (pretty fast reading of 126,000 rows 6 times!).
>
> (Queries:
>
> # 1: generates the scan (natural?)
>
> select a.descri,a.idsubarti,
> coalesce(ex.existe,0.0),
> b.idarea,
> coalesce((
> select sum(unids) from facrens aa join facturas bb on
> bb.idfactura=aa.idfactura
> and aa.idsubarti=a.idsubarti and aa.idarea=b.idarea and bb.idfacedo
> in (2,12,25)
> ),0.0)
> as reser, a.serial,c.cosba,cast(c.pventa as
> numeric(12,2)),cast(c.pventa2 as numeric(12,2)),b.area
> from subartis a
> join nipres c on c.idarti=a.idarti and c.nipre=a.nipre
> left outer join exiareas ex on ex.idsubarti=a.idsubarti
> left outer join areas b on b.idarea=ex.idarea
> where a.idsubarti in (select idsub2 from tor_equiv where idsub1 = :ids)
>
> # 2: uses the correct index to join the tables
>
> select a.descri,a.idsubarti,
> coalesce(ex.existe,0.0),
> b.idarea,
> coalesce((
> select sum(unids) from facrens aa join facturas bb on
> bb.idfactura=aa.idfactura
> and aa.idsubarti=a.idsubarti and aa.idarea=b.idarea and bb.idfacedo
> in (2,12,25)
> ),0.0)
> as reser, a.serial,c.cosba,cast(c.pventa as
> numeric(12,2)),cast(c.pventa2 as numeric(12,2)),b.area
> from subartis a
> join nipres c on c.idarti=a.idarti and c.nipre=a.nipre
> left outer join exiareas ex on ex.idsubarti=a.idsubarti
> left outer join areas b on b.idarea=ex.idarea
> where a.idsubarti in (24858,32170,37405,42166,76894)
>
> ...
>
> The 5 values in the list are the five times that the plan says it will
> use RDB$PRIMARY179.
>
> If the query states:
>
> ... where a.idsubarti in (SELECT IDSUB2 from tor_quiv where IDSUB1=:ids)
>
> EMBEDS THE MESSAGE to the Firebird Engine that if A.IDSUBARTI(S) must
> be found, it should use the AVAILABLE index ON THAT SPECIFIC column.
> The subquery is executing fast (it is using TOR_EQUIV_UNQ_1 to
> generate the 5 IDSUB2's that should locate the SUBARTIS!).
>
> If we can't rely on this not working using the most efficient plan,
> this is then, unreliable!
>
> FJFortuny