def test_broken_aqo_tables(install_postgres): """ Learn aqo with specific SQL query, partially remove records for this query from aqo tables and make sure aqo will add it again. """ SQL_QUERY = "SELECT 1" install_postgres.load_extension('aqo') conn = psycopg2.connect(install_postgres.connstring) execute(conn, 'SET aqo.mode TO intelligent') execute(conn, SQL_QUERY) num = execute( conn, "SELECT COUNT(*) FROM aqo_query_texts" " WHERE query_text = '%s'" % SQL_QUERY)[0][0] assert num == 1 execute(conn, "DELETE FROM aqo_query_texts WHERE query_text = '%s'" % SQL_QUERY) execute(conn, SQL_QUERY) num = execute( conn, "SELECT COUNT(*) FROM aqo_query_texts" "WHERE query_text = '%s'" % SQL_QUERY)[0][0] assert num == 1
def populate_tpch(request): """ This method setup tables for TPC-H benchmark. """ import psycopg2 from helpers.sql_helpers import execute CONN_STRING = "host='localhost' user='******'" # GETTING A BENCHMARK TPCH_SCALE = "1" # 1, 10, 100, 300, 1000, 3000, 10000, 30000, 100000 COMMIT_HASH = "c5cd7711cc35" TPCH_BENCHMARK = "https://bitbucket.org/tigvarts/tpch-dbgen/get/" \ "%s.zip" % COMMIT_HASH tbls = ["region.tbl", "nation.tbl", "customer.tbl", "supplier.tbl", "part.tbl", "partsupp.tbl", "orders.tbl", "lineitem.tbl"] sqls = ["postgres_dll.sql", "postgres_load.sql", "postgres_ri.sql"] tpch_archive = os.path.join(TMP_DIR, "tpch-benchmark-%s.zip" % COMMIT_HASH) if not os.path.exists(tpch_archive): download_file(TPCH_BENCHMARK, tpch_archive) tpch_dir = os.path.join(TMP_DIR, "tigvarts-tpch-dbgen-%s" % COMMIT_HASH) if not os.path.exists(tpch_dir): os.mkdir(tpch_dir) subprocess.check_output(["unzip", tpch_archive, "-d", TMP_DIR]) os.chdir(tpch_dir) # SETUP DATABASE (see ./install.sh) subprocess.check_output(["make"]) # TODO: run multiple parallel streams when # generating large amounts of data subprocess.check_output(["./dbgen", "-s", TPCH_SCALE, "-vf"]) assert TMP_DIR == '/tmp' for t in tbls: if os.path.exists(os.path.join(TMP_DIR, t)): os.remove(os.path.join(TMP_DIR, t)) shutil.move(os.path.join(tpch_dir, t), TMP_DIR) conn = psycopg2.connect(CONN_STRING) for sql_file in sqls: execute(conn, open(os.path.join(tpch_dir, sql_file)).read()) conn.close() for t in tbls: tbl_path = os.path.join(TMP_DIR, t) if os.path.exists(tbl_path): os.remove(tbl_path)
def populate_imdb(request): """ This method needed for creating tables and populate them with IMDb dataset. http://www.imdb.com/interfaces """ import psycopg2 from helpers.sql_helpers import execute CONN_STRING = "host='localhost' user='******'" job_file = os.path.join(TMP_DIR, "join-order-benchmark.tar.gz") job_dir = os.path.join(TMP_DIR, "join-order-benchmark-0.1") job_url = "https://codeload.github.com/" \ "ligurio/join-order-benchmark/tar.gz/0.1" if not os.path.exists(job_file): download_file(job_url, job_file) subprocess.check_output(["tar", "xvzf", job_file, "-C", TMP_DIR]) # CUSTOM METHOD OF DATABASE SETUP imdb_tgz = os.path.join(TMP_DIR, "imdb.tgz") if not os.path.exists(imdb_tgz): download_file("http://homepages.cwi.nl/~boncz/job/imdb.tgz", imdb_tgz) imdb_csv = os.path.join(TMP_DIR, "imdb_csv") if not os.path.exists(imdb_csv): os.mkdir(imdb_csv) subprocess.check_output(["tar", "xvzf", imdb_tgz, "-C", imdb_csv]) os.chdir(imdb_csv) for csv in glob.glob('*.csv'): csv_file = os.path.join(imdb_csv, csv) data = open(csv_file).read().split('\n') for i in range(len(data)): data[i] = data[i].replace(r'\\', '').replace(r'\"', '') print(data[i]) with open(csv_file, 'w') as f: f.write('\n'.join(data)) assert TMP_DIR == '/tmp' sql_files = ["schema", "imdb_load", "fkindexes", "imdb_analyse"] conn = psycopg2.connect(CONN_STRING) for f in sql_files: sql_path = os.path.join(job_dir, f + '.sql') with open(sql_path, 'r') as file: execute(conn, file.read()) conn.close()
def set_query_aqo_param(sql_query, param_name, value, connstring): """ Set aqo parameter for specified SQL query. Possible parameters: learn_aqo, use_aqo, fspace_hash, auto_tuning """ conn = psycopg2.connect(connstring) execute( conn, "UPDATE aqo_queries SET %s = %s \ WHERE query_hash = \ (SELECT query_hash from aqo_query_texts \ WHERE query_text = '%s')" % (param_name, value, sql_query)) conn.close() assert get_query_aqo_param(sql_query, param_name, connstring) == value
def test_default_aqo_mode(install_postgres): """ Make sure default aqo mode stay without changes """ install_postgres.load_extension('aqo') conn = psycopg2.connect(install_postgres.connstring) SQL_QUERY = "SELECT boot_val FROM pg_settings WHERE name='aqo.mode'" mode = execute(conn, SQL_QUERY)[0][0] conn.close() assert mode == "controlled"
def query_to_hash(sql_query, connstring): """ Return query hash for specified SQL query. """ conn = psycopg2.connect(connstring) query_hash_sql = execute( conn, "SELECT query_hash \ FROM aqo_query_texts \ WHERE query_text='%s'" % sql_query) conn.close() return query_hash_sql
def test_available_modes(install_postgres): """ Make sure aqo extension provides only known modes. """ install_postgres.load_extension('aqo') known_modes = ['intelligent', 'forced', 'controlled', 'disabled'] conn = psycopg2.connect(install_postgres.connstring) SQL_QUERY = "SELECT enumvals FROM pg_settings WHERE name='aqo.mode';" actual_modes = execute(conn, SQL_QUERY)[0][0] conn.close() assert known_modes == actual_modes
def test_in_honor_of_teodor(install_postgres): PREP_TABLE_QUERY = """ DROP TABLE IF EXISTS t; DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; SELECT id::int4, (id/3)::int4 AS v INTO t FROM GENERATE_SERIES(1,10000) AS id; CREATE UNIQUE INDEX it ON t (id, v); SELECT (100.0 * RANDOM())::int4 AS id, (100.0 * RANDOM())::int4 AS v INTO a FROM GENERATE_SERIES(1,10000) AS id; CREATE INDEX ia ON a (id, v); SELECT (100000.0 * RANDOM())::int4 AS id, (100000.0 * RANDOM())::int4 AS v INTO b FROM GENERATE_SERIES(1,10000) AS id; CREATE INDEX ib ON b (id, v); """ queries = [ "SELECT * FROM t t1, t t2 WHERE t1.id = t2.id AND t1.v = t2.v;", "SELECT * FROM a t1, a t2 WHERE t1.id = t2.id AND t1.v = t2.v;", "SELECT * FROM b t1, b t2 WHERE t1.id = t2.id AND t1.v = t2.v;", "SELECT * FROM a t1, t t2 WHERE t1.id = t2.id AND t1.v = t2.v;", "SELECT * FROM b t1, t t2 WHERE t1.id = t2.id AND t1.v = t2.v;", "SELECT * FROM a t1, b t2 WHERE t1.id = t2.id AND t1.v = t2.v;" ] install_postgres.load_extension('aqo') connstring = install_postgres.connstring conn = psycopg2.connect(connstring) execute(conn, PREP_TABLE_QUERY) execute(conn, 'VACUUM ANALYZE t') execute(conn, 'VACUUM ANALYZE a') execute(conn, 'VACUUM ANALYZE b') conn.close() time.sleep(10) reset_aqo_stats(connstring) install_postgres.set_option('aqo.mode', 'intelligent') for q in queries: print(q) stats = learn_aqo(q, 100) plot_stats(stats, connstring) evaluate_aqo(stats)
def get_query_aqo_stat(sql_query, param_name, connstring): """ Return aqo statistics for a specific query: - execution time with/without aqo - planning time with/without aqo - cardinality error with/without aqo - executions with/without aqo """ conn = psycopg2.connect(connstring) response = execute( conn, "SELECT %s FROM aqo_query_stat \ WHERE query_hash = \ (SELECT query_hash from aqo_query_texts \ WHERE query_text = '%s')" % (param_name, sql_query)) conn.close() return response
def get_query_aqo_param(sql_query, param_name, connstring): """ Return aqo parameter for specified SQL query. Possible parameters: learn_aqo, use_aqo, fspace_hash, auto_tuning """ conn = psycopg2.connect(connstring) value = execute( conn, "SELECT %s FROM aqo_queries \ WHERE query_hash = \ (SELECT query_hash from aqo_query_texts \ WHERE query_text = '%s')" % (param_name, sql_query)) conn.close() if len(value) is not 0: return value[0][0] else: return None
def test_max_query_length(install_postgres): """ Maximum SQL query length in PostgreSQL is about 1Gb. AQO extension stores queries in a special table - aqo_query_texts and then uses this column to retrieve query statistics. We should make sure that column can store queries with maximum length there. """ install_postgres.load_extension('aqo') reset_aqo_stats(install_postgres.connstring) conn = psycopg2.connect(install_postgres.connstring) column_name = 'query_text' column_type_query = "SELECT format_type(atttypid, atttypmod) \ AS type FROM pg_attribute \ WHERE attrelid = 'aqo_query_texts'::regclass \ AND attname = '%s';" % column_name type = execute(conn, column_type_query)[0][0] conn.close() assert type == "character varying"
def test_similar_queries(install_postgres): """ Make sure AQO uses common statistic data for queries with the same constants in SQL clauses. """ install_postgres.load_extension('aqo') reset_aqo_stats(install_postgres.connstring) conn = psycopg2.connect(install_postgres.connstring) execute(conn, 'SELECT 1') num1 = execute( conn, "SELECT COUNT(*) FROM aqo_query_texts" " WHERE query_text = 'SELECT 1'")[0][0] execute(conn, 'SELECT 2') num2 = execute( conn, "SELECT COUNT(*) FROM aqo_query_texts" " WHERE query_text = 'SELECT 2'")[0][0] conn.close() assert num1 == 1 assert num2 == 0
def test_tuning_max_iterations(install_postgres): """ AQO will disable query optimization after N unsussessful attempts. N is defined in auto_tuning_max_iterations in aqo.c """ install_postgres.load_extension('aqo') reset_aqo_stats(install_postgres.connstring) conn = psycopg2.connect(install_postgres.connstring) execute(conn, 'SELECT 1') num1 = execute( conn, "SELECT COUNT(*) FROM aqo_query_texts" " WHERE query_text = 'SELECT 1'")[0][0] execute(conn, 'SELECT 2') num2 = execute( conn, "SELECT COUNT(*) FROM aqo_query_texts " "WHERE query_text = 'SELECT 2'")[0][0] conn.close() assert num1 == 1 assert num2 == 0
def learn_aqo(sql_query, connstring, number=AQO_AUTO_TUNING_MAX_ITERATIONS): """ This function is intended to learn AQO with a specific function with exact number of iterations equal to AQO_AUTO_TUNING_MAX_ITERATIONS """ conn = psycopg2.connect(connstring) stats = { 'default': [], 'aqo': [], 'aqo_stat': [], 'learn_aqo_true': '', 'learn_aqo_false': '', 'use_aqo_true': '', 'use_aqo_false': '', 'query': sql_query } sql_query_analyze = 'EXPLAIN ANALYZE ' + sql_query aqo_mode = execute(conn, 'SHOW aqo.mode')[0][0] pg_set_option(connstring, 'aqo.mode', 'disabled') for i in range(0, number): dict = parse_explain_analyze_stat(execute(conn, sql_query_analyze)) stats['default'].append(dict) pg_set_option(connstring, 'aqo.mode', aqo_mode) aqo_stat = [] for i in range(0, number): use_aqo = get_query_aqo_param(sql_query_analyze, 'use_aqo', connstring) learn_aqo = get_query_aqo_param(sql_query_analyze, 'learn_aqo', connstring) auto_tuning = get_query_aqo_param(sql_query_analyze, 'auto_tuning', connstring) dict = parse_explain_analyze_stat(execute(conn, sql_query_analyze)) stats['aqo'].append(dict) if learn_aqo: if stats['learn_aqo_true'] == '': stats['learn_aqo_true'] = i if use_aqo: cardinality_error = get_query_aqo_stat( sql_query_analyze, 'cardinality_error_with_aqo', connstring)[0][0][-1] else: cardinality_error = get_query_aqo_stat( sql_query_analyze, 'cardinality_error_without_aqo', connstring)[0][0][-1] else: if stats['learn_aqo_false']: stats['learn_aqo_false'] = i cardinality_error = 0 if use_aqo: if stats['use_aqo_true'] == '': stats['use_aqo_true'] = i execution_time = get_query_aqo_stat(sql_query_analyze, 'execution_time_with_aqo', connstring)[0][0][-1] planning_time = get_query_aqo_stat(sql_query_analyze, 'planning_time_with_aqo', connstring)[0][0][-1] elif learn_aqo or auto_tuning: execution_time = get_query_aqo_stat(sql_query_analyze, 'execution_time_without_aqo', connstring)[0][0][-1] planning_time = get_query_aqo_stat(sql_query_analyze, 'planning_time_without_aqo', connstring)[0][0][-1] else: if stats['use_aqo_false'] == '': stats['use_aqo_false'] = i execution_time = 0 planning_time = 0 dict = { 'execution_time': execution_time, 'planning_time': planning_time, 'cardinality_error': cardinality_error } aqo_stat.append(dict) stats['aqo_stat'] = aqo_stat conn.close() return stats
def test_pgprobackup_compression_continious_backup(self, request, install_postgres): """Test pg_probackup with compression feature and full continous backup Scenario: 1. Create backup dir 2. Set data dir to environment variables 3. Init backup dir 4. Set options for pg_probackup 5. Create tablespace with compression 6. Create table in compression tablespace 7. Edit pg_hba_config 8. Make full backup 9. Get backup id 10. Check that backup status is OK 11. Check that backup validation is OK """ # Step 1 backup_dir = self.create_backup_directory() # Step 2 os.environ["PGDATA"] = install_postgres.get_option('data_directory') # Step 3 install_postgres.edit_pg_hba_conf(self.PG_HBA_CONFIG) install_postgres.manage_psql('restart') # Step 4 self.execute_pg_probackup("init") for root, dirs, files in os.walk(backup_dir): for d in dirs: os.chown(os.path.join(root, d), pwd.getpwnam("postgres").pw_uid, grp.getgrnam("postgres").gr_gid) for f in files: os.chown(os.path.join(root, f), pwd.getpwnam("postgres").pw_uid, grp.getgrnam("postgres").gr_gid) # Step 5 install_postgres.set_option("ptrack_enable", "on") install_postgres.set_option("wal_level", "archive") install_postgres.set_option("archive_mode", "on") install_postgres.set_option( "archive_command", "test ! -f {0}/wal/%f && cp %p {1}/wal/%f".format( backup_dir, backup_dir)) install_postgres.set_option("cfs_gc_workers", "0") # Step 6 tablespace_path = os.path.join(TMP_DIR, 'pgprobackup_compression') os.mkdir(tablespace_path) os.chown(tablespace_path, pwd.getpwnam("postgres").pw_uid, grp.getgrnam("postgres").gr_gid) conn = psycopg2.connect(install_postgres.connstring) execute( conn, 'CREATE TABLESPACE pgprobackup_compression' ' LOCATION \'%s\' WITH(compression = true);' % tablespace_path) # Step 7 execute(conn, 'CREATE TABLE tbl TABLESPACE pgprobackup_compression' ' AS SELECT i, md5(random()::text)' ' FROM generate_series(0,1e05) AS i;') # Step 8 self.execute_pg_probackup("backup", "-b", "full", "-d", "postgres", "-U", "postgres") # Step 9 # Get last backup id and get out for show command with this backup pgprobackup_show = subprocess.Popen( ["%s/pg_probackup" % pg_bindir(), "show", "-U", "postgres"], stdout=subprocess.PIPE) awk_backup_id = subprocess.Popen( ["awk", "FNR == 4 {print $1}"], stdin=pgprobackup_show.stdout, stdout=subprocess.PIPE) backup_id = awk_backup_id.communicate()[0].strip() pgprobackup_show.stdout.close() # Step 10 backup_info = self.parse_pgprobackup_show_command_out( self.execute_pg_probackup("show", backup_id)) assert backup_info['STATUS'] == 'OK' # Step 11 self.execute_pg_probackup("validate", backup_id)
def reset_aqo_stats(connstring, sql_query=None): conn = psycopg2.connect(connstring) if sql_query is not None: execute( conn, "DELETE FROM aqo_query_stat \ WHERE query_hash = (SELECT query_hash from aqo_query_texts \ WHERE query_text = '%s')" % sql_query) execute( conn, "DELETE FROM aqo_queries \ WHERE query_hash = (SELECT query_hash from aqo_query_texts \ WHERE query_text = '%s')" % sql_query) execute( conn, "DELETE FROM aqo_query_texts \ WHERE query_text = '%s'" % sql_query) else: execute(conn, "TRUNCATE aqo_data CASCADE") execute(conn, "TRUNCATE aqo_queries CASCADE") execute(conn, "TRUNCATE aqo_query_stat CASCADE") execute(conn, "TRUNCATE aqo_query_texts CASCADE") conn.close()
def test_tpcds_benchmark(install_postgres): """ TPC-DS benchmark """ # https://github.com/tigvarts/tpcds-kit # tools/How_To_Guide.doc TPCDS_SCALE = "1" # 100GB, 300GB, 1TB, 3TB, 10TB, 30TB and 100TB. TPCDS_BENCHMARK = "https://github.com/ligurio/tpcds-kit/archive/0.1.zip" # GETTING A BENCHMARK tpcds_archive = os.path.join(TMP_DIR, "tpcds-kit-0.1.zip") if not os.path.exists(tpcds_archive): download_file(TPCDS_BENCHMARK, tpcds_archive) tpcds_dir = os.path.join(TMP_DIR, "tpcds-kit-0.1") if not os.path.exists(tpcds_dir): os.mkdir(tpcds_dir) subprocess.check_output(["unzip", tpcds_archive, "-d", TMP_DIR]) tpcds_dir = os.path.join(TMP_DIR, "tpcds-kit-0.1/tools") os.chdir(tpcds_dir) # SETUP DATABASE (see ./install.sh) if platform.system() == 'Darwin': shutil.copy("Makefile.osx", "Makefile") else: shutil.copy("Makefile.suite", "Makefile") subprocess.check_call(["make"]) p = subprocess.Popen([ "dsdgen", "-verbose", "-force", "-dir", TMP_DIR, "-scale", TPCDS_SCALE, "-filter" ], stdout=subprocess.PIPE, stderr=subprocess.PIPE) stdout, stderr = p.communicate() print(stdout, stderr) assert p.wait() == 0 # FIXME: assert -11 == 0 connstring = install_postgres.connstring conn = psycopg2.connect(connstring) execute(conn, "DROP DATABASE IF EXISTS tpcds") # create_test_database("tpcds") execute(conn, "CREATE DATABASE tpcds") conn.close() sqls = ["tpcds.sql", "tpcds_load.sql", "tpcds_ri.sql"] for sql_file in sqls: sql_path = os.path.join(tpcds_dir, sql_file) sql = subprocess.Popen(["cat", sql_path], subprocess.PIPE) psql = subprocess.Popen(["psql", "-d", "tpcds"], stdin=sql.stdout) assert psql.wait() == 0 # os.remove(os.path.join(TMP_DIR, '*.dat')) # RUN WORKLOAD # relationship between database size (SF) and query streams (N) # see tools/How_To_Guide.doc # query_streams = {"100": "7", # "300": "9", # "1000": "11", # "3000": "13", # "10000": "15", # "30000": "17", # "100000": "15", # "100000": "19"} install_postgres.load_extension('aqo') reset_aqo_stats(connstring) query_templates = os.path.join(tpcds_dir, "query_variants/*.tpl") for template in glob.glob(query_templates): stats = learn_aqo(template, connstring, 100) plot_stats(stats, connstring) evaluate_aqo(stats)
def test_1C_sample(install_postgres): SQL_QUERY = """ SELECT * FROM _AccRgAT31043 , _AccRgAT31043_ttgoab153 T2 WHERE (T2._Period = _AccRgAT31043._Period AND T2._AccountRRef = _AccRgAT31043._AccountRRef AND T2._Fld1009RRef = _AccRgAT31043._Fld1009RRef AND (COALESCE(T2._Fld1010RRef,'\\377'::bytea) = COALESCE(_AccRgAT31043._Fld1010RRef,'\\377 '::bytea)) AND (COALESCE(T2._Fld1011RRef,'\\377'::bytea) = COALESCE(_AccRgAT31043._Fld1011RRef,'\\377'::bytea)) AND T2._Fld995 = _AccRgAT31043._Fld995 AND (COALESCE(T2._Value1_TYPE,'\\377'::bytea) = COALESCE(_AccRgAT31043._Value1_TYPE,'\\377'::bytea) AND COALESCE(T2._Value1_RTRef,'\\377'::bytea) = COALESCE(_AccRgAT31043._Value1_RTRef,'\\377'::bytea) AND COALESCE(T2._Value1_RRRef,'\\377'::bytea) = COALESCE(_AccRgAT31043._Value1_RRRef,'\\377'::bytea)) AND (COALESCE(T2._Value2_TYPE,'\\377'::bytea) = COALESCE(_AccRgAT31043._Value2_TYPE,'\\377'::bytea) AND COALESCE(T2._Value2_RTRef,'\\377'::bytea) = COALESCE(_AccRgAT31043._Value2_RTRef,'\\377'::bytea) AND COALESCE(T2._Value2_RRRef,'\\377'::bytea) = COALESCE(_AccRgAT31043._Value2_RRRef, '\\377'::bytea)) AND (COALESCE(T2._Value3_TYPE,'\\377'::bytea) = COALESCE(_AccRgAT31043._Value3_TYPE,'\\377'::bytea) AND COALESCE(T2._Value3_RTRef,'\\377'::bytea) = COALESCE(_AccRgAT31043._Value3_RTRef,'\\377'::bytea) AND COALESCE(T2._Value3_RRRef,'\\377'::bytea) = COALESCE(_AccRgAT31043._Value3_RRRef,'\\377'::bytea)) AND _AccRgAT31043._Splitter = 0) AND (T2._EDCount = 3) AND (_AccRgAT31043._Fld995 = 271602); """ DUMP_URL = "http://dist.l.postgrespro.ru/vm-images/" \ "test/blobs/pg.sql.bz" archive_path = os.path.join(TMP_DIR, "pg.sql.gz") plain_path = os.path.join(TMP_DIR, "pg.sql") if not os.path.exists(plain_path): if not os.path.exists(archive_path): download_file(DUMP_URL, archive_path) os.chdir(TMP_DIR) subprocess.check_output(['gunzip', archive_path]) # with open(plain_path, 'r') as file: # psql = subprocess.Popen(["psql"], stdin=file) # psql.wait() # assert psql.returncode == 0 connstring = install_postgres.connstring conn = psycopg2.connect(connstring) execute(conn, open(plain_path, "r").read()) execute(conn, 'VACUUM ANALYZE _accrgat31043') conn.close() time.sleep(10) install_postgres.load_extension('aqo') reset_aqo_stats(connstring) # FIXME: execute(conn, 'SET escape_string_warning=off') # FIXME: execute(conn, 'SET enable_mergejoin=off') stats = learn_aqo(SQL_QUERY, connstring) plot_stats(stats, connstring) evaluate_aqo(stats)
def test_aqo_mode(aqo_mode, install_postgres): """ Testcase validates available aqo modes +-------------+-------------+-------------+-----------------+ | | optimize | optimize | separate record | | aqo mode | new queries | old queries | for each query | +-----------------------------------------------------------+ | intelligent | Yes | Yes | Yes | | manual | No | Yes | Yes | | forced | Yes | Yes | No | | disabled | No | No | - | +-------------+-------------+-------------+-----------------+ """ OLD_SQL_QUERY = "SELECT * FROM pg_class WHERE relpages > 455" NEW_SQL_QUERY = "SELECT * FROM pg_class" \ " WHERE reltablespace != reltoastrelid" old_sql_query_explain = 'EXPLAIN ANALYZE ' + OLD_SQL_QUERY new_sql_query_explain = 'EXPLAIN ANALYZE ' + NEW_SQL_QUERY install_postgres.load_extension('aqo') connstring = install_postgres.connstring reset_aqo_stats(connstring) install_postgres.set_option('aqo.mode', 'intelligent') learn_aqo(OLD_SQL_QUERY, connstring) install_postgres.set_option('aqo.mode', aqo_mode) learn_aqo(NEW_SQL_QUERY, connstring) conn = psycopg2.connect(install_postgres.connstring) num_old_sql_query = execute( conn, "SELECT COUNT(*) FROM aqo_query_texts" " WHERE query_text = '%s'" % old_sql_query_explain)[0][0] num_new_sql_query = execute( conn, "SELECT COUNT(*) FROM aqo_query_texts" " WHERE query_text = '%s'" % new_sql_query_explain)[0][0] if aqo_mode == 'forced' or aqo_mode == 'disabled': executions_w_aqo = execute( conn, "SELECT executions_with_aqo FROM aqo_query_stat" " WHERE query_hash = 0;") executions_wo_aqo = execute( conn, "SELECT executions_without_aqo FROM aqo_query_stat" " WHERE query_hash = 0;") elif aqo_mode == 'intelligent': new_executions_w_aqo = get_query_aqo_stat(new_sql_query_explain, 'executions_with_aqo', connstring)[0][0] new_executions_wo_aqo = get_query_aqo_stat(new_sql_query_explain, 'executions_without_aqo', connstring)[0][0] old_executions_w_aqo = get_query_aqo_stat(old_sql_query_explain, 'executions_with_aqo', connstring) old_executions_wo_aqo = get_query_aqo_stat(old_sql_query_explain, 'executions_without_aqo', connstring) conn.close() if aqo_mode == 'intelligent': assert num_old_sql_query == 1 assert num_new_sql_query == 1 assert new_executions_w_aqo + new_executions_wo_aqo == \ AQO_AUTO_TUNING_MAX_ITERATIONS assert old_executions_w_aqo + old_executions_wo_aqo == \ AQO_AUTO_TUNING_MAX_ITERATIONS # TODO: check optimization of old query # TODO: check optimization of new query elif aqo_mode == 'forced': assert num_old_sql_query == 1 assert num_new_sql_query == 0 assert executions_w_aqo + executions_wo_aqo == \ AQO_AUTO_TUNING_MAX_ITERATIONS # TODO: check optimization of old query # TODO: check optimization of new query elif aqo_mode == 'manual': assert num_old_sql_query == 1 assert num_new_sql_query == 0 # TODO: check optimization of old query # TODO: check optimization of new query elif aqo_mode == 'disabled': assert num_old_sql_query == 1 assert num_new_sql_query == 0 assert executions_w_aqo + executions_wo_aqo == 0 # TODO: check optimization of old queries # TODO: check optimization of new queries else: pytest.fail("Unknown AQO mode - %s" % aqo_mode)