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
