Well - you are right

Adding the +0 to the date gives me a PLAN without the TRANS_DATO index being used.
now it is equally fast.

What I dont get is why the optimizer chooses to use TRANS_DATO in the last one, and thereby making it slower just becaue I have an DEB=x or DEB=y.


But you are right about the join part - I ommited something, should have ommited the join as well. Gives no difference though

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> Hei Michael!
>
> Your problem is that the optimizer thinks the debnr index is somewhat selective, but not more so than using the index for dato would be a beneficial if you check more than one debnr (after all, how many dates can be before the end of last month?). The quick-fix way is to change to "and t.dato+0<'2010-11-30';" A better and more permanent solution would be to upgrade to a Firebird version that support the use of histograms, though that might take a little while since it isn't programmed yet (I think it was scheduled for 3.0 and not part of 2.5?)
>
> By the way, I expect this to only be part of your real SQL statement, since I see no good reason for including betalingsvej (well, unless you want to double what customers with two betalingsvej's pay).
>
> HTH,
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of mivi71dk
> Sent: 3. desember 2010 12:45
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] SQL, Plan and why
>
> Hello
>
> I have this:
>
> select
> Sum (
> (case when
> (t.art IN (3,45))
> then
> (t.salgkr*-1)
> else
> (t.salgkr)
> end)
> ) as StartSaldo
> from transaktioner t
> left join betalingsvej on (
> betalingsvej.betvej_navn=t.betvej and
> betalingsvej.AFDELING_ID=t.afdeling_ID and
> betalingsvej.uafd_Navn=t.UAfd_Navn
> and (betalingsvej.betvej_spec_betvej=5)
> )
> where
> (t.debnr='1712' or t.debnr='1712 ') and
> (t.art in (3,18,45) or ((art=23)))
> and t.dato<'2010-11-30';
>
>
> with this plan
>
> PLAN JOIN (T INDEX (TRANS_DEBNR, TRANS_DEBNR, TRANS_DATO), BETALINGSVEJ INDEX (PK_BETALINGSVEJ))
>
> Its some 2 seconds to execute.
>
> If I chnage it to this (ommits an OR in the where clause)
>
> select
> Sum (
> (case when
> (t.art IN (3,45))
> then
> (t.salgkr*-1)
> else
> (t.salgkr)
> end)
> ) as StartSaldo
> from transaktioner t
> left join betalingsvej on (
> betalingsvej.betvej_navn=t.betvej and
> betalingsvej.AFDELING_ID=t.afdeling_ID and
> betalingsvej.uafd_Navn=t.UAfd_Navn
> and (betalingsvej.betvej_spec_betvej=5)
> )
> where
> (t.debnr='1712') and
> (t.art in (3,18,45) or ((art=23)))
> and t.dato<'2010-11-30';
>
>
>
> I get this PLAN:
>
> PLAN JOIN (T INDEX (TRANS_DEBNR), BETALINGSVEJ INDEX (PK_BETALINGSVEJ))
>
> And instead of 2 seconds it takes 0.059 seconds to execute.
>
> I our cause I have to use the first one. At least for now.
> But I would like to use the last one.
> The report we are doing is at the moment some 14 minutes to complete.
> Could I get the first to be as fast as the second it would only take 25 seconds
>