--- In firebird-support@yahoogroups.com, Dmitry Yemanov <dimitr@...>
wrote:

> Again, can we see the plan?

Here is the plan, as reported by the statement as it is executed:

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