Conclusion:

> 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)

This will always work, as Firebird is designed internally to use the
"fast index".

> 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)

The above will NEVER work: it will always use a scan on table-columns
A.IDSUBARTI. Avoid this if you need speed.

What will always work?

This is the way to build the above query:

> 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 te on te.idsub2=a.idsubarti and
te.idsub1= :ids1

> left outer join exiareas ex on ex.idsubarti=a.idsubarti
> left outer join areas b on b.idarea=ex.idarea

Notice that the JOINS come BEFORE the left outer joins. Any JOIN
placed AFTER the "LEFT OUTER JOINS" will cause FIREBIRD to use "WRONG
INDEXES". So, if you have a VIEW that has "left outer joins" and then
you intend to make a JOIN to such view, your new query will NOT USE A
fast index.

ONE MORE VERY IMPORTANT THING:

If your database statistics are too old, even changing to the last
version of the query WILL NOT make the proper (fast) index to be used.
If this happens, you need to execute:

SET STATISTICS INDEX <INDEX TO BE USED FOR TABLE>

After the above statement, then try the "fast version" of the query.
it will use the proper index and things will continue to run
smoothly... until you run into another strange thing and invest 3 or 4
days to find the solution!

I hope this will be useful for some.

Franz J Fortuny L