PostgreSQL int array slice benchmark (with postgresql arrays and bytea fields)

bench.py

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))

result.txt

(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