Greg Smith wrote: > On Wed, 14 May 2008, Kevin Grittner wrote: > >> If this is the first time that the rows are being read since they were >> inserted (or since the database was loaded, including from backup), it >> may be rewriting the rows to set hint bits, which can make subsequent >> access faster. > > This is the second time this has come up recently, and I know it used to > puzzle me too. This is a particularly relevant area to document better > for people doing benchmarking. As close I've found to a useful > commentary on this subject is the thread at > http://archives.postgresql.org/pgsql-patches/2005-07/msg00390.php > > I still don't completely understand this myself though, if I did I'd add > a FAQ on it. Anyone want to lecture for a minute on the birth and care > of hint bits? I'll make sure any comments here get onto the wiki. Hint bits are used to mark tuples as created and/or deleted by transactions that are know committed or aborted. To determine the visibility of a tuple without such bits set, you need to consult pg_clog and possibly pg_subtrans, so it is an expensive check. On the other hand, if the tuple has the bits set, then it's state is known (or, at worst, it can be calculated easily from your current snapshot, without looking at pg_clog.) There are four hint bits: XMIN_COMMITTED -- creating transaction is known committed XMIN_ABORTED -- creating transaction is known aborted XMAX_COMMITTED -- same, for the deleting transaction XMAX_ABORTED -- ditto If neither of the bits is set, then the transaction is either in progress (which you can check by examining the list of running transactions in shared memory) or your process is the first one to check (in which case, you need to consult pg_clog to know the status, and you can update the hint bits if you find out a permanent state). Regarding FAQs, I'm having trouble imagining putting this in the user FAQ; I think it belongs into the developer's FAQ. However, a benchmarker is not going to look there. Maybe we should start "a benchmarker's FAQ"? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support