Hi Juarez!

This query cannot use any index for PRODVENDA since it has to be the
first table in your PLAN (all other tables are LEFT JOINed) and you do
not have a limiting WHERE clause. I guess whatever program produces
your red graph may think that using NATURAL in the plan is a bad
thing. Well, if you're really interested in all rows, then your query
is OK and cannot be improved (well, remove VENDA V since that doesn't
contribute to the answer). But don't answer this question too lightly
- it is far too common to come from a desktop database background and
return more rows than necessary. Ask yourself the following questions:

Do you really need LEFT JOIN?
If the records having NULL in NOMESETOR, CATEGORIA or SUBCATEGORIA
isn't interesting, then JOIN is preferable to LEFT JOIN. Likewise, if
all P.CODPRODUTO exists in PCAD.CODPRODUTO, then use JOIN and not LEFT
JOIN (the same applies to other tables).

Does this query return some rows that aren't interesting?
If so, try to find their common denominator and rewrite your query so
they're not part of the rows returned.

The fact that this query actually does LEFT JOIN VENDA V when that
table doesn't contribute to the result at all, does indicate that you
too easily use LEFT JOIN and that you haven't examined your query
carefully enough.

HTH,
Set

--- In firebird-support@yahoogroups.com, "Juarez A. Mendes" wrote:
> I have the following query
>
> SELECT
> SE.NOMESETOR,
> CA.CATEGORIA,
> SC.SUBCATEGORIA,
> P.NOMEPRODUTO AS ITEM,
> P.CODIGOEAN AS CODIGO,
> SUM(P.TOTAL) AS TOTAL,
> SUM(P.QUANTIDADE) as QUANTIDADE,
> COALESCE(SUM(P.PRECOCUSTO),0) PRECOCUSTO,
> FROM PRODVENDA P
> LEFT OUTER JOIN PRODUTOS PCAD ON (PCAD.CODPRODUTO=P.CODPRODUTO)
> LEFT OUTER JOIN VENDA V ON (V.REFVENDA=P.CODVENDA AND
> V.DATAVENDAbetween :DATAINICIO AND :DATAFIM OR 2=0)
> and P.CANCELADO=0 and V.TIPOREGISTRO=1)
> LEFT OUTER JOIN SETOR SE ON (PCAD.CODSETOR=SE.CODSETOR)
> LEFT OUTER JOIN CATEGORIA CA ON
> (PCAD.CODCATEGORIA=CA.CODCATEGORIA)
> LEFT OUTER JOIN SUBCATEGORIA SC ON
> (PCAD.CODSUBCATEGORIA=SC.CODSUBCAT)
> GROUP BY 1,2,3,4,5
> ORDER BY 1,3,4
>
> her this working well but always when passing for a performance
> analysis I do verify that PRODVENDA always appears in red in the
> graph, did I already create indexes and did I turn off making
> several tests but does it always continue in red showing researches
> not indexed, does anybody know as could optimize that?
>
> Thanks
>
> Juarez A. Mendes