Re: WIP: generalized index constraints

On Mon, 2009-07-06 at 08:50 -0700, Jeff Davis wrote:
> On Mon, 2009-07-06 at 11:56 +0100, Simon Riggs wrote:
> > I think it will be useful to separate the concepts of a constraint from
> > the concept of an index. It seems possible to have a UNIQUE constraint
> > that doesn't help at all in locating rows, just in proving that the rows
> > are unique.
>
> That would be interesting. Do you have a use case? Checking the
> constraint would surely be slower in a lot of cases.
>
> I could imagine different constraint-checking schemes that could be fast
> against a heap. For instance, if it's greater than the max or less than
> the min value, that would be cheap to check. That might be an
> interesting way to handle the constraint for a sequence-generated
> column, or timestamp column that is always ascending.

Yes.

> However, the problem is I don't see a lot of room for a practical use
> case. In the above situations, you'd almost certainly want indexes
> anyway: what's the point of a sequence number unless you're going to do
> lookups? And if you have an ascending timestamp column, I would think
> that you might do range lookups occasionally (which will be even better
> because the heap will be clustered).

In many cases, people add unique indexes solely to allow replication to
work correctly. The index itself may never be used, especially in high
volume applications.

How do you handle uniqueness within a stream? Presumably it is possible
and useful to have a stream of data that can be guaranteed unique, yet a
stream would never be uniquely targeted for lookups because of the
volume of data involved.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support