regarding issues 2 - when addition of order by 1,2,3 clause increases
the reads count - well - I tried to put the select into stored
procedure and then make select from it. Well - this didn't changed
the picture - the count of reads remained approx the same and in
increased when the group by clause was added to 'select ... from
proc_name group by 1,2,3'....

I am using 1.5.5, but I guess - this is complex issue and it remains
intact across versions. If you can't fix it, you gotta stand it...

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

>
> You've forgotten something important, to tell us which version of

Firebird you are referring to, show us the select statements, the
plans generated and tell us how many records are returned (and
someone might be interested in your number of reads as well, I just
don't know enough about them to be very interested myself). Hence,
I'll only guess a partial answer...

>
> In Firebird 1.5 lets say I do
>
> SELECT MyPK
> FROM MyTable
> WHERE IndexedField = 1
>
> That will give a plan similar to
>
> PLAN (MyTable INDEX(IndexedField))
>
> If I change it to
>
> SELECT MyPK
> FROM MyTable
> WHERE IndexedField = 1
> ORDER BY MyPK
>
> I may see the plan change to
>
> PLAN (MyTable ORDER MyPK)
>
> This doesn't mean that Firebird doesn't use the index for

IndexedField, it is just on a 'lower' level that isn't surfaced in
the plan.

>
> Using the index for ORDER BY may or may not be beneficial. Try and

see for yourself whether it is useful in your case. You can prevent
it from being used by doing something like:

>
> SELECT MyPK+0 as MyPK
> FROM MyTable
> WHERE IndexedField = 1
> ORDER BY 1
>
> From Firebird 2 (I think, it might be 2.1), both levels of the PLAN

are surfaced and you can see both the index used for selecting and
ordering.

>
> HTH,
> Set
>
> --- In firebird-support@yahoogroups.com, "vincent_kwinsey" wrote:
> >
> > Hello!
> >
> > I am trying to find optimal select statement. Currently I have

only

> > indexed reads (no non-indexed reads), but I am still trying to
> minimize
> > this number - on some test cases I can see improvement some 10
> times -
> > for the number of reads and execution time as well.
> >
> > There are strange things:
> > - adding 'order by 1, 2, 3' clause removes all the optimization

and

> > there is max number of reads
> > - 'union' remove all the optimization as opposite to 'union all'.
> > In case of 'union' the total number of reads exceeds the sum of

reads

> > for individual selects (which are parts or 'union').
> >
> > Intuition says than in both situations there should be some fixed
> > amount of reads and the 'order' or 'union' should simply make some
> > sorting or elimination of duplications of some temporary set and
> > there should be no additional reads...
>