Skip to content

Instantly share code, notes, and snippets.

@niwinz
Created October 2, 2012 21:17
Show Gist options
  • Save niwinz/3823366 to your computer and use it in GitHub Desktop.
Save niwinz/3823366 to your computer and use it in GitHub Desktop.
PostgreSQL int array slice benchmark (with postgresql arrays and bytea fields)
from __future__ import print_function
import timeit
import array
import struct
import psycopg2 as pg
connection = pg.connect(host="localhost", dbname="test")
def setup1():
cursor = connection.cursor()
cursor.execute("BEGIN;")
cursor.execute("DROP TABLE IF EXISTS foo1;")
cursor.execute("CREATE TABLE foo1 (id int, data int[]);")
cursor.execute("INSERT INTO foo1 (id, data) VALUES (%s, %s)", [1, list(range(0,200000))])
cursor.execute("COMMIT;")
def bench1():
cursor = connection.cursor()
cursor.execute("SELECT data[10:100] FROM foo1 WHERE id = %s;", [1])
x = cursor.fetchone()
cursor.close()
def setup2():
cursor = connection.cursor()
cursor.execute("BEGIN;")
cursor.execute("DROP TABLE IF EXISTS foo2;")
cursor.execute("CREATE TABLE foo2 (id int, data text);")
cursor.execute("INSERT INTO foo2 (id, data) VALUES (%s, %s)", [1, ",".join([str(x) for x in range(0,200000)])])
cursor.execute("COMMIT;")
def bench2():
cursor = connection.cursor()
cursor.execute("SELECT data FROM foo2 WHERE id = %s", [1])
result = cursor.fetchone()[0]
result = map(int, result.split(",")[10:100])
cursor.close()
def setup3():
cursor = connection.cursor()
cursor.execute("BEGIN;")
cursor.execute("DROP TABLE IF EXISTS foo3;")
cursor.execute("CREATE TABLE foo3 (id int, data bytea);")
data = array.array("i", [x for x in range(0,200000)])
cursor.execute("INSERT INTO foo3 (id, data) VALUES (%s, %s)", [1, pg.Binary(data.tostring())])
cursor.execute("COMMIT;")
def bench3():
cursor = connection.cursor()
cursor.execute("SELECT data FROM foo3 WHERE id = %s", [1])
data = array.array("i")
data.fromstring(cursor.fetchone()[0])
result = data.tolist()[10:100]
cursor.close()
def setup4():
cursor = connection.cursor()
cursor.execute("BEGIN;")
cursor.execute("DROP TABLE IF EXISTS foo4;")
cursor.execute("CREATE TABLE foo4 (id int, data bytea);")
data = struct.pack("!200000i", *[x for x in range(0,200000)])
cursor.execute("INSERT INTO foo4 (id, data) VALUES (%s, %s)", [1, pg.Binary(data)])
cursor.execute("COMMIT;")
def bench4():
cursor = connection.cursor()
cursor.execute("SELECT substring(data from %s for %s) FROM foo4 WHERE id = %s", [10*4, 90*4, 1])
data = bytes(cursor.fetchone()[0])
data = struct.unpack("90i", data)
cursor.close()
if __name__ == "__main__":
t1 = timeit.Timer(stmt=bench1, setup=setup1)
print("Postgresql array with slice: ", t1.timeit(150))
t2 = timeit.Timer(stmt=bench2, setup=setup2)
print("Postgresql text with python slice: ", t2.timeit(150))
t3 = timeit.Timer(stmt=bench3, setup=setup3)
print("Postgresql bytea with array slice: ", t3.timeit(150))
t4 = timeit.Timer(stmt=bench4, setup=setup4)
print("Postgresql bytea substring with struct: ", t4.timeit(150))
(test2)[3/5.0.0]niwi@vaio:~/niwi-benchmarks/postgresql-array> python bench.py
Postgresql array with slice: 0.18566584587097168
Postgresql text with python slice: 6.571290969848633
Postgresql bytea with array slice: 3.2369840145111084
Postgresql bytea substring with struct: 0.05106019973754883
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment