Are you sure? There's no reason why

select
sum(paritypos)
from ParityEx
where 1=1 and BeginParityDate between '2007/12/01' and '2008/11/30'

should perform much slower than your original statement. However, a
known trick to prevent the optimizer from choosing a particular index,
is to use

select
sum(paritypos)
from ParityEx
where 0=1 or BeginParityDate between '2007/12/01' and '2008/11/30'

and this statement cannot use an index since 0=1 isn't indexed and may
be much slower than your original statement.

This can be a very useful trick sometimes when the optimizer otherwise
chooses a suboptimal plan, and there are lots of programs that would
suffer if the optimizer simply ignored things that always would evaluate
to false and chose the same plan whether this was included in the select
or not. Since there are no sensible reason for comparing two constants,
such a modification would have drawbacks without any benefits - so I
seriously doubt that it will ever be implemented!

Having said that, a 'wizard' should only add this when it knows
something about the data that the optimizer cannot know, e.g. it would
be sensible to add '0=1' to your statement if ParityEx only contained
data for the last two years so that the condition would be met by the
majority of records. If the table contains records for the last 1000
years, adding '0=1' would make things a lot slower.

So maybe your wizard should be improved, but I don't think anything
should change in Firebird to adjust it to programs using Firebird in an
inappropriate manner.

Set

kokok_kokok wrote:

> I have a simple sql statement:
>
> select
> sum(paritypos)
> from ParityEx
> where BeginParityDate between '2007/12/01' and '2008/11/30'
>
> It takes 400 mseg for 6000 records.
>
> The problem is the sql statement is created by a "wizard", for tech
> questions, sometimes it adds 1=1, I thought that the SQL optimizer of
> Firebird optimizes scenarios like this:
>
> select
> sum(paritypos)
> from ParityEx
> where 1=1 and BeginParityDate between '2007/12/01' and '2008/11/30'
>
> but the above statement takes 5000 mseg, 12.5 times more!
>
> I am using FB 2.0, does somebody know if it will fixed in a future
> version? or it is a usual behavior, is there a way to avoid this problem?
>
> Thank you