21.08.2015 17:39, Ann Harrison wrote:

> So, is it worth making index access slower and index maintenance harder
> to speed up count(*) and multi-way joins using junction tables?

I don't worry much about these two cases. However, I suspect that index
coverage could in fact affect more real-world queries than just
multi-way joins.

What is way more important for me is the hope for [dramatically] faster
GC in indices.

> My thought was that primary key indexes rarely change and are
> particularly useful for junction records. If primary key - or all unique
> indexes - carried transaction information and other indexes did not,
> Firebird could support an index-only count(*) without looking at data.
> It also handles joins like "students s join registrations r on
> r.student_id = s.student_id join courses c on c.course_id = r.course_id"
> without reading the Registration table.

At the other side, primary keys (well, at least generator-based ones)
are usually prefix-compressed quite well. Adding even one transaction id
to the node would double the index size, increasing the I/O overhead for
other queries.


Dmitry