Re: How much do the hint bits help?

On Tue, Dec 21, 2010 at 7:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> Attached is an incomplete patch disabling hint bits based on compile
>> switch. ...
>> So far, at least doing pgbench runs and another test designed to
>> exercise clog lookups, the performance loss of always doing full
>> lookup hasn't materialized.
>
> The standard pgbench test would be just about 100% useless for stressing
> this, because its net database activity is only about one row
> touched/updated per query.  You need a test case that hits lots of rows
> per query, else you're just measuring parse+plan+network overhead.

right -- see the attached clog_stress.sql above.  It creates a script
that inserts records in blocks of 10000, deletes half of them, and
vacuums.  Neither the execution of the script nor a seq scan following
its execution showed an interesting performance difference (which I am
arbitrarily calling 5% in either direction).  Like I said though, I
don't trust the patch or the results yet.

@Mark: apparently the cvs server is behind git and there are some
recent changes to heapam.c that need more attention.  I need to get
git going on my box, but try changing this:
if ((tuple->t_infomask & HEAP_XMIN_COMMITTED) ||    (!(tuple->t_infomask & HEAP_XMIN_COMMITTED) &&
!(tuple->t_infomask& HEAP_XMIN_INVALID) &&     TransactionIdDidCommit(xmin))) 

to this:
if (TransactionIdDidCommit(xmin))

also, isn't the extra check vs HEAP_XMIN_COMMITTED redundant, and if
you do have to look up clog, why not set the hint bit?

merlin