Re: Visibility map thoughts

Mark Mielke wrote:
> Simon Riggs wrote:
>> On Mon, 2007-11-05 at 09:52 +0000, Heikki Linnakangas wrote:
>> I'm racking my brain trying to think of a query that will benefit from
>> index-only scans without specifically creating covered indexes. Apart
>> from count(*) queries and RI lookups. I can't see RI lookups being much
>> cheaper with this technique, do you see something there
> I'm not sure what RI lookup is. Sorry. :-)

Referential Integrity. For example, if you insert a row to table Child,
that has a foreign key reference to table Parent, a RI trigger is fired
that checks the there's a row in Parent table for that key.

Unfortunately that lookup is done with "FOR SHARE", index-only scan
won't help because we have to go and lock the heap tuple anyway :(.

> My list would be:
> - EXISTS / NOT EXISTS
> - COUNT(*)

Yeah, those are good candidates.

> - Tables that are heavily updated - any case where the index entry often
> maps to a non-visible tuple.

Heavily updated tuples won't benefit from the visibility map, because
the bits in the map will be clear all the time due to the updates.

> Beyond that, yeah, I cannot think of other benefits.

Many-to-many relationships is one example:

CREATE TABLE aa (id INTEGER PRIMARY KEY);
CREATE TABLE bb (id INTEGER PRIMARY KEY);
CREATE TABLE aa_bb (aid INTEGER REFERENCES aa (id), bid INTEGER
REFERENCES bb (id));

The relationship table will usually have indexes in both directions:

CREATE INDEX i_aa_bb_1 ON aa_bb (aid, bid);
CREATE INDEX i_aa_bb_2 ON aa_bb (bid, aid);

And of course people will start adding columns to indexes, to make use
of index-only-scans, once we have the capability.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com