Re: How much do the hint bits help?

On 22.12.2010 02:56, Merlin Moncure wrote:
> On Tue, Dec 21, 2010 at 7:45 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Merlin Moncure<mmoncure(at)gmail(dot)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.

Make sure you have a good mix of different xids in the table,
TransactionLogFetch has a one-item cache so repeatedly checking the same
xid is much faster than the general case.

Perhaps run pgbench for a while, and then do "SELECT COUNT(*)" on the
resulting tables.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com