I am currently playing with the nice pgbench tool. I would like to build a benchmark using pgbench with customized scenarios, in order to get something quite representative of a real workload. I have designed a few tables, with a simple script to populate them, and defined 3 scenarios representing typical transactions.
But I have the following issue. Some tables have CHAR (or TEXT or VARCHAR) columns that belong to their primary key and I want to include into pgbench scenarios statements with conditions on these CHAR columns, using some random values generated by pgbench. As pgbench \set, \setrandom or \setshell meta-commands only manage integer variables, I tried to use SQL conditions like: ... where my_column = to_char(:var::integer,'00009FM') ... with var previously defined by: \setrandom var 1 :maxvar
Having previously loaded my_column with digits strings, I get the right result. But ... this condition cannot use any index (tested on 9.2beta2). As a result, I get looooooong index or table scans, which is of course not acceptable in my benchmark as it is not representative of the real data access path :-((
Does someone has a trick to manage random char or text variable in pgbench ?
Thanks by advance for any help. Best regards. Philippe Beaudoin.