On Mon, Jan 10, 2005 at 11:51:51AM -0500, Alex Turner wrote: > I'm no database writing guru, but wouldn't it just be a matter of > adding a transaction number to an index entry so as to determine it's > newness and only retrieve entries with an older transaction number?
No, it's more complex than that. Index entries would have to be labeled with both a creation transaction Id and a destruction transaction Id (xmin and xmax. Probably it'd also need Cmin and Cmax to be completely consistent.) Keeping them in sync would be prone to deadlock because it'd have to simultaneously update the table proper and the possibly multiple indexes. Plus, having all those identifiers in the index file would imply more I/O costs. > I'm guessing that the theory is that most insert transactions will be > committed, or only contain a small number of rows relative to the > overall size of the table, and therefore the extra overhead of > checking newer tuples won't impact the overall performance that much? The choice is yours (or whoever's): if you absolutely need exact numbers, you pay the cost of having a trigger. OTOH if you can do with estimates, you can use the reltuples column from pg_class. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "There is evil in the world. There are dark, awful things. Occasionally, we get a glimpse of them. But there are dark corners; horrors almost impossible to imagine... even in our worst nightmares." (Van Helsing, Dracula A.D. 1972) ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match