example.out

10 total cols: 1 selected, 9 NULL, 10000000 rows 0.6916604042053223 seconds
10 total cols: 1 selected, 8 NULL, 10000000 rows 0.6812009811401367 seconds
10 total cols: 1 selected, 7 NULL, 10000000 rows 0.6044158935546875 seconds
10 total cols: 1 selected, 6 NULL, 10000000 rows 0.8335869312286377 seconds
10 total cols: 1 selected, 5 NULL, 10000000 rows 0.6090021133422852 seconds
10 total cols: 1 selected, 4 NULL, 10000000 rows 0.572737455368042 seconds
10 total cols: 1 selected, 3 NULL, 10000000 rows 0.584613561630249 seconds
10 total cols: 1 selected, 2 NULL, 10000000 rows 0.7285683155059814 seconds
10 total cols: 1 selected, 1 NULL, 10000000 rows 0.6625514030456543 seconds
10 total cols: 2 selected, 8 NULL, 10000000 rows 0.6221935749053955 seconds
10 total cols: 2 selected, 7 NULL, 10000000 rows 0.8217811584472656 seconds
10 total cols: 2 selected, 6 NULL, 10000000 rows 0.900759220123291 seconds
10 total cols: 2 selected, 5 NULL, 10000000 rows 0.6378457546234131 seconds
10 total cols: 2 selected, 4 NULL, 10000000 rows 0.7322068214416504 seconds
10 total cols: 2 selected, 3 NULL, 10000000 rows 0.5630929470062256 seconds
10 total cols: 2 selected, 2 NULL, 10000000 rows 0.5395193099975586 seconds
10 total cols: 2 selected, 1 NULL, 10000000 rows 0.6461427211761475 seconds
10 total cols: 3 selected, 7 NULL, 10000000 rows 0.6598279476165771 seconds
10 total cols: 3 selected, 6 NULL, 10000000 rows 0.6342368125915527 seconds
10 total cols: 3 selected, 5 NULL, 10000000 rows 0.6533758640289307 seconds
10 total cols: 3 selected, 4 NULL, 10000000 rows 0.5480964183807373 seconds
10 total cols: 3 selected, 3 NULL, 10000000 rows 0.6522617340087891 seconds
10 total cols: 3 selected, 2 NULL, 10000000 rows 0.6625094413757324 seconds
10 total cols: 3 selected, 1 NULL, 10000000 rows 0.5635101795196533 seconds
10 total cols: 4 selected, 6 NULL, 10000000 rows 0.5155024528503418 seconds
10 total cols: 4 selected, 5 NULL, 10000000 rows 0.5159933567047119 seconds
10 total cols: 4 selected, 4 NULL, 10000000 rows 0.5202929973602295 seconds
10 total cols: 4 selected, 3 NULL, 10000000 rows 0.5519185066223145 seconds
10 total cols: 4 selected, 2 NULL, 10000000 rows 0.6474406719207764 seconds
10 total cols: 4 selected, 1 NULL, 10000000 rows 0.6520495414733887 seconds
10 total cols: 5 selected, 5 NULL, 10000000 rows 0.5719130039215088 seconds
10 total cols: 5 selected, 4 NULL, 10000000 rows 0.5545668601989746 seconds
10 total cols: 5 selected, 3 NULL, 10000000 rows 0.639406681060791 seconds
10 total cols: 5 selected, 2 NULL, 10000000 rows 0.6129019260406494 seconds
10 total cols: 5 selected, 1 NULL, 10000000 rows 0.5966455936431885 seconds
10 total cols: 6 selected, 4 NULL, 10000000 rows 0.5749166011810303 seconds
10 total cols: 6 selected, 3 NULL, 10000000 rows 0.6580791473388672 seconds
10 total cols: 6 selected, 2 NULL, 10000000 rows 0.6976237297058105 seconds
10 total cols: 6 selected, 1 NULL, 10000000 rows 0.5874862670898438 seconds
10 total cols: 7 selected, 3 NULL, 10000000 rows 0.6260466575622559 seconds
10 total cols: 7 selected, 2 NULL, 10000000 rows 0.6250591278076172 seconds
10 total cols: 7 selected, 1 NULL, 10000000 rows 0.587761640548706 seconds
10 total cols: 8 selected, 2 NULL, 10000000 rows 0.9103586673736572 seconds
10 total cols: 8 selected, 1 NULL, 10000000 rows 0.8547675609588623 seconds
10 total cols: 9 selected, 1 NULL, 10000000 rows 0.6762118339538574 seconds
10 total cols: 1 selected, 9 NULL, 100 rows 0.015694141387939453 seconds
10 total cols: 1 selected, 8 NULL, 100 rows 0.0169675350189209 seconds
10 total cols: 1 selected, 7 NULL, 100 rows 0.014212608337402344 seconds
10 total cols: 1 selected, 6 NULL, 100 rows 0.014072418212890625 seconds
10 total cols: 1 selected, 5 NULL, 100 rows 0.015524625778198242 seconds
10 total cols: 1 selected, 4 NULL, 100 rows 0.013295412063598633 seconds
10 total cols: 1 selected, 3 NULL, 100 rows 0.013313770294189453 seconds
10 total cols: 1 selected, 2 NULL, 100 rows 0.013698577880859375 seconds
10 total cols: 1 selected, 1 NULL, 100 rows 0.011465072631835938 seconds
10 total cols: 2 selected, 8 NULL, 100 rows 0.011356592178344727 seconds
10 total cols: 2 selected, 7 NULL, 100 rows 0.009987831115722656 seconds
10 total cols: 2 selected, 6 NULL, 100 rows 0.010006904602050781 seconds
10 total cols: 2 selected, 5 NULL, 100 rows 0.01069188117980957 seconds
10 total cols: 2 selected, 4 NULL, 100 rows 0.010583877563476562 seconds
10 total cols: 2 selected, 3 NULL, 100 rows 0.01146388053894043 seconds
10 total cols: 2 selected, 2 NULL, 100 rows 0.009010791778564453 seconds
10 total cols: 2 selected, 1 NULL, 100 rows 0.00992584228515625 seconds
10 total cols: 3 selected, 7 NULL, 100 rows 0.009830474853515625 seconds
10 total cols: 3 selected, 6 NULL, 100 rows 0.008640050888061523 seconds
10 total cols: 3 selected, 5 NULL, 100 rows 0.00934290885925293 seconds
10 total cols: 3 selected, 4 NULL, 100 rows 0.00909113883972168 seconds
10 total cols: 3 selected, 3 NULL, 100 rows 0.010122060775756836 seconds
10 total cols: 3 selected, 2 NULL, 100 rows 0.01024007797241211 seconds
10 total cols: 3 selected, 1 NULL, 100 rows 0.010496377944946289 seconds
10 total cols: 4 selected, 6 NULL, 100 rows 0.010824441909790039 seconds
10 total cols: 4 selected, 5 NULL, 100 rows 0.012118101119995117 seconds
10 total cols: 4 selected, 4 NULL, 100 rows 0.010517358779907227 seconds
10 total cols: 4 selected, 3 NULL, 100 rows 0.011622905731201172 seconds
10 total cols: 4 selected, 2 NULL, 100 rows 0.010773181915283203 seconds
10 total cols: 4 selected, 1 NULL, 100 rows 0.010844945907592773 seconds
10 total cols: 5 selected, 5 NULL, 100 rows 0.009765863418579102 seconds
10 total cols: 5 selected, 4 NULL, 100 rows 0.009224891662597656 seconds
10 total cols: 5 selected, 3 NULL, 100 rows 0.011211156845092773 seconds
10 total cols: 5 selected, 2 NULL, 100 rows 0.011994123458862305 seconds
10 total cols: 5 selected, 1 NULL, 100 rows 0.010563135147094727 seconds
10 total cols: 6 selected, 4 NULL, 100 rows 0.011039018630981445 seconds
10 total cols: 6 selected, 3 NULL, 100 rows 0.01609659194946289 seconds
10 total cols: 6 selected, 2 NULL, 100 rows 0.010680198669433594 seconds
10 total cols: 6 selected, 1 NULL, 100 rows 0.009272336959838867 seconds
10 total cols: 7 selected, 3 NULL, 100 rows 0.0101470947265625 seconds
10 total cols: 7 selected, 2 NULL, 100 rows 0.011170387268066406 seconds
10 total cols: 7 selected, 1 NULL, 100 rows 0.017583131790161133 seconds
10 total cols: 8 selected, 2 NULL, 100 rows 0.012769937515258789 seconds
10 total cols: 8 selected, 1 NULL, 100 rows 0.011213064193725586 seconds
10 total cols: 9 selected, 1 NULL, 100 rows 0.013700723648071289 seconds
5 total cols: 1 selected, 4 NULL, 1000000 rows 0.06916499137878418 seconds
5 total cols: 1 selected, 3 NULL, 1000000 rows 0.06649374961853027 seconds
5 total cols: 1 selected, 2 NULL, 1000000 rows 0.06927776336669922 seconds
5 total cols: 1 selected, 1 NULL, 1000000 rows 0.0681772232055664 seconds
5 total cols: 2 selected, 3 NULL, 1000000 rows 0.08529543876647949 seconds
5 total cols: 2 selected, 2 NULL, 1000000 rows 0.09929919242858887 seconds
5 total cols: 2 selected, 1 NULL, 1000000 rows 0.10389423370361328 seconds
5 total cols: 3 selected, 2 NULL, 1000000 rows 0.10135531425476074 seconds
5 total cols: 3 selected, 1 NULL, 1000000 rows 0.08748745918273926 seconds
5 total cols: 4 selected, 1 NULL, 1000000 rows 0.06591272354125977 seconds

local_table_benchmark.py

import subprocess
import time 

TABLE_NAME = "LOCAL0"
PORT = 9700

run_times = {}

class Config:
  def __init__(self, numCols, numRow):
    self.numCols = numCols
    self.numRow = numRow

def generate_table_command(number_of_columns):
    tableCreateCommand = f'CREATE TABLE {TABLE_NAME}(a0 int'
    for i in range(1, number_of_columns):
        tableCreateCommand += f', a{i} int'
    tableCreateCommand += ");"
    return tableCreateCommand

def generate_insert_command(number_of_rows, number_of_columns):
    insertCommand = f'INSERT INTO {TABLE_NAME} SELECT *'
    for i in range(1, number_of_columns):
        insertCommand += f', *'
    insertCommand += f' FROM generate_series(1, {number_of_rows});'
    return insertCommand

def generate_warm_command():
    return f'SELECT COUNT(*) FROM (SELECT * FROM {TABLE_NAME});'

def generate_select_command(number_of_selected_columns, null_columns):
    selectCommand = f'SELECT COUNT(*) FROM (SELECT a0 '
    for i in range(1, number_of_selected_columns):
        selectCommand += f',a{i}'
    for i in range(1, null_columns):
        selectCommand += ', NULL'    

    selectCommand += f' FROM {TABLE_NAME}) foo;'
    return selectCommand

def generate_drop_table():
    return f'DROP TABLE {TABLE_NAME}'

def run_psql(command):
    subprocess.check_call(['psql', '-p',  str(PORT), '-c', command])

def print_results():
    for config, time in run_times.items():
        print(config, f'{time} seconds')

def test_config(config):
    numberOfCols = config.numCols
    numberOfRows = config.numRow
    run_psql(generate_table_command(numberOfCols))
    run_psql(generate_insert_command(numberOfRows, numberOfCols))
    run_psql(generate_warm_command())
    for i in range (1,numberOfCols):
        for k in range(i, numberOfCols):
            selectedCols = i
            nullCols = numberOfCols - k
            start_time = time.time()
            run_psql(generate_select_command(selectedCols, nullCols))
            runtime = time.time() - start_time
            run_times[f'{numberOfCols} total cols: {selectedCols} selected, {nullCols} NULL, {numberOfRows} rows'] = runtime
            print("--- %s seconds ---" % runtime)
    run_psql(generate_drop_table())

# CHANGE THE CONFIGS HERE TO TEST (NUMBER_OF_COLS, NUMBER_OF_ROWS)
configValues = [(10, 10000000), (10, 100), (5, 1000000)]
for configValue in configValues:
    numCols, numRows = configValue
    config = Config(numCols, numRows)
    test_config(config)   

print_results()