I'm currently doing some benchmarking on a Nehalem
box(http://www.kaltenbrunner.cc/blog/index.php?/archives/26-Benchmarking-8.4-Chapter-1Read-Only-workloads.html)
with 8.4 and while investigating what looks like issues in pgbench I
also noticed that using char() has more than a negligable overhead on
some (very special) readonly(!) workloads.
for example running sysbench in read-only mode against 8.4 results in a
profile(for the full run) that looks similiar to:
samples % symbol name
981690 11.0656 bcTruelen
359183 4.0487 index_getnext
311128 3.5070 AllocSetAlloc
272330 3.0697 hash_search_with_hash_value
258157 2.9099 LWLockAcquire
195673 2.2056 _bt_compare
190303 2.1451 slot_deform_tuple
168101 1.8948 PostgresMain
164191 1.8508 _bt_checkkeys
126110 1.4215 FunctionCall2
123965 1.3973 SearchCatCache
120629 1.3597 LWLockRelease
the default sysbench mode actually uses a number of different queries
and the ones dealing with char() are actually only a small part of the
full set of queries sent.
The specific query is causing bcTruelen to show up in the profile is:
"SELECT c from sbtest where id between $1 and $2 order by c" where the
parameters are for example
$1 = '5009559', $2 = '5009658' - ie ranges of 100.
benchmarking only that query results in:
samples % symbol name
2148182 23.5861 bcTruelen
369463 4.0565 index_getnext
362784 3.9832 AllocSetAlloc
284198 3.1204 slot_deform_tuple
185279 2.0343 _bt_checkkeys
180119 1.9776 LWLockAcquire
172733 1.8965 appendBinaryStringInfo
144158 1.5828 internal_putbytes
141040 1.5486 AllocSetFree
138093 1.5162 printtup
124255 1.3643 hash_search_with_hash_value
117054 1.2852 heap_form_minimal_tuple
at around 46000 queries/s
changing the fault sysbench schema from:
Table "public.sbtest"
Column | Type | Modifiers
--------+----------------+-----------------------------------------------------
id | integer | not null default
nextval('sbtest_id_seq'::regclass)
k | integer | not null default 0
c | character(120) | not null default ''::bpchar
pad | character(60) | not null default ''::bpchar
Indexes:
"sbtest_pkey" PRIMARY KEY, btree (id)
"k" btree (k)
to
Table "public.sbtest"
Column | Type | Modifiers
--------+-------------------+-----------------------------------------------------
id | integer | not null default
nextval('sbtest_id_seq'::regclass)
k | integer | not null default 0
c | character varying | not null default ''::character varying
pad | character(60) | not null default ''::bpchar
Indexes:
"sbtest_pkey" PRIMARY KEY, btree (id)
"k" btree (k)
results in a near 50%(!) speedup in terms of tps to around 67000
queries/s. This is however an extreme case because the c column actually
contains no data at all (except for an empty string).
the profile for the changed testcase looks like:
430797 5.2222 index_getnext
396750 4.8095 AllocSetAlloc
345508 4.1883 slot_deform_tuple
228222 2.7666 appendBinaryStringInfo
227766 2.7610 _bt_checkkeys
193818 2.3495 LWLockAcquire
179925 2.1811 internal_putbytes
168871 2.0471 printtup
152026 1.8429 AllocSetFree
146333 1.7739 heap_form_minimal_tuple
144305 1.7493 FunctionCall2
128320 1.5555 hash_search_with_hash_value
at the very least we should reconsider this part of our docs:
" There is no performance difference between these three types, apart
from increased storage space when using the blank-padded type, and a few
extra CPU cycles to check the length when storing into a
length-constrained column."
from http://www.postgresql.org/docs/8.4/static/datatype-character.html
regards
Stefan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
