Hi all,

I´m, again, trying to understand more the Firebird's PLANs... so this question raised.

I'm trying to know what was the sum of products in my store on let´s say, 30 days before.
Let´s sai that I had a table with products [PRODUTOS P], a table with outgoings (like sells, etc..) [SAIDAPRODUTOS Spt], a table with ingoings (like buy to stock,...) [ENTRADAPRODUTOS Ept], and a table with products devolutions (maybe I sell it, but someone don´t like it, and take it back).
So, its easy. Take number of products I have today, sum products I selled, minus products I buyed and minus products that was delvolved. Presto!
In a fast way I created this query:

(DATA is the date of the moviment,
and DATA_INI the day I want to know how many products I had)

select
p.CODPRODUTO,
p.DESCRICAO AS DESCRICAO,
(p.ESTOQUE + coalesce((SELECT
SUM (spt.qtde)
FROM SAIDAPRODUTOS SPt
INNER JOIN SAIDANOTAS sntt ON SPt.CODSAIDANOTA = sntt.CODSAIDANOTA
WHERE (SPt.CODPRODUTO = p.codproduto) AND (sntt.DATA >= :DATA_INI ) AND sntt.PEDIDO = 0
),0)
- coalesce((SELECT
SUM (ept.qtde)
FROM entradaprodutos EPt
inner join entradanotas ett on EPT.codentradanota = ett.codentradanota
WHERE (ePt.CODPRODUTO = p.codproduto) AND (ett.DATA >= :DATA_INI)
),0)
- coalesce((Select sum(qtde)
from devprodutos dvp
where (dvp.codproduto = p.codproduto) and (dvp.data >= :DATA_INI)
),0)
) as estoquenoDia

from PRODUTOS P

the plan generated
Plan
PLAN JOIN (SPT INDEX (SAIDAPRODUTOS_IDX3), SNTT INDEX (RDB$PRIMARY29))
PLAN JOIN (SPT INDEX (SAIDAPRODUTOS_IDX3), SNTT INDEX (RDB$PRIMARY29))
PLAN JOIN (EPT INDEX (RDB$FOREIGN55), ETT INDEX (RDB$PRIMARY17))
PLAN JOIN (EPT INDEX (RDB$FOREIGN55), ETT INDEX (RDB$PRIMARY17))
PLAN (DVP INDEX (RDB$FOREIGN51))
PLAN (DVP INDEX (RDB$FOREIGN51))
PLAN (P NATURAL)

first question:
Why SPT, EPT AND DVP was duplicated in plan? I was expecting or one time (because temp tables) or manymanymany times (because to each row in products table they should run the select again).

well, in mean while, I thinked "if for each row it did the subselect, may I run this tables separated and join it?" So I did another SQL:

select
p.CODPRODUTO,
p.DESCRICAO AS DESCRICAO,
CAST(:DATA_INI AS DATE) AS DATAINI,
p.estoque + coalesce(saidas,0)-coalesce(entradas,0) - coalesce(devolucoes,0)
from produtos p
inner join
(SELECT
spt.codproduto,
SUM (spt.qtde) as saidas
FROM SAIDAPRODUTOS SPt
INNER JOIN SAIDANOTAS sntt ON SPt.CODSAIDANOTA = sntt.CODSAIDANOTA
WHERE (sntt.DATA >= :DATA_INI ) AND (sntt.PEDIDO = 0)
group by spt.codproduto) sai on p.codproduto = sai.codproduto
inner join
(SELECT
ePt.CODPRODUTO,
SUM (ept.qtde) as entradas
FROM entradaprodutos EPt
inner join entradanotas ett on EPT.codentradanota = ett.codentradanota
WHERE (ett.DATA >= :DATA_INI)
group by ePt.CODPRODUTO) entra on p.codproduto = entra.codproduto
inner join
(Select
dvp.codproduto,
sum(dvp.qtde) as devolucoes
from devprodutos dvp
where (dvp.data >= :DATA_INI)
group by dvp.codproduto) devolve on p.codproduto = devolve.codproduto

the result plan:
Plan
PLAN JOIN (MERGE (SORT (DEVOLVE DVP ORDER RDB$FOREIGN51), SORT (SORT (JOIN (ENTRA ETT NATURAL, ENTRA EPT INDEX (RDB$FOREIGN56)))), SORT (SORT (JOIN (SAI SNTT NATURAL, SAI SPT INDEX (SAIDAPRODUTOS_IDX1))))), P INDEX (RDB$PRIMARY27))

so, more questions:
Don't understand very well the MERGE and SORT in plan...
when it said that, for example, (ENTRA ETT NATURAL) that is because it could not use index for table ETT? why not used ETT INDEX (RDB$PRIMARY17) in first select?
Anyway, this second Select should be faster than first, right?
sugestions to do it in another third way? (no changes in table please)

if there's more info about this in old threads or some docs please, let me know. I didn't find it cleary.

TIA