Hi Franz!

Constant values never change, so you know you can use an index.
Subselects may change their value for every row, and cannot use an index.

Sure, the simple

SELECT * FROM A WHERE A.FieldA IN (SELECT B.FieldA FROM B)

could be considered similar to constant values (though if B contained
considerably more records than A, modifying the optimizer could slow
things down, rather than speed it up), but

SELECT * FROM A WHERE A.FieldA IN (SELECT B.FieldA FROM B
WHERE B.FieldB = A.FieldB)

makes the result of the subselect change for every row of A and couldn't
possibly use any index for A.FieldA. These are the simple cases, I've
never tried to think of all the possible cases that Firebird will have
to handle.

I think the optimizer changes the simple select internally to

SELECT * FROM A WHERE EXISTS(SELECT * FROM B WHERE A.FieldA = B.FieldA)

Depending on the size of A and B and how many rows in them that matches
the criteria, this will be faster or slower than first getting all
values from B and then compare the result to A.FieldA.

The way Firebird works in this regard, is definitely not a bug. A bug is
something that prevent things from working correctly, this is just a
case of Firebird doing exactly what it is told to do in a case where it
theoretically could have been optimized to take shortcuts due to your
particular situation with no other indexed criteria on the larger table
A, not all too many records matching tor_equiv.idsub1 and no references
to A in your subselect. All three of these must hold true for there to
be a benefit from modifying the optimizer.

I generally prefer to cooperate with the optimizer rather than fight it,
and avoid writing my queries in ways that are unnecessarily time
consuming. Change your query to

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
join tor_equiv d on d.idsub2 = a.idsubarti
left outer join exiareas ex on ex.idsubarti=a.idsubarti
left outer join areas b on b.idarea=ex.idarea
where d.idsub1 = :ids

and things should speed up.

HTH,
Set

Franz J Fortuny wrote:

> --- In firebird-support@yahoogroups.com, Dmitry Yemanov wrote:
>> This is not a bug, Firebird just cannot handle the IN predicate
> another way.
>
> However, it does handle the in predicate PERFECTLY in the first case
> (where the list is provided):
>
> in (xxx,xxx,xxx,xxx,xxx,xxx)
>
> And the values generated by the subquery are EXACTLY the values that I
> placed inside the parenthesis for the IN Predicate to take care of.
>
> So, the problem might not be the IN PREDICATE, but the handling of the
> values generated by a subquery to be handled by the IN PREDICATE. This
> is where the BUG is located.
>
> BTW: I have tried the ALL OTHER IMAGINABLE FORMS (4 hours of trying)
> and I still don't get the darn thing to use the proper index, even if
> the JOIN of the tor_equiv table is the first one.
>
> Even if the TOR_EQUIV table is the first one and the others are joined
> to it: it will still NOT USE the SUBARTIS index.
>
> This is NOT reliable. Sorry.
>
> FJFortuny