| Subject | Re: slow query |
|---|---|
| Author | Stephen Boyd |
| Post date | 2009-08-11T12:21:23Z |
--- In firebird-support@yahoogroups.com, Dmitry Yemanov <dimitr@...> wrote:
That puts a whole new complexion on the way I should be structuring my queries. Up until now, I have been relying on the optimizer except in a very few exceptional circumstances where really complex queries caused the optimizer to create a sub-optimal plan. But this tells me that I should be specifying a plan whenever I want to retrieve a small, ordered result from a large domain. Hmmmm! That makes dynamically generated SELECT statements a bit more problematic. I would, in effect, have to code my own optimizer.
>So in the case where one is retrieving a small, ordered result set from a large dataset (realizing that small and large are relative terms) it would always be more efficient to sort rather than to order by an index?
> It's simply lack of its intelligence. ORDER vs SORT is always a
> heuristic decision, not a cost based one. In your case, with a very
> selective search condition, it looks obvious to use SORT instead, but
> the optimizer is not that clever (yet).
>
That puts a whole new complexion on the way I should be structuring my queries. Up until now, I have been relying on the optimizer except in a very few exceptional circumstances where really complex queries caused the optimizer to create a sub-optimal plan. But this tells me that I should be specifying a plan whenever I want to retrieve a small, ordered result from a large domain. Hmmmm! That makes dynamically generated SELECT statements a bit more problematic. I would, in effect, have to code my own optimizer.