test_postgre_for_orderby.py

import psycopg2
import random
num_iterations = 1000
db = 'domains'
host = '127.0.0.1'
pwd = 'topsecretmegapassword'
conn = psycopg2.connect(database=db,
                        user=db,
                        password=pwd)

cur = conn.cursor()
max_sw_id = 2284616
max_w_id = 97912

execution_time_with_table_name = \
    execution_time_without_table_name = \
    plaining_time_with_table_name = \
    plaining_time_without_table_name = float(0)

for i in range(num_iterations):
    id_w = random.randint(105146, 105146 + max_w_id)
    cur.execute(f'SELECT name FROM linkmanager_word WHERE id = {id_w}')
    word = cur.fetchone()[0]
    sql_with_table_name = f'EXPLAIN ANALYZE ' \
                          f'SELECT linkmanager_word.id, linkmanager_word.name, linkmanager_subword.sw_name ' \
                          f'FROM linkmanager_word \
    JOIN linkmanager_subword ON  linkmanager_word.id = linkmanager_subword.baseword_id ' \
                          f'WHERE linkmanager_word.name = \'{word}\'\
    ORDER BY      linkmanager_word.name \
    LIMIT 10;'
    
    sql_without_table_name = f'EXPLAIN ANALYZE ' \
                             f'SELECT linkmanager_word.id, linkmanager_word.name, linkmanager_subword.sw_name ' \
                             f'FROM linkmanager_word \
    JOIN linkmanager_subword ON  linkmanager_word.id = linkmanager_subword.baseword_id ' \
                             f'WHERE linkmanager_word.name = \'{word}\'\
    ORDER BY  name \
    LIMIT 10;'
    print(f'{sql_with_table_name}\n'
          f'{sql_without_table_name}')

    cur.execute(sql_without_table_name)
    res_2 = cur.fetchall()
    cur.execute(sql_with_table_name)
    res_1 = cur.fetchall()

    Plaining_time1 = res_1[6][0].replace('Planning time: ', '').replace(' ms', '')
    Plaining_time2 = res_2[6][0].replace('Planning time: ', '').replace(' ms', '')
    Execution_time1 = res_1[7][0].replace('Execution time: ', '').replace(' ms', '')
    Execution_time2 = res_2[7][0].replace('Execution time: ', '').replace(' ms', '')
    plaining_time_with_table_name += float(Plaining_time1)
    plaining_time_without_table_name += float(Plaining_time2)
    execution_time_with_table_name += float(Execution_time1)
    execution_time_without_table_name += float(Execution_time2)

av_PT1 = plaining_time_with_table_name / num_iterations
av_PT2 = plaining_time_without_table_name / num_iterations
av_ET1 = execution_time_with_table_name / num_iterations
av_ET2 = execution_time_without_table_name / num_iterations

print(f'Results:\n'
      f'Average Plaining with TABLE_NAME on ORDER BY: {av_PT1} ms\n'
      f'Average Plaining without TABLE_NAME on ORDER BY: {av_PT2} ms\n'
      f'Average Execution with TABLE_NAME on ORDER BY: {av_ET1} ms\n'
      f'Average Execution without TABLE_NAME on ORDER BY: {av_ET2} ms\n')