🚀 go-pugleaf

Hs wrote:

> What is happening? Generaly what is the difference in resuls and in
> background between calling HAVING or WHERE for non aggregation fields?

In case one the optimizer elected to read farmer in natural order and
use the primary index of buy to locate the record in buy that matched
the current record in farmer. This means that every record in buy was
located in the index then read from disk before the WHERE conditions
could be applied.

In case two the optimizer elected to sort farmer, sort buy then merge
the two streams. Although you cannot tell from the plan the WHERE and
HAVING conditions were probably applied to buy as it was read in
natural order so only the matching records had to be sorted and merged.
This resulted in much less I/O than locating all of the buy records one
at a time through the index.

Why did the optimizer elect to use the index in one case and do a sort
merge in the other? Only one (or possibly two) people know and they are
in InterBase R&D.:)

-- 
Bill Todd (TeamB)