baselines.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlglot\n",
    "import mysql.connector\n",
    "from functools import partial\n",
    "import json\n",
    "import numpy as np\n",
    "import random\n",
    "from tqdm import tqdm\n",
    "import copy\n",
    "\n",
    "pd.set_option(\"display.max_rows\", 100)\n",
    "\n",
    "database = mysql.connector.connect(\n",
    "    user='root', \n",
    "    password='password',\n",
    "    host='127.0.0.1', \n",
    "    port=3307,\n",
    "    database=\"TPCH\",\n",
    ")\n",
    "\n",
    "cursor = database.cursor()\n",
    "\n",
    "def partial_transformer(node, table_index_info):\n",
    "    if isinstance(node, sqlglot.exp.Table):\n",
    "        table_name = node.this.output_name\n",
    "        if table_name in table_index_info:\n",
    "            indexes = [ f\"index_{table_name}_{column}\" for column in table_index_info[table_name][\"indexes\"]]\n",
    "            use_index_flag = table_index_info[table_name][\"use_index_flag\"] if len(indexes) > 0 else True\n",
    "        else:\n",
    "            use_index_flag = True\n",
    "            indexes = list()\n",
    "\n",
    "        table_hint = sqlglot.exp.IndexTableHint()\n",
    "        table_hint.set(\"this\", \"USE\" if use_index_flag and len(indexes) > 0 else \"USE\" if use_index_flag else \"IGNORE\")\n",
    "        indexes_identifier = sqlglot.exp.Identifier()\n",
    "        indexes_identifier.set(\"this\", \", \".join(indexes))\n",
    "        table_hint.set(\"expressions\", table_hint.expressions + [indexes_identifier])\n",
    "        node.set(\"hints\", node.expressions + [table_hint])\n",
    "        return node\n",
    "    return node\n",
    "\n",
    "def get_query_cost(query, table_index_info, default=False) -> float:\n",
    "    table_index_info = copy.deepcopy(table_index_info)\n",
    "\n",
    "    for table in table_index_info:\n",
    "        if table_index_info[table][\"use_index_flag\"]:\n",
    "            table_index_info[table][\"indexes\"] = [col for col in table_index_info[table][\"indexes\"] if col in query]\n",
    "    \n",
    "    if not default:\n",
    "        expression_tree = sqlglot.parse_one(query)\n",
    "        transformer = partial(partial_transformer, table_index_info=table_index_info)\n",
    "        transformed_tree = expression_tree.transform(transformer)\n",
    "        index_specified_query = transformed_tree.sql()\n",
    "        # print(index_specified_query)\n",
    "        cursor.execute(f\"EXPLAIN FORMAT='JSON' {index_specified_query}\")\n",
    "    else:\n",
    "        cursor.execute(f\"EXPLAIN FORMAT='JSON' {query}\")\n",
    "    \n",
    "\n",
    "    query_cost = json.loads(cursor.fetchall()[0][0])[\"query_block\"][\"cost_info\"][\"query_cost\"]\n",
    "\n",
    "    return float(query_cost)\n",
    "\n",
    "def get_query_cost_actual(query, table_index_info, default=False) -> float:\n",
    "    table_index_info = copy.deepcopy(table_index_info)\n",
    "\n",
    "    for table in table_index_info:\n",
    "        if table_index_info[table][\"use_index_flag\"]:\n",
    "            table_index_info[table][\"indexes\"] = [col for col in table_index_info[table][\"indexes\"] if col in query]\n",
    "    \n",
    "    if not default:\n",
    "        expression_tree = sqlglot.parse_one(query)\n",
    "        transformer = partial(partial_transformer, table_index_info=table_index_info)\n",
    "        transformed_tree = expression_tree.transform(transformer)\n",
    "        index_specified_query = transformed_tree.sql()\n",
    "        cursor.execute(f\"EXPLAIN ANALYZE {index_specified_query}\")\n",
    "    else:\n",
    "        cursor.execute(f\"EXPLAIN ANALYZE {query}\")\n",
    "    \n",
    "    return float(cursor.fetchall()[0][0].split(\"actual time=\")[1].split(\"..\")[1].split(\" \")[0])\n",
    "\n",
    "def get_index_cost(table_index_info) -> float:\n",
    "    index_name_list = []\n",
    "    for table_name in table_index_info:\n",
    "        for column in table_index_info[table_name][\"indexes\"]:\n",
    "            index_name_list.append(f\"index_{table_name}_{column}\")\n",
    "\n",
    "    index_name_list_string = \"('\"+ \"','\".join(index_name_list) + \"')\"\n",
    "        \n",
    "    cursor.execute(f\"SELECT ROUND(SUM(stat_value * @@innodb_page_size / 1024 / 1024), 2) size_in_mb FROM mysql.innodb_index_stats WHERE stat_name = 'size' AND index_name != 'PRIMARY' AND database_name = 'TPCH' AND index_name IN {index_name_list_string}\")\n",
    "    return float(cursor.fetchone()[0])\n",
    "\n",
    "def std(array):\n",
    "    return np.std(array, ddof=len(array) - 1)\n",
    "\n",
    "def average_std(data):\n",
    "    return np.sqrt(np.square(data).sum())\n",
    "\n",
    "def get_table_index_info_extremes(database):\n",
    "    tables_list = pd.read_sql(\"SHOW TABLES\", database)[\"Tables_in_TPCH\"].tolist()\n",
    "    index_table_mapping = dict()\n",
    "    index_list = list()\n",
    "    for table in tables_list:\n",
    "        query_result = pd.read_sql(f\"SHOW indexes FROM {table} WHERE key_name LIKE 'index_%'\", database)\n",
    "        index_table_mapping[table] = query_result[\"Column_name\"].tolist()\n",
    "        index_list += query_result[\"Key_name\"].tolist()\n",
    "\n",
    "    table_names = [x.split(\"_\")[1] for x in index_list]\n",
    "\n",
    "    table_index_info_noopt = dict()\n",
    "    for i in range(len(index_list)):\n",
    "        index = index_list[i]\n",
    "        table = table_names[i]\n",
    "        index_col = index.replace(f\"index_{table}_\", \"\")\n",
    "        if table in table_index_info_noopt:\n",
    "            table_index_info_noopt[table][\"indexes\"].append(index_col)\n",
    "        else:\n",
    "            table_index_info_noopt[table] = {\n",
    "                \"use_index_flag\": False,\n",
    "                \"indexes\": [index_col],\n",
    "            }\n",
    "    \n",
    "    table_index_info_allopt = copy.deepcopy(table_index_info_noopt)\n",
    "\n",
    "    for table in table_index_info_allopt:\n",
    "        table_index_info_allopt[table][\"use_index_flag\"] = True\n",
    "\n",
    "    return table_index_info_noopt, table_index_info_allopt\n",
    "\n",
    "def get_table_index_info_inverse(table_index_info, table_index_info_noopt):\n",
    "    table_index_info_inverse = dict()\n",
    "\n",
    "    for table in table_index_info_noopt:\n",
    "        table_index_info_inverse[table] = copy.deepcopy(table_index_info_noopt[table])\n",
    "        if table in table_index_info:\n",
    "            indexes = table_index_info[table][\"indexes\"]\n",
    "            table_index_info_inverse[table][\"indexes\"] = list(set(table_index_info_inverse[table][\"indexes\"]) - set(indexes))\n",
    "    \n",
    "    return table_index_info_inverse\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_reader = open(\"queries/test_queries.sql\")\n",
    "queries = sql_reader.read().split(\";\")\n",
    "sql_reader.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "table_index_info_humopt = {\n",
    "    \"lineitem\": {\n",
    "        \"use_index_flag\": True,\n",
    "        \"indexes\": [\"l_shipdate\", \"l_partkey\", \"l_quantity\"],\n",
    "    },\n",
    "    \"orders\": {\n",
    "        \"use_index_flag\": True,\n",
    "        \"indexes\": [\"o_orderpriority\", \"o_custkey\", \"o_orderdate\"],\n",
    "    },\n",
    "    \"part\": {\n",
    "        \"use_index_flag\": True,\n",
    "        \"indexes\": [\"p_type\"]\n",
    "    }   \n",
    "}\n",
    "\n",
    "table_index_info_greedyopt =  {\n",
    "    'orders': {\n",
    "        'use_index_flag': True, \n",
    "        'indexes': ['o_custkey']\n",
    "    }, \n",
    "    'lineitem': {\n",
    "        'use_index_flag': True, \n",
    "        'indexes': ['l_suppkey']\n",
    "    }, \n",
    "    'part': {\n",
    "        'use_index_flag': True, \n",
    "        'indexes': ['p_container']\n",
    "    }, \n",
    "    'customer': {\n",
    "        'use_index_flag': True, \n",
    "        'indexes': ['c_nationkey', 'c_mktsegment']\n",
    "    }\n",
    "}\n",
    "\n",
    "table_index_info_mlopt = {'orders': {'use_index_flag': True, 'indexes': ['o_custkey', 'o_orderdate']},\n",
    " 'customer': {'use_index_flag': True, 'indexes': ['c_nationkey']},\n",
    " 'part': {'use_index_flag': True, 'indexes': ['p_brand']},\n",
    " 'lineitem': {'use_index_flag': True, 'indexes': ['l_suppkey']}}\n",
    "\n",
    "table_index_info_mlopt_25 = {'lineitem': {'use_index_flag': True, 'indexes': ['l_shipdate']},\n",
    " 'orders': {'use_index_flag': True, 'indexes': ['o_orderdate']},\n",
    " 'part': {'use_index_flag': True, 'indexes': ['p_container', 'p_brand']},\n",
    " 'partsupp': {'use_index_flag': True, 'indexes': ['ps_suppkey']}}\n",
    "\n",
    "table_index_info_mlopt_50 =  {'lineitem': {'use_index_flag': True, 'indexes': ['l_shipdate']},\n",
    " 'part': {'use_index_flag': True, 'indexes': ['p_size', 'p_container']},\n",
    " 'partsupp': {'use_index_flag': True, 'indexes': ['ps_suppkey']},\n",
    " 'orders': {'use_index_flag': True, 'indexes': ['o_custkey', 'o_orderdate']},\n",
    " 'supplier': {'use_index_flag': True, 'indexes': ['s_nationkey']},\n",
    " 'customer': {'use_index_flag': True,\n",
    "  'indexes': ['c_nationkey', 'c_mktsegment']}}\n",
    "\n",
    "table_index_info_mlopt_75 =   {'lineitem': {'use_index_flag': True, 'indexes': ['l_shipdate']}, 'part': {'use_index_flag': True, 'indexes': ['p_size', 'p_type', 'p_mfgr']}, 'supplier': {'use_index_flag': True, 'indexes': ['s_nationkey']}}\n",
    "\n",
    "table_index_info_mlopt_100 =   {'lineitem': {'use_index_flag': True, 'indexes': ['l_shipdate']}, 'part': {'use_index_flag': True, 'indexes': ['p_size', 'p_container', 'p_mfgr']}, 'partsupp': {'use_index_flag': True, 'indexes': ['ps_suppkey']}}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/k2/zns3zzl52ngb09pgfbbnb3pm0000gn/T/ipykernel_23784/2061268447.py:100: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  tables_list = pd.read_sql(\"SHOW TABLES\", database)[\"Tables_in_TPCH\"].tolist()\n",
      "/var/folders/k2/zns3zzl52ngb09pgfbbnb3pm0000gn/T/ipykernel_23784/2061268447.py:104: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  query_result = pd.read_sql(f\"SHOW indexes FROM {table} WHERE key_name LIKE 'index_%'\", database)\n",
      "/var/folders/k2/zns3zzl52ngb09pgfbbnb3pm0000gn/T/ipykernel_23784/2061268447.py:104: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  query_result = pd.read_sql(f\"SHOW indexes FROM {table} WHERE key_name LIKE 'index_%'\", database)\n",
      "/var/folders/k2/zns3zzl52ngb09pgfbbnb3pm0000gn/T/ipykernel_23784/2061268447.py:104: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  query_result = pd.read_sql(f\"SHOW indexes FROM {table} WHERE key_name LIKE 'index_%'\", database)\n"
     ]
    }
   ],
   "source": [
    "table_index_info_noopt, table_index_info_allopt = get_table_index_info_extremes(database)\n",
    "table_index_info_humopt_inverse = get_table_index_info_inverse(table_index_info_humopt, table_index_info_noopt)\n",
    "table_index_info_greedyopt_inverse = get_table_index_info_inverse(table_index_info_greedyopt, table_index_info_noopt)\n",
    "table_index_info_mlopt_inverse = get_table_index_info_inverse(table_index_info_mlopt, table_index_info_noopt)\n",
    "table_index_info_mlopt_25_inverse = get_table_index_info_inverse(table_index_info_mlopt_25, table_index_info_noopt)\n",
    "table_index_info_mlopt_50_inverse = get_table_index_info_inverse(table_index_info_mlopt_50, table_index_info_noopt)\n",
    "table_index_info_mlopt_75_inverse = get_table_index_info_inverse(table_index_info_mlopt_75, table_index_info_noopt)\n",
    "table_index_info_mlopt_100_inverse = get_table_index_info_inverse(table_index_info_mlopt_100, table_index_info_noopt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The cost for noopt was 0\n",
      "The cost for humopt was 675.8\n",
      "The cost for greedyopt was 245.0\n",
      "The cost for mlopt was 268.03\n",
      "The cost for allopt was 2758.23\n",
      "The cost for mlopt_25 was 220.89\n",
      "The cost for mlopt_50 was 263.69\n",
      "The cost for mlopt_75 was 185.56\n",
      "The cost for mlopt_100 was 201.89\n"
     ]
    }
   ],
   "source": [
    "print(f\"The cost for noopt was {0}\")\n",
    "print(f\"The cost for humopt was {get_index_cost(table_index_info_humopt)}\")\n",
    "print(f\"The cost for greedyopt was {get_index_cost(table_index_info_greedyopt)}\")\n",
    "print(f\"The cost for mlopt was {get_index_cost(table_index_info_mlopt)}\")\n",
    "print(f\"The cost for allopt was {get_index_cost(table_index_info_allopt)}\")\n",
    "print(f\"The cost for mlopt_25 was {get_index_cost(table_index_info_mlopt_25)}\")\n",
    "print(f\"The cost for mlopt_50 was {get_index_cost(table_index_info_mlopt_50)}\")\n",
    "print(f\"The cost for mlopt_75 was {get_index_cost(table_index_info_mlopt_75)}\")\n",
    "print(f\"The cost for mlopt_100 was {get_index_cost(table_index_info_mlopt_100)}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_query_13 = True\n",
    "query_list = [13] if test_query_13 else range(len(queries))\n",
    "models = [\"noopt\", \"humopt\", \"mlopt\", \"mlopt_25\", \"mlopt_50\", \"mlopt_75\", \"mlopt_100\"] if test_query_13 else [\"noopt\", \"greedyopt\", \"allopt\", \"humopt\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 0:   0%|          | 0/7 [00:00<?, ?it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 noopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 0:  14%|█▍        | 1/7 [00:04<00:25,  4.19s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_25\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 0:  29%|██▊       | 2/7 [00:05<00:11,  2.35s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_75\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 0:  43%|████▎     | 3/7 [00:06<00:07,  1.77s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 humopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 0:  57%|█████▋    | 4/7 [00:07<00:04,  1.51s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_50\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 0:  71%|███████▏  | 5/7 [00:08<00:02,  1.36s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 0:  86%|████████▌ | 6/7 [00:12<00:02,  2.17s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_100\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 0: 100%|██████████| 7/7 [00:13<00:00,  1.91s/it]\n",
      "Query Test Iteration for 1:   0%|          | 0/7 [00:00<?, ?it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_50\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 1:  14%|█▍        | 1/7 [00:01<00:07,  1.20s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_100\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 1:  29%|██▊       | 2/7 [00:02<00:05,  1.17s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 humopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 1:  43%|████▎     | 3/7 [00:03<00:04,  1.18s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 noopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 1:  57%|█████▋    | 4/7 [00:07<00:06,  2.29s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_75\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 1:  71%|███████▏  | 5/7 [00:08<00:03,  1.87s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_25\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 1:  86%|████████▌ | 6/7 [00:09<00:01,  1.62s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 1: 100%|██████████| 7/7 [00:13<00:00,  1.94s/it]\n",
      "Query Test Iteration for 2:   0%|          | 0/7 [00:00<?, ?it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 noopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 2:  14%|█▍        | 1/7 [00:03<00:22,  3.77s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_25\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 2:  29%|██▊       | 2/7 [00:04<00:10,  2.19s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_75\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 2:  43%|████▎     | 3/7 [00:05<00:06,  1.70s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 2:  57%|█████▋    | 4/7 [00:09<00:07,  2.51s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_50\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 2:  71%|███████▏  | 5/7 [00:10<00:04,  2.00s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 humopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 2:  86%|████████▌ | 6/7 [00:11<00:01,  1.69s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_100\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 2: 100%|██████████| 7/7 [00:13<00:00,  1.87s/it]\n",
      "Query Test Iteration for 3:   0%|          | 0/7 [00:00<?, ?it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 noopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 3:  14%|█▍        | 1/7 [00:03<00:22,  3.82s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_25\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 3:  29%|██▊       | 2/7 [00:04<00:11,  2.24s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_100\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 3:  43%|████▎     | 3/7 [00:06<00:06,  1.75s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 3:  57%|█████▋    | 4/7 [00:09<00:07,  2.55s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_50\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 3:  71%|███████▏  | 5/7 [00:11<00:04,  2.04s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 humopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 3:  86%|████████▌ | 6/7 [00:12<00:01,  1.74s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_75\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 3: 100%|██████████| 7/7 [00:13<00:00,  1.90s/it]\n",
      "Query Test Iteration for 4:   0%|          | 0/7 [00:00<?, ?it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 4:  14%|█▍        | 1/7 [00:03<00:22,  3.80s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_75\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 4:  29%|██▊       | 2/7 [00:04<00:11,  2.23s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_25\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 4:  43%|████▎     | 3/7 [00:06<00:06,  1.73s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_100\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 4:  57%|█████▋    | 4/7 [00:07<00:04,  1.53s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 humopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 4:  71%|███████▏  | 5/7 [00:08<00:02,  1.40s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_50\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 4:  86%|████████▌ | 6/7 [00:09<00:01,  1.31s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 noopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 4: 100%|██████████| 7/7 [00:13<00:00,  1.93s/it]\n",
      "Query Test Iteration for 5:   0%|          | 0/7 [00:00<?, ?it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_25\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 5:  14%|█▍        | 1/7 [00:01<00:06,  1.15s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 noopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 5:  29%|██▊       | 2/7 [00:05<00:13,  2.74s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_75\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 5:  43%|████▎     | 3/7 [00:06<00:08,  2.02s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_100\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 5:  57%|█████▋    | 4/7 [00:07<00:05,  1.69s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 humopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 5:  71%|███████▏  | 5/7 [00:08<00:03,  1.53s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 5:  86%|████████▌ | 6/7 [00:12<00:02,  2.45s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_50\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 5: 100%|██████████| 7/7 [00:14<00:00,  2.01s/it]\n",
      "Query Test Iteration for 6:   0%|          | 0/7 [00:00<?, ?it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 humopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 6:  14%|█▍        | 1/7 [00:01<00:07,  1.33s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_50\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 6:  29%|██▊       | 2/7 [00:02<00:06,  1.24s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_75\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 6:  43%|████▎     | 3/7 [00:03<00:05,  1.27s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_100\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 6:  57%|█████▋    | 4/7 [00:05<00:03,  1.26s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 6:  71%|███████▏  | 5/7 [00:08<00:04,  2.19s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_25\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 6:  86%|████████▌ | 6/7 [00:10<00:01,  1.84s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 noopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 6: 100%|██████████| 7/7 [00:13<00:00,  2.00s/it]\n",
      "Query Test Iteration for 7:   0%|          | 0/7 [00:00<?, ?it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 humopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 7:  14%|█▍        | 1/7 [00:01<00:07,  1.21s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 noopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 7:  29%|██▊       | 2/7 [00:04<00:13,  2.71s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_50\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 7:  43%|████▎     | 3/7 [00:06<00:08,  2.03s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_100\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 7:  57%|█████▋    | 4/7 [00:07<00:05,  1.68s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_75\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 7:  71%|███████▏  | 5/7 [00:08<00:02,  1.49s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 7:  86%|████████▌ | 6/7 [00:12<00:02,  2.31s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_25\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 7: 100%|██████████| 7/7 [00:13<00:00,  1.95s/it]\n",
      "Query Test Iteration for 8:   0%|          | 0/7 [00:00<?, ?it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 humopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 8:  14%|█▍        | 1/7 [00:01<00:06,  1.17s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_75\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 8:  29%|██▊       | 2/7 [00:02<00:05,  1.19s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_25\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 8:  43%|████▎     | 3/7 [00:03<00:04,  1.22s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_100\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 8:  57%|█████▋    | 4/7 [00:04<00:03,  1.25s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 noopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 8:  71%|███████▏  | 5/7 [00:08<00:04,  2.19s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 8:  86%|████████▌ | 6/7 [00:12<00:02,  2.73s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_50\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 8: 100%|██████████| 7/7 [00:13<00:00,  1.98s/it]\n",
      "Query Test Iteration for 9:   0%|          | 0/7 [00:00<?, ?it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_75\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 9:  14%|█▍        | 1/7 [00:01<00:07,  1.23s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 9:  29%|██▊       | 2/7 [00:05<00:13,  2.78s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_50\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 9:  43%|████▎     | 3/7 [00:06<00:08,  2.06s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_25\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 9:  57%|█████▋    | 4/7 [00:07<00:05,  1.70s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 noopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 9:  71%|███████▏  | 5/7 [00:11<00:05,  2.55s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 mlopt_100\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 9:  86%|████████▌ | 6/7 [00:12<00:02,  2.09s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13 humopt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Query Test Iteration for 9: 100%|██████████| 7/7 [00:13<00:00,  1.98s/it]\n"
     ]
    }
   ],
   "source": [
    "data = list()\n",
    "for trial_id in range(10):\n",
    "    tests = list()\n",
    "    for query_id in query_list:\n",
    "        for table_index_info_type in models:\n",
    "            tests.append({\"query_id\": query_id, \"index_type\": table_index_info_type})\n",
    "    random.shuffle(tests)\n",
    "    for test in tqdm(tests, desc=f\"Query Test Iteration for {trial_id}\"):\n",
    "        query_id = test[\"query_id\"]\n",
    "        table_index_info_type = test[\"index_type\"]\n",
    "        print(query_id, table_index_info_type)\n",
    "        if table_index_info_type == \"noopt\":\n",
    "            table_index_info = table_index_info_noopt\n",
    "        elif table_index_info_type == \"humopt\":\n",
    "            table_index_info = table_index_info_humopt_inverse\n",
    "        elif table_index_info_type == \"allopt\":\n",
    "            table_index_info = dict()\n",
    "        elif table_index_info_type == \"mlopt\":\n",
    "            table_index_info = table_index_info_mlopt_inverse\n",
    "        elif table_index_info_type == \"greedyopt\":\n",
    "            table_index_info = table_index_info_greedyopt_inverse\n",
    "        elif table_index_info_type == \"mlopt_25\":\n",
    "            table_index_info = table_index_info_mlopt_25_inverse\n",
    "        elif table_index_info_type == \"mlopt_50\":\n",
    "            table_index_info = table_index_info_mlopt_50_inverse\n",
    "        elif table_index_info_type == \"mlopt_75\":\n",
    "            table_index_info = table_index_info_mlopt_75_inverse\n",
    "        elif table_index_info_type == \"mlopt_100\":\n",
    "            table_index_info = table_index_info_mlopt_100_inverse\n",
    "\n",
    "        \n",
    "        query_time = get_query_cost_actual(queries[query_id], table_index_info, table_index_info_type == \"allopt\")\n",
    "        # print(f\"The query time for {table_index_info_type} and query {query_id} is {query_time}\")\n",
    "        data.append({\"trial_id\": trial_id, \"query_id\": query_id, \"query_time\": query_time, \"index_type\":  table_index_info_type})    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataframe = pd.DataFrame(data)\n",
    "\n",
    "query_performance = (\n",
    "    dataframe\n",
    "    .groupby([\"query_id\", \"index_type\"], as_index=False)\n",
    "    .agg(\n",
    "        min_time = pd.NamedAgg(column=\"query_time\", aggfunc=\"min\"),\n",
    "        median_time = pd.NamedAgg(column= \"query_time\", aggfunc=\"median\"),  \n",
    "        mean_time = pd.NamedAgg(column= \"query_time\", aggfunc=\"mean\"),    \n",
    "        sd_time = pd.NamedAgg(column= \"query_time\", aggfunc=std),    \n",
    "        max_time = pd.NamedAgg(column=\"query_time\", aggfunc=\"max\"),       \n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "index_performance = (\n",
    "    query_performance\n",
    "    .groupby(\"index_type\", as_index=False)\n",
    "    .agg(\n",
    "        mean_time = pd.NamedAgg(column=\"mean_time\", aggfunc=\"sum\"),\n",
    "        sd_time = pd.NamedAgg(column=\"sd_time\", aggfunc=average_std)\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option(\"display.max_rows\", 120)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index_type</th>\n",
       "      <th>mean_time</th>\n",
       "      <th>sd_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>humopt</td>\n",
       "      <td>1175.0</td>\n",
       "      <td>202.859557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mlopt</td>\n",
       "      <td>3841.7</td>\n",
       "      <td>441.846240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>mlopt_100</td>\n",
       "      <td>1178.3</td>\n",
       "      <td>151.241859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>mlopt_25</td>\n",
       "      <td>1143.4</td>\n",
       "      <td>192.982901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>mlopt_50</td>\n",
       "      <td>1168.0</td>\n",
       "      <td>176.470961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>mlopt_75</td>\n",
       "      <td>1150.8</td>\n",
       "      <td>185.799892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>noopt</td>\n",
       "      <td>3903.0</td>\n",
       "      <td>395.026581</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  index_type  mean_time     sd_time\n",
       "0     humopt     1175.0  202.859557\n",
       "1      mlopt     3841.7  441.846240\n",
       "2  mlopt_100     1178.3  151.241859\n",
       "3   mlopt_25     1143.4  192.982901\n",
       "4   mlopt_50     1168.0  176.470961\n",
       "5   mlopt_75     1150.8  185.799892\n",
       "6      noopt     3903.0  395.026581"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>query_id</th>\n",
       "      <th>index_type</th>\n",
       "      <th>min_time</th>\n",
       "      <th>median_time</th>\n",
       "      <th>mean_time</th>\n",
       "      <th>sd_time</th>\n",
       "      <th>max_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "      <td>humopt</td>\n",
       "      <td>1085.0</td>\n",
       "      <td>1161.5</td>\n",
       "      <td>1175.0</td>\n",
       "      <td>202.859557</td>\n",
       "      <td>1324.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13</td>\n",
       "      <td>mlopt</td>\n",
       "      <td>3751.0</td>\n",
       "      <td>3784.0</td>\n",
       "      <td>3841.7</td>\n",
       "      <td>441.846240</td>\n",
       "      <td>4240.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>13</td>\n",
       "      <td>mlopt_100</td>\n",
       "      <td>1074.0</td>\n",
       "      <td>1189.0</td>\n",
       "      <td>1178.3</td>\n",
       "      <td>151.241859</td>\n",
       "      <td>1237.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13</td>\n",
       "      <td>mlopt_25</td>\n",
       "      <td>1049.0</td>\n",
       "      <td>1137.0</td>\n",
       "      <td>1143.4</td>\n",
       "      <td>192.982901</td>\n",
       "      <td>1273.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>13</td>\n",
       "      <td>mlopt_50</td>\n",
       "      <td>1082.0</td>\n",
       "      <td>1177.0</td>\n",
       "      <td>1168.0</td>\n",
       "      <td>176.470961</td>\n",
       "      <td>1272.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>13</td>\n",
       "      <td>mlopt_75</td>\n",
       "      <td>1074.0</td>\n",
       "      <td>1133.0</td>\n",
       "      <td>1150.8</td>\n",
       "      <td>185.799892</td>\n",
       "      <td>1279.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>13</td>\n",
       "      <td>noopt</td>\n",
       "      <td>3748.0</td>\n",
       "      <td>3874.5</td>\n",
       "      <td>3903.0</td>\n",
       "      <td>395.026581</td>\n",
       "      <td>4177.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   query_id index_type  min_time  median_time  mean_time     sd_time  max_time\n",
       "0        13     humopt    1085.0       1161.5     1175.0  202.859557    1324.0\n",
       "1        13      mlopt    3751.0       3784.0     3841.7  441.846240    4240.0\n",
       "2        13  mlopt_100    1074.0       1189.0     1178.3  151.241859    1237.0\n",
       "3        13   mlopt_25    1049.0       1137.0     1143.4  192.982901    1273.0\n",
       "4        13   mlopt_50    1082.0       1177.0     1168.0  176.470961    1272.0\n",
       "5        13   mlopt_75    1074.0       1133.0     1150.8  185.799892    1279.0\n",
       "6        13      noopt    3748.0       3874.5     3903.0  395.026581    4177.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(index_performance)\n",
    "display(query_performance)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivoted_data = query_performance.pivot(index=\"query_id\", columns=\"index_type\", values=[\"mean_time\", \"sd_time\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "# dataframe.to_csv(\"skew_results/base_test_data.csv\", index=False)\n",
    "# query_performance.to_csv(\"skew_results/query_performance.csv\", index=False)\n",
    "# index_performance.to_csv(\"skew_results/index_performance.csv\", index=False)\n",
    "# pivoted_data.to_csv(\"results/pivoted_performance.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"7\" halign=\"left\">mean_time</th>\n",
       "      <th colspan=\"7\" halign=\"left\">sd_time</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index_type</th>\n",
       "      <th>humopt</th>\n",
       "      <th>mlopt</th>\n",
       "      <th>mlopt_100</th>\n",
       "      <th>mlopt_25</th>\n",
       "      <th>mlopt_50</th>\n",
       "      <th>mlopt_75</th>\n",
       "      <th>noopt</th>\n",
       "      <th>humopt</th>\n",
       "      <th>mlopt</th>\n",
       "      <th>mlopt_100</th>\n",
       "      <th>mlopt_25</th>\n",
       "      <th>mlopt_50</th>\n",
       "      <th>mlopt_75</th>\n",
       "      <th>noopt</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>query_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1175.0</td>\n",
       "      <td>3841.7</td>\n",
       "      <td>1178.3</td>\n",
       "      <td>1143.4</td>\n",
       "      <td>1168.0</td>\n",
       "      <td>1150.8</td>\n",
       "      <td>3903.0</td>\n",
       "      <td>202.859557</td>\n",
       "      <td>441.84624</td>\n",
       "      <td>151.241859</td>\n",
       "      <td>192.982901</td>\n",
       "      <td>176.470961</td>\n",
       "      <td>185.799892</td>\n",
       "      <td>395.026581</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           mean_time                                                       \\\n",
       "index_type    humopt   mlopt mlopt_100 mlopt_25 mlopt_50 mlopt_75   noopt   \n",
       "query_id                                                                    \n",
       "13            1175.0  3841.7    1178.3   1143.4   1168.0   1150.8  3903.0   \n",
       "\n",
       "               sd_time                                                 \\\n",
       "index_type      humopt      mlopt   mlopt_100    mlopt_25    mlopt_50   \n",
       "query_id                                                                \n",
       "13          202.859557  441.84624  151.241859  192.982901  176.470961   \n",
       "\n",
       "                                    \n",
       "index_type    mlopt_75       noopt  \n",
       "query_id                            \n",
       "13          185.799892  395.026581  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_performance.pivot(index=\"query_id\", columns=\"index_type\", values=[\"mean_time\", \"sd_time\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Define Queries DBA Can Look At"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "queries_length = [len(\"\".join(query.split())) for query in queries]\n",
    "candidate_queries = sorted(range(len(queries_length)), key=lambda sub: queries_length[sub])[:5]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[5, 15, 12, 13, 3]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "candidate_queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\n",
      "\n",
      "select\n",
      "\tsum(l_extendedprice * l_discount) as revenue\n",
      "from\n",
      "\tlineitem\n",
      "where\n",
      "\tl_shipdate >= date '1997-01-01'\n",
      "\tand l_shipdate < date '1997-01-01' + interval '1' year\n",
      "\tand l_discount between 0.06 - 0.01 and 0.06 + 0.01\n",
      "\tand l_quantity < 25\n",
      "\n",
      "\n",
      "\n",
      "\n",
      "select\n",
      "\tsum(l_extendedprice) / 7.0 as avg_yearly\n",
      "from\n",
      "\tlineitem,\n",
      "\tpart\n",
      "where\n",
      "\tp_partkey = l_partkey\n",
      "\tand p_brand = 'Brand#25'\n",
      "\tand p_container = 'WRAP BAG'\n",
      "\tand l_quantity < (\n",
      "\t\tselect\n",
      "\t\t\t0.2 * avg(l_quantity)\n",
      "\t\tfrom\n",
      "\t\t\tlineitem\n",
      "\t\twhere\n",
      "\t\t\tl_partkey = p_partkey\n",
      "\t)\n",
      "\n",
      "\n",
      "\n",
      "\n",
      "select\n",
      "\tc_count,\n",
      "\tcount(*) as custdist\n",
      "from\n",
      "\t(\n",
      "\t\tselect\n",
      "\t\t\tc_custkey,\n",
      "\t\t\tcount(o_orderkey)\n",
      "\t\tfrom\n",
      "\t\t\tcustomer left outer join orders on\n",
      "\t\t\t\tc_custkey = o_custkey\n",
      "\t\t\t\tand o_comment not like '%unusual%deposits%'\n",
      "\t\tgroup by\n",
      "\t\t\tc_custkey\n",
      "\t) as c_orders (c_custkey, c_count)\n",
      "group by\n",
      "\tc_count\n",
      "order by\n",
      "\tcustdist desc,\n",
      "\tc_count desc\n",
      "\n",
      "\n",
      "\n",
      "\n",
      "select\n",
      "\t100.00 * sum(case\n",
      "\t\twhen p_type like 'PROMO%'\n",
      "\t\t\tthen l_extendedprice * (1 - l_discount)\n",
      "\t\telse 0\n",
      "\tend) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue\n",
      "from\n",
      "\tlineitem,\n",
      "\tpart\n",
      "where\n",
      "\tl_partkey = p_partkey\n",
      "\tand l_shipdate >= date '1997-07-01'\n",
      "\tand l_shipdate < date '1997-07-01' + interval '1' month\n",
      "\n",
      "\n",
      "\n",
      "\n",
      "select\n",
      "\to_orderpriority,\n",
      "\tcount(*) as order_count\n",
      "from\n",
      "\torders\n",
      "where\n",
      "\to_orderdate >= date '1994-05-01'\n",
      "\tand o_orderdate < date '1994-05-01' + interval '3' month\n",
      "\tand exists (\n",
      "\t\tselect\n",
      "\t\t\t*\n",
      "\t\tfrom\n",
      "\t\t\tlineitem\n",
      "\t\twhere\n",
      "\t\t\tl_orderkey = o_orderkey\n",
      "\t\t\tand l_commitdate < l_receiptdate\n",
      "\t)\n",
      "group by\n",
      "\to_orderpriority\n",
      "order by\n",
      "\to_orderpriority\n"
     ]
    }
   ],
   "source": [
    "for query in candidate_queries:\n",
    "    print(queries)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "myenv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}