Re: [HACKERS] Bug#48582: psql spends hours computing results it already knows (fwd)

Brian E Gallew <geek+@cmu.edu> writes:
> So, does the planner allow counting from a unique index (if one
> exists)?  In general, an index scan on a unique index should be faster
> than a table scan.  Of course, I'm sure someone already thought of this...

Vadim will have to check me on this, but I believe that index entries
don't contain transaction information --- that is, you can determine
whether a tuple matches a specified search key by examining the index,
but in order to discover whether the tuple is actually *valid*
(according to your transaction's worldview) you must fetch the tuple
itself from the main table.  So scanning an index cannot be cheaper than
a sequential scan of the main table, except when the index allows you to
avoid visiting most of the tuples in the main table.

There has been some discussion of allowing scans of indexes without
fetching the underlying tuples, but AFAICS that would mean replicating
the tuple transaction status information into (each!) index, which'd
be a big hit in both disk space and number of disk writes implied by
committing a tuple.  I've got my doubts about it being a win...
        regards, tom lane