--- In firebird-support@yahoogroups.com, "laurenz_brein"

> > One of the impacts of a generational architecture is that inserts
> > are cleaner than with locking systems, but selects can be slower
> > since it is not possible to do an index-only scan.
> > A generational system is preferred for systems that mostly
> > insert rows (journaling accounting systems), whereas a locking
> > system performs better where you are mostly retrieving data (data
> > warehousing).
> >
> > Hope this helps.
>
> It sure does. Indeed I notice that the time taken for 'index scans'
> is comparable to that for 'table scans'.
>
> Do you think that the difference you mention can account for
> a factor of 4 for 'simple selects' compared to the worst competitor
> and a factor of 6 for 'table scans'?
>

Two out of four of your "simple selects" were "select count (*) from
table where x>y".

On a locking architecture, this operation does a traversal of a B+
tree index. In a locking architecture, the first index page with a
matching leaf is the only place where you actually would need a loop
to count the rows. After that, the index page's leaf entry count
could be taken and added to the count from the previous iterations.
(1 I/O per index page)

In a generational database, you can narrow down your index pages by
finding the first page with a matching leaf node, just like the
locking architecture does. After that, you must loop through every
index entry and check against the table to see if the indexed row is
visible to the current transaction. Not only do you get double I/O,
but you may end up thrashing your hard drive with seeks. (1 I/O per
index page) + (<= 1 I/O per indexed row) easily adds up to a factor
of 6 or more times over locking architectures for a count(*)
operation.