Re: Reducing tuple overhead

On Thu, Apr 30, 2015 at 9:46 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> As the index expression contain table columns and all the functions
> or operators used in expression must be IMMUTABLE, won't that
> guarantee to avoid such a situation?

The concern is that they might be labeled as immutable but not
actually behave that way.

The other, related problem is that the ordering operator might start
to return different results than it did at index creation time. For
example, consider a btree index built on a text column. Now consider
'yum update'. glibc gets updated, collation ordering of various
strings change, and now you've got tuples that are in the "wrong
place" in the index, because when the index was built, we thought A <
B, but now we think B < A. You would think the glibc maintainers
might avoid such changes in minor releases, or that the Red Hat guys
would avoid packaging and shipping those changes in minor releases,
but you'd be wrong. We've seen cases where the master and the standby
were both running RHEL X.Y (same X.Y on both servers) but they didn't
agree on the collation definitions, so queries that worked on the
master failed on the slave.

> If not, then I think for such indexes we might need to search
> for tupleoffset in the entire index and mark it as Delete or may be
> for such indexes follow the current mechanism.

I think that *is* the current mechanism.

> I think it will still
> give us lot of benefit in more common cases.

It's hard to figure out exactly what can work here. Aside from
correctness issues, the biggest problem with refinding the index
tuples one-by-one and killing them is that it may suck for bulk
operations. When you delete one tuple from the table, refinding the
index tuples and killing them immediately is probably the smartest
thing you can do. If you postpone it, somebody may be forced to split
the page because it's full, whereas if you do it right away, the next
guy who wants to put a tuple on that page may be able to make it fit.
That's a big win.

But if you want to delete 10 million tuples, doing an index scan for
each one may induce a tremendous amount of random I/O on the index
pages, possibly visiting and dirtying the same pages more than once.
Scanning the whole index for tuples to kill avoids that.

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