Re: [Firebird-test] FB 2.0 Alpha 3 - Doubts in optmizer improvements

[Steve L. <st...@sl...>]

STARTING WITH and LIKE queries are not treated the same in the optimizer.

Try this query it would be interesting to see what the plan looks like.

Select
  *
from
  TituloReceber TR join
  TituloReceberBaixa TRB on (TRB.TituloReceberID = TR.TituloReceberID) join
  Empresa_CredorDevedor ECD on (ECD.Empresa_CredorDevedor_ID = 
TR.EmpresaCredorDevedorID) join
  MultiEmpresa ME on (ME.MultiEmpresaID = TR.MultiEmpresaID)
where
  ECD.Nome STARTING 'Alpha Corantes'


Alexandre Benson Smith wrote:

> Guys,
>
> I have played a bit with FB 2.0 alpha 1, but now downloaded the alpha 
> 3 version. The main focus os my tests will be the new INTL module with 
> special attention to the new PT_BR collation.
>
> I have restored a 1.5 database and so far all appears ok !
>
> The other feature I like to look (not to test since I don't have 
> enough knowledge to stress test it) is the Optimizer improvements.
>
> I was looking at the Release Notes and found this:
> "Better support for STARTING WITH"
>
> I'd like to know a bit more about this feature. I will present an 
> example:
> Select
>   *
> from
>   TituloReceber TR join
>   TituloReceberBaixa TRB on (TRB.TituloReceberID = TR.TituloReceberID) 
> join
>   Empresa_CredorDevedor ECD on (ECD.Empresa_CredorDevedor_ID = 
> TR.EmpresaCredorDevedorID) join
>   MultiEmpresa ME on (ME.MultiEmpresaID = TR.MultiEmpresaID)
> where
>   ECD.Nome like 'Alpha Corantes%'
>
> PLAN JOIN (TRB NATURAL, TR INDEX (PK_TITULORECEBER), ME INDEX 
> (PK_MULTIEMPRESA), ECD INDEX (PK_EMPRESA_CREDORDEVEDOR))
> around 79k reads on mos tables
>
> but when I change the LIKE to "="
> Select
>   *
> from
>   TituloReceber TR join
>   TituloReceberBaixa TRB on (TRB.TituloReceberID = TR.TituloReceberID) 
> join
>   Empresa_CredorDevedor ECD on (ECD.Empresa_CredorDevedor_ID = 
> TR.EmpresaCredorDevedorID) join
>   MultiEmpresa ME on (ME.MultiEmpresaID = TR.MultiEmpresaID)
> where
>   ECD.Nome = 'Alpha Corantes'
>
> I got this plan
> PLAN JOIN (ECD INDEX (SK_EMPRESA_CREDORDEVEDOR_NOME), TR INDEX 
> (FK_TRECEBER_ECD), TRB INDEX (FK_TRECEBERBAIXA_TRECEBER), ME INDEX 
> (PK_MULTIEMPRESA))
> around 800 reads on most tables.
>
> The later was the expected plan for the first case. the 
> SK_Empresa_CredorDevedor_Nome index is very good (RDB$STATISTICS for 
> this index is 0,000321336759952828)
>    Index SK_EMPRESA_CREDORDEVEDOR_NOME (3)
>    Depth: 2, leaf buckets: 5, nodes: 3130
>    Average data length: 7.00, total dup: 18, max dup: 1
>    Fill distribution:
>         0 - 19% = 0
>        20 - 39% = 0
>        40 - 59% = 0
>        60 - 79% = 0
>        80 - 99% = 5
>
> AFAIR STARTING WITH has lower precedence (don't know if it's the 
> correct term) to specify the join order and index selection, is the 
> improvement mentioned above a better handling of this type of query ? 
> If so, why the index was not used in the first case ?
>
> I am ready to provide more information if someone need it.
>
> The optimization done in views with agregates was really fantastic ! :-D
>
> Good job guys !
>
> see you !
>