Re: pgbench more operators & functions

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: Stephen Frost <sfrost(at)snowman(dot)net>
Cc: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Jeevan Ladhe <jeevan(dot)ladhe(at)enterprisedb(dot)com>, PostgreSQL Developers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pgbench more operators & functions
Date: 2017-02-04 09:51:29
Message-ID: alpine.DEB.2.20.1702040914330.20076@lancre
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


> For my 2c, at least, while I'm definitely interested in this, it's not
> nearly high enough on my plate with everything else going on to get any
> attention in the next few weeks, at least.
> I do think that, perhaps, this patch may deserve a bit of a break, to
> allow people to come back to it with a fresh perspective, so perhaps
> moving it to the next commitfest would be a good idea, in a Needs Review
> state.

So, let's try again for the next CF...

Here is a v9 which includes some more cleanup, hopefully in the expected
direction which is to make pgbench expressions behave as SQL expressions,
and I hope taking into account all other feedback as well.


Pgbench has been given an expression parser (878fdcb8) which allows to use
full expressions instead of doing one-at-a-time operations. This parser
has been extended with functions (7e137f84) & double type (86c43f4e). The
first batch of functions was essentially a poc about how to add new
functions with various requirements. Pgbench default "tpcb-like" test
takes advantage of these additions to reduce the number of lines it needs.


This patch aims at providing actually useful functions for benchmarking.
The functions and operators provided here are usual basic operations. They
are not chosen randomly, but are simply taken from existing benchmarks:

In TPC-B 2.0.0 section 5.3.5 and TPC-C 5.11 section, the selection
of accounts uses a test (if ...), logical conditions (AND, OR) and
comparisons (<, =, >=, >).

In TPC-C 5.11 section 2.1.6, a bitwise or (|) is used to skew a
distribution based on two uniform distributions.

In TPC-C 5.11 section, a log function is used to determine "think
time", which can be truncated (i.e. "least" function, already in pgbench).


The attached patch provides a consistent set of functions and operators
based on the above examples, with operator precedence taken from postgres
SQL parser:

- "boolean" type support is added, because it has been requested that
pgbench should be as close as SQL expressions as possible. This induced
some renaming as some functions & struct fields where named "num" because
they where expecting an int or a double, but a boolean is not really a

- SQL comparisons (= <> < > <= >=) plus pg SQL "!=", which result in a

- SQL logical operators (and or not) on booleans.

- SQL bitwise operators taken from pg: | & # << >> ~.

- mod SQL function as a synonymous for %.

- ln and exp SQL functions.

- SQL CASE/END conditional structure.

The patch also includes documentation and additional tap tests.
A test script is also provided.

This version is strict about typing, mimicking postgres behavior. For
instance, using an int as a boolean results in a error. It is easy to make
it more tolerant to types, which was the previous behavior before it was
suggested to follow SQL behavior.

Together with another submitted patch about retrieving query results, the
added capabilities allow to implement strictly conforming TPC-B


Attachment Content-Type Size
pgbench-more-ops-funcs-9.patch text/x-diff 34.0 KB
functions.sql application/x-sql 1.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Ashutosh Sharma 2017-02-04 10:38:24 Re: pageinspect: Hash index support
Previous Message Boris Muratshin 2017-02-04 08:41:29 3D Z-curve spatial index