On 5.11.2015 19:38, setysvar setysvar@... [firebird-support] wrote:

>
> Den 05.11.2015 12:12, skrev Josef Kokeš j.kokes@...
> [firebird-support]:
> > SELECT COUNT(*)
> > FROM a_complex_view
> > WHERE field1=1 AND field2<>2
> > AND key_field IN (SELECT key_field FROM key_field_list)
> >
> > went from 2 seconds in FB 2.5.0-2.5.3 to 4 minutes in FB 2.5.4. After
> > rewriting the last condition to EXISTS:
> Nevertheless, I am surprised about the difference between 2.5.3 and
> 2.5.4 that you report. Your subselect is not correlated to the main
> select (i.e. you have no reference to a_complex_view inside your
> subselect), so in theory I'd expect it to be possible for the optimizer
> to change it to an EXISTS as part of its optimization (or isn't
> key_field_list a normal table?).
>

key_field_list in this case is a normal table.

I am not worried about the optimizer failing to convert the SELECT to
EXISTS - as far as I am concerned, that's primarily my responsibility
and I am perfectly willing to fix that. What worries me is that the
behavior changed so drastically in a minor upgrade to Firebird and I
can't find any reason what could have caused that.

Josef