Change last line:

where a.idsubarti in (select idsub2 from tor_equiv where idsub1 = :ids)



with this one

where exists (select 1 from tor_equiv where a.idsubarti= idsub2 and idsub1 =
:ids)



Regards,
Paul



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Franz J Fortuny
Sent: Tuesday, July 03, 2007 11:37 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Query plan insists on NOT USING an index



Consider first the following 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
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)

When this query is executed, it takes no time at all (miliseconds) and
returns several rows. Part of the plan for this query is to use the
PRIMARY KEY of the SUBARTIS table.

Now, consider the following 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
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)

It is IDENTICAL to the previous one, except that the second query
generates EXACTLY the same values as the first query from the
execution of the SUBQUERY

select idsub2 from tor_equiv where idsub1 = :ids

(Which executes instantaneously).

Of course, my application needs the SECOND form of the query, not the
first.

However, using the second form of the query, Firebird 2.0 forces a
plan that MAKES A SCAN of the 126,000-element SUBARTIS table.

Even if you try to force the plan to include the primary key of the
SUBARTIS table, Firebird 2.0 will respond that "it is not possible to
use such index to run this query".

I suppose this is a problem for the developers to check upon, since it
is obviously a BUG in Firebrid 2.0, build 12748-0 (last non-beta version).

Even with this variation of the second query, it won't work properly
(that is, it will make a SCAN of SUBARTIS):

...
left outer join areas b on b.idarea=ex.idarea
join tor_equiv te on te.idsub1=:ids and te.idsub2= a.idsubarti

There goes my contribution for somebody else not to take ages trying
to find the problem.

FJFortuny





[Non-text portions of this message have been removed]