I've been learning a lot about PostgreSQL recently. A trigger for this was a Stack overflow question I came about some time ago, featuring a fairly complex equation for calculating color similarity. I've been obsessing over it ever since and I've used it as a test case for a number of things. Hopefully, this will be one of several articles about how I used it and what I learned along.

This article is a benchmark of different implementations of same function in PostgreSQL. The function in question is difference between colors, and the implementations are in SQL, C, and several variants of Python: pure, numpy, numba and Cython.

Delta E color difference is a measure of color difference. The L*a*b color space was designed with intention to represent colors in a perceptually uniform way, meaning some change in value should result in equal or very similar change in perception. Since the color space was designed with this intention, the original CIE 1976 Delta E was just cartesian distance between two point in color space. Along with more research it became clear the CIE 1976 had some deficiencies and was followed by improved version CIE 1994 Delta E. The latest revision is CIE 2000 Delta E and is far from being a simple equation. For the reader to understand the rest of the post, it is enough to know this equations is math intensive, accepts two 3-dimensional vectors, and returns a single scalar value.

So, with the CIE 2000 Delta E standard no longer being a simple calculation, implementing it in SQL became cumbersome. Jaza, author of the SO question, managed to implement it in SQL (kudos!), but even with CTEs the code is still incomprehensible. And the code is slow, too. SQL just wasn't made for this.

In an effort to speed this up, I tested several approaches. I tested Jaza's SQL implementation, plpgsql implementation and my custom PostgreSQL C extension (more about this in another post). PostgreSQL officially supports Python as a server side procedural language, so this is also something to evaluate. Python is diverse and offers several options for implementation, depending on your needs for performance: pure Python, numpy, numba, cython.

For testing I used several configurations: pure SQL function (as written by Jaza), plpgsql function, my PostgreSQL C extension, pure Python function, Python function using NumPy, then I used Python module as a container for functions, and additionally tried to see if I can make numba JIT work on the server side.

The results are interesting, but first the setup.

dropdb colors
createdb colors
psql colors -c 'create table color (rgb_r double precision, rgb_g double precision, rgb_b double precision, lab_l double precision, lab_a double precision, lab_b double precision);'

Benchmark

For benchmark I created a table with 20.000 random RGB colors, converted them to Lab color space and stored in database. Test case consists of running the distance function on whole table, comparing with a fixed value, in my case it was L=42.041, a=-9.105, b=-13.873. Each implementation was called 10 times on an idle laptop.

Reusing connections

First run was with connection reuse:

Implementation	Minimum	Average	Maximum	Speedup
SQL	11.3849	11.8725	12.5735	1.0
plpgsql	0.8482	0.8786	0.9386	13.5
C extension	0.0550	0.0593	0.0715	200.3
Py inline pure	0.5735	0.6166	0.6651	19.3
Py inline pure JIT	0.8567	1.2102	3.8561	9.8
Py inline pure JIT cached	0.8730	0.9800	1.2758	12.1
Py inline numpy	6.2782	6.5544	6.8891	1.8
Py module pure	0.6055	0.6445	0.6936	18.4
Py module pure JIT	0.2837	0.4468	1.7373	26.6
Py module pure JIT cached	0.2878	0.4241	1.4920	28.0
Py module pure cython	0.4944	0.5123	0.5600	23.2
Py module numpy	6.0890	6.4830	7.7312	1.8
Py module numpy JIT	6.0668	6.5032	7.5097	1.8
Py module numpy JIT cached	6.0912	6.5865	7.3711	1.8

SQL is slowest with 11 seconds for 20k rows. C extension is fastest, with 55 to 72 miliseconds and offers a speedup of 200x.

In between there's Python. All numpy implementations are a bit less than 2x faster than SQL. Using numpy in plpython is slow, since the data needs to be copied to numpy array before it is processed. This is a costly operation and it shows. Inline numpy with numba JIT took far longer that SQL against which I am comparing so I didn't test it at all.

Pure Python implementations do better and are about 19x faster than SQL. By defining function inline instead of in module it was 4% faster, but that can make logic harder to maintain. However, when using numba JIT, defining function inline slows down execution to 12x faster, and defining function in module speeds it up to 28x faster. Apparently defining function inline makes JIT less effective. All numba decorated functions also have an initialization penalty, where the first run is significantly slower, most obvious with pure jit module.

Caching with numba does not seem to help. It does have one extra requirement, since the server process needs write permissions on directory next to python module in order to write cached data to disk.

I also tested a Cython implementation, at 23x it was faster than pure Python, but slower than numba.

New connections

Second run was with new connection for every execution:

Implementation	Minimum	Average	Maximum	Speedup
SQL	11.5494	12.0963	12.7010	1.0
plpgsql	0.9093	1.0696	1.2528	11.3
C extension	0.0977	0.1129	0.1325	107.2
Py inline pure	0.6567	0.7199	0.8560	16.8
Py inline pure JIT	1.2135	1.3678	1.6060	8.8
Py inline pure JIT cached	1.1845	1.2449	1.3095	9.7
Py inline numpy	6.0845	6.4325	6.7866	1.9
Py module pure	0.6414	0.6754	0.7062	17.9
Py module pure JIT	1.4029	1.4491	1.5507	8.3
Py module pure JIT cached	1.4275	1.5280	1.6158	7.9
Py module pure cython	0.5374	0.5874	0.6513	20.6
Py module numpy	7.1427	7.3657	7.5970	1.6
Py module numpy JIT	7.2352	7.4535	7.7606	1.6
Py module numpy JIT cached	7.3986	7.5940	7.9226	1.6

When including connection overhead there are significant reductions in performance. It is important to understand how PostgreSQL implements plpython and server processes in general. After connecting, PostgreSQL forks a process for client. If you run any plpython functions, this process then loads Python interpreter, also adding some overhead.

SQL implementation stays almost the same, numpy is slightly slower with 1.6-1.9x speedup.

All implementations with numba are significantly slower. Where numba was previously 28x faster, it is now always hitting initialization on first request, and the speedup drops to 7.9x. Best numba enabled implementation is 9.7x faster, down from 12.1x. Both are slower than pure Python.

A couple of implementations stand out here: pure Python, both inline and as module, and also Cython, have approximately the same speedup as before, meaning they are a stable implementation and do not incur significant initialization overhead.