Richard,

> > Since you haven't specified any WHERE criteria, you are asking for

the

> > complete table.
>
> Not quite - I am asking for the unique combinations of dimension
> values, which can be obtained from the indecies. The query returns
> 1932 rows, which is less than 5% of the rows in the table. Plus, the
> five columns of this dimension are not as wide as the entire table.
>
> > That being the case, it is always faster to perform a natural scan

and

> > sort rather than walk an index.
> >
> > Why is it faster?
> >
> > Because the index will be sorted in value order, and not page order.
> > This means that while walking the index the engine would cause the
> > HD to
> > thrash as the engine reads the appropriate data page for each
> > record in
> > the index.
>
> This is why I asked if hierarchal levels benefit from composite
> indecies - I was wondering if this was a clue to the optimiser that
> this is a hierarchy. I understand that if we had to read every row
> in random order, it would be a performance nightmare, but we actually
> only need to read < 5% of the row data, and IIRC that data is ALL in
> the indecies. But as Adam points out, this is an unusual case and
> maybe the optimiser does not notice it. And that is a fine answer.

The engine MUST READ read ALL records, since the index structures
contain data values which apply to all active transactions (Ann forgive
me if I'm describing this incorrectly). As such, each record must be
read to confirm whether the data values (visible for this transaction)
meet the query criteria.

You have made an assumption regarding the format/content of the FB
indexes and how they are processed.

> > Why does it read the records?
> >
> > Because Firebird is a multi-generational engine, which means that

the

> > indexes contain data which may be 'dirty', the engine needs to read
> > the
> > records to confirm the data values (for this transaction).
>
> Jet was simply the engine I would have expected it to beat, although
> I appreciate that it does not have the transactional overhead that FB
> has. But MySQL 4/5, Postrges 8, SQL Server 2000/2005 and Oracle 9i/
> 10g also outperform FB2 by a significant margin on this one test.

With the exception of Postgres and Oracle none of the other databases
are multi-generational. This makes a big difference, heck MySQL 4 isn't
even transactional.

> The Postgres server is running on the same machine, so it is a

tolerably

> fair comparison.

Agreed, the comparison is fair.

> And just to assuage any wounded partisan feelings: while Postgres
> actually performs the best on this particular test, it has its own
> set of outliers that I need to investigate.

Firebird like all databases has some very good points, the benchmarks I
have seen shows that it outperforms Postgres for some operations, and
it's bad points, you have found 10 out of 1000 (1% 'failure' rate pretty
good as I see it, it would be better if it were .01%).


I don't think anyone took offense to your question, but perhaps to your
pre-conceived notion of how FB works and the fact that you didn't really
want to listen to the explanations we were providing.


Sean