Re: Disallow unique index on system columns

On 15 April 2016 at 13:56, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> David Rowley <david.rowley@2ndquadrant.com> writes:
>> On 15 April 2016 at 13:30, Andres Freund <andres@anarazel.de> wrote:
>>> What'd be the point of indexing ctid, and why would it be correct?
>>> Wouldn't, hm, HOT break it?
>
>> I don't personally see the point.
>
> An index on ctid is useless by definition: if you know the ctid of
> a tuple, you can just go get it, never mind the index.

I'm not sure that's 100% accurate, and perhaps it's not worth arguing,
as they're likely broken because of HOT anyway, but it does seem like
you've totally disregarded the fact that a TIDscan does not support
range scanning, where an index scan on ctid would.

E.g; how many live tuples are on page 0?

select count(*) from t where ctid between '(0,0)' and '(0,10000)';

I'm not saying it's going to be a common case. I just want to ensure
we've considered all semi realistic use cases before we go and turn
this off.

-- David Rowley                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services