Subject Re: [firebird-support] Re: Natural Plan when grouping by 5 indexed columns?
Author Richard Wesley
On Feb 1, 2007, at 16:10, Adam wrote:

> As a MGA database, the index nodes contain uncommitted inserts,
> records that your transaction can not see due to isolation, and
> deleted records yet to be garbage collected. It is not enough to look
> at the index. The data page shows whether your transaction can see a
> specific record.

Are any optimisations applied when a database is read-only?

> That said, if the index had 'poor enough' selectivity, it may be less
> expensive to do an indexed walk for distinctness, although you would
> get thrashing, it would possibly be in cases similar to yours faster.
> The distict operator does not try this optimisation (in 1.5.x anyway).
> I don't know whether it is just something not considered or whether
> there is some other logical fault that means it is unreliable, or
> whether it is just too hard to guess when to use which approach.

Yes, for historical reasons, our test data is unfortunately all flat
relations, so the selectivity is often poor. But this does not seem
to matter for dimensional filtering - I have found that even with as
few as three distinct values, indexing greatly improves filtering
performance. One of these days I will build a star schema pre-
processor for our data extractor and many of these silly issues will
go away...
Richard Wesley Senior Software Developer Tableau