* Jim Nasby (jim.na...@bluetreble.com) wrote: > On 7/28/16 10:05 AM, Alex Ignatov wrote: > >>Just curious: what if PostgreSQL supported index that stores "primary > >>key" (or unique key) instead of tids? > > > >You mean IOT like Oracle have? > > IIRC, IOT either stores the table in index order, which is something > different.
IOT is definitely an interesting idea that I'd like to see us pursue, but I agree that it's something different. > What Alex is proposing is an index method that stores a datum > instead of a ctid. You would then use that datum to probe a > different index to get the ctid. Or put simply, you have a PK index > that contains ctid's, and a bunch of other indexes that contain a PK > value instead of ctid's. Right, that's the MySQL approach, which has advantages and disadvantages. > I think it's an idea worth pursuing, but I don't see how you can > make it work with our MVCC system unless we drop the aversion to > scanning back into an index as part of an update. I'm not terribly excited about the MySQL approach, personally, but I really like the idea of trying to make HOT updates smarter and allow HOT updates for indexes which don't include TIDs, as Robert and Alvaro are discussing. Another thought that was kicking around in my head related to that is if we could have indexes that only provide page-level information (similar to BRIN, but maybe a btree) and which also would allow HOT updates. Those indexes would typically be used in a bitmap index scan where we're going to be doing a bitmap heap scan with a recheck, of course, though I wonder if we could come up with a way to do an in-order bitmap index scan where we sort the tuples on the page and then perform some kind of mergejoin recheck (or just pull out whatever the lowest-not-seen each time we sort the tuples on the page). All very hand-wavy, of course, and it'd make sense to make the concept work for BRIN before we consider anything else, but it seems like there could be a use-case for allowing indexes other than BRIN to be built in a way that allows HOT updates to happen, thus eliminating the cost of having to update those indexes when the tuple is changed, in many cases. Of course, those indexes couldn't be used UNIQUE indexes or used for primary keys, and adjusting the parameters to a BRIN index you could possibly get a similar index, but this might allow such an index to still be usable for index-only scans, which a BRIN index will never be able to provide. Thanks! Stephen
signature.asc
Description: Digital signature
