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?

On Aug 21, 2015, at 3:05 PM, Dmitry Yemanov wrote:

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.

Almost certainly for "exists" and "in".  The world where people expect subqueries to be faster than joins confuses me, but it exists (or is in the set of real programmers).

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

Interesting.  When we used the "one index entry per value" rules, maintenance was reasonably cheap, but the algoithm was fragile and subject to more problems as processors got faster.  I suspect that there's a solution to that problem short of adding 128 bits to every index entry.

My thought was that primary key indexes rarely change and are

particularly useful for junction records. 

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.

Right.  Prefix compression might do something for the TID length... or storing the delta from the creating TID for the destroying TID...  Jim's position (and mine, no surprise) is that the benefits aren't worth the overhead in the general case.   I suspect that Postgres went the other way.  They store their record versions oldest to newest (rather than vice versa) and (IIRC) their indexes point to the version, not the primary record.  

Anyway, not to distract you from V3 issues....

Cheers,

Ann