woodsmailbox wrote:

> A huge sort table gets created (and lousy performance) depending on
> what I put in the select list, not what I put in the order-by clause.
> SORT is the root of the plan, but why does it include other columns
> that are not included in the order-by clause in it? I'd wish it would
> choose to retrieve the other values _after_ the SORT. Currently, I
> trick the optimizer by creating a view on top, but this trick might
> not work in a future version of firebird and I'll be back where I
> started.

Generally, it is much faster to retrieve records in storage order
than to access them randomly. Unless you happen to store records
in the order that you're sorting them, selecting after the sort
will produce much more I/O on the database than you're currently
seeing on the sort file.

>
> Btw, in fb 2.1.2 or 2.5, does the optimizer try to inline stacked
> views (views that select from views)?

Yes, when possible, which it is if the inner views are simple joins,
meaning that they don't includes group by or aggregation. That's
been the case since Firebird was InterBase.


Good luck,

Ann