# Measure the performance of inserts and compare between JSON and JSONB from pg import DB import random import time db = DB(dbname='postgres', host='10.40.22.181', port=5432, user='wb', passwd='knsbb') # 1. Insert 1 row to JSON def runTest(): print "1. Inserting 1 row to JSON table, result:" query_str = "EXPLAIN ANALYZE INSERT INTO \"postgres\".\"sandbox\".\"table_json10\" VALUES ('{\"x1\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x2\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x3\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x4\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x5\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x6\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x7\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x8\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x9\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x10\":" + str(random.uniform(-1000000, 1000000)) + \ "}')" result = db.query(query_str) print result print "\n" #2. Insert 1 row to JSONB print "2. Inserting 1 row to JSONB table, result:" query_str = "EXPLAIN ANALYZE INSERT INTO \"postgres\".\"sandbox\".\"table_json10b\" VALUES ('{\"x1\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x2\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x3\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x4\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x5\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x6\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x7\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x8\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x9\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x10\":" + str(random.uniform(-1000000, 1000000)) + \ "}')" result = db.query(query_str) print result print "\n" # 3. Multi-insert (10 rows) to JSON print "3. Multi insert (10) to JSON table, result:" query_str = "EXPLAIN ANALYZE INSERT INTO \"postgres\".\"sandbox\".\"table_json10\" (data) VALUES " for i in xrange(0, 9): query_str += "('{\"x1\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x2\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x3\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x4\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x5\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x6\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x7\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x8\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x9\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x10\":" + str(random.uniform(-1000000, 1000000)) + \ "}')," query_str += "('{\"x1\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x2\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x3\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x4\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x5\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x6\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x7\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x8\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x9\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x10\":" + str(random.uniform(-1000000, 1000000)) + \ "}');" result = db.query(query_str) print result print "\n" # 4. Multi-insert (10 rows) to JSONB print "4. Multi insert (10) to JSONB table, result:" query_str = "EXPLAIN ANALYZE INSERT INTO \"postgres\".\"sandbox\".\"table_json10b\" (data) VALUES " for i in xrange(0, 9): query_str += "('{\"x1\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x2\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x3\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x4\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x5\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x6\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x7\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x8\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x9\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x10\":" + str(random.uniform(-1000000, 1000000)) + \ "}')," query_str += "('{\"x1\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x2\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x3\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x4\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x5\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x6\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x7\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x8\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x9\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x10\":" + str(random.uniform(-1000000, 1000000)) + \ "}');" result = db.query(query_str) print result print "\n" # 5. Insert 10,000 rows to JSON table print "4. Insert 10,000 rows to JSON, execution time (sec):" sum_of_inserts_secs = 0 for i in xrange(0, 10000): query_str = "INSERT INTO \"postgres\".\"sandbox\".\"table_json10\" VALUES ('{\"x1\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x2\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x3\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x4\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x5\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x6\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x7\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x8\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x9\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x10\":" + str(random.uniform(-1000000, 1000000)) + \ "}')" start = time.time() db.query(query_str) end = time.time() sum_of_inserts_secs += (end - start) print sum_of_inserts_secs print "\n" # 5. Insert 10,000 rows to JSONB table print "5. Insert 10,000 rows to JSONB, execution time (sec):" sum_of_inserts_secs = 0 for i in xrange(0, 10000): query_str = "INSERT INTO \"postgres\".\"sandbox\".\"table_json10b\" VALUES ('{\"x1\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x2\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x3\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x4\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x5\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x6\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x7\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x8\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x9\":" + str(random.uniform(-1000000, 1000000)) + \ ", \"x10\":" + str(random.uniform(-1000000, 1000000)) + \ "}')" start = time.time() db.query(query_str) end = time.time() sum_of_inserts_secs += (end - start) print sum_of_inserts_secs print "\n" if __name__ == "__main__": runTest()