Re: the big picture for index-only scans

On Sat, Aug 20, 2011 at 5:06 AM, Gokulakannan Somasundaram
<gokul007(at)gmail(dot)com> wrote:
> By your argument, we can say that no-one will create a index with a function
> like random(), time(), date(), broken operators etc. Its hard to imagine a
> index in which a a user will only want to insert and never select on it.

The whole point of this optimization is to make index access cheaper,
not more expensive. You seem convinced it's done the opposite, but
you haven't offered much evidence (or any test results) to support
that proposition.

> Even C++ provides std::map infrastructure for objects, where the user owns
> the responsibility of writing the operator< correctly. Other databases do
> the same. Even going one step ahead, we are already going back to such
> indexes, if there is unique constraint/ referential integrity constraints.
> But with all such caveats, it was decided we should not allow covering
> indexes, as they require going back to the index for updates/deletes.

What we decided NOT to do is put xmin/xmax/cid into the index tuple,
for precisely the reasons you mention. That would be catastrophic
both for write operations to the table, and for the size of the index.
This approach is appealing precisely because a single visibility map
page can cover a very large chunk of the heap. Is there a potential
problem buried somewhere in there? Maybe. But if there is, I'm
pretty sure it's something far less obvious than what you seem to be
worried about.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company