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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
