Is this slow for anyone else?

[leematos]

I'm playing around with a django/pypy/postgres stack and using this for postgres support. I'm not sure if my db is misconfigured or if this is just not "speedy" yet. Can anyone chime in and provide some feedback?

Regards, Lee

[mvantellingen]

Hi,

Do you have any more information? What is exactly slow? Which pypy version, did you test it with cPython + psycopg2?

Note that I haven't done any benchmarks recently, and I am pretty sure it is slower then cPython + psycopg2 before the PyPy JIT kicks in.

[leematos]

Hi, I don't have much more information beyond it looks like general queries are slow. It's the latest pypy 1.7 and I didn't use pyscopg2 before. I'm moving from a sqlite in local testing to building an experimental stack and wanted to see how this performed. If you are around in an IRC channel somewhere we can discuss this more if you'd like. If there is anything you'd like to see me run I'd gladly do it. It's on the web at www.gencyclothing.com. No CSS is rendered as I'm still working on it :)

[gtaylor]

I tested this with PyPy 1.8, and noticed that it was indeed a bit slower than psycopg2 + CPython, even after warming the JIT up. I had a handful of smaller queries that typically take <1ms a piece on CPython+pyscopg2 take 10ms each, consistently. Testing a heavier page load on a Django app saw a 2x increase (400ms instead of psycopg2's 200ms).

I'd love to help pin this down some, but am not sure how to with PyPy. Any tips on profiling?

[mvantellingen]

I think we first need a good set of benchmarks. The pypq repository contains a few, see https://bitbucket.org/descent/pypq/src/9a520e535758/pypq/tests

The one place where I know we can optimize is Cursor._combine_cmd_params, see https://github.com/mvantellingen/psycopg2-ctypes/blob/master/psycopg2ct/_impl/cursor.py#L850

We could also look in using pqExecParams() which might be faster then manual escaping and calling pqExec()

[dvarrazzo]

@mvantellingen Using pqExecParams is a long standiing issue in psycopg: in short, it can't be done unless breaking compatibility and cutting away features. I'm reserving some time in future to provide an alternative connection class using it, but it's never going to be a replacement to the connection as it is currently known.

The improvement we can do in _combine_cmd_params() is to use the string % operator, which is going to be messy in python3 - that's why psycopg2 has its own Bytes_Format implementation - but always faster than operating on the string byte by byte as we do now.