7_Query_Optimization/1_Explain_Intro.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fc6ee6c2",
   "metadata": {},
   "source": [
    "<a target=\"_blank\" href=\"https://colab.research.google.com/github/lukebarousse/Int_SQL_Data_Analytics_Course/blob/main/7_Query_Optimization/1_Explain_Intro.ipynb\">\n",
    "  <img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/>\n",
    "</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Explain & Optimization Intro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overview"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 🥅 Analysis Goals\n",
    "\n",
    "- **Plan Query Execution:** Use `EXPLAIN` to understand how PostgreSQL will execute the query, identifying potential inefficiencies like sequential scans or costly joins.  \n",
    "- **Measure Actual Query Performance:** Use `EXPLAIN ANALYZE` to execute the query while collecting real performance metrics, comparing estimated vs. actual execution times for optimization."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 📘 Concepts Covered\n",
    "\n",
    "- `EXPLAIN`\n",
    "- `EXPLAIN ANALYZE`\n",
    "\n",
    "[Source Documentation for Using Explain](https://www.postgresql.org/docs/17/using-explain.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Connecting to &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Connecting to 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import sys\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "\n",
    "# If running in Google Colab, install PostgreSQL and restore the database\n",
    "if 'google.colab' in sys.modules:\n",
    "    # Install PostgreSQL\n",
    "    !sudo apt-get install postgresql -qq > /dev/null 2>&1\n",
    "\n",
    "    # Start PostgreSQL service (suppress output)\n",
    "    !sudo service postgresql start > /dev/null 2>&1\n",
    "\n",
    "    # Set password for the 'postgres' user to avoid authentication errors (suppress output)\n",
    "    !sudo -u postgres psql -c \"ALTER USER postgres WITH PASSWORD 'password';\" > /dev/null 2>&1\n",
    "\n",
    "    # Create the 'colab_db' database (suppress output)\n",
    "    !sudo -u postgres psql -c \"CREATE DATABASE contoso_100k;\" > /dev/null 2>&1\n",
    "\n",
    "    # Download the PostgreSQL .sql dump\n",
    "    !wget -q -O contoso_100k.sql https://github.com/lukebarousse/Int_SQL_Data_Analytics_Course/releases/download/v.0.0.0/contoso_100k.sql\n",
    "\n",
    "    # Restore the dump file into the PostgreSQL database (suppress output)\n",
    "    !sudo -u postgres psql contoso_100k < contoso_100k.sql > /dev/null 2>&1\n",
    "\n",
    "    # Shift libraries from ipython-sql to jupysql\n",
    "    !pip uninstall -y ipython-sql > /dev/null 2>&1\n",
    "    !pip install jupysql > /dev/null 2>&1\n",
    "\n",
    "# Load the sql extension for SQL magic\n",
    "%load_ext sql\n",
    "\n",
    "# Connect to the PostgreSQL database\n",
    "%sql postgresql://postgres:password@localhost:5432/contoso_100k\n",
    "\n",
    "# Enable automatic conversion of SQL results to pandas DataFrames\n",
    "%config SqlMagic.autopandas = True\n",
    "\n",
    "# Disable named parameters for SQL magic\n",
    "%config SqlMagic.named_parameters = \"disabled\"\n",
    "\n",
    "# Display pandas number to two decimal places\n",
    "pd.options.display.float_format = '{:.2f}'.format"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "## EXPLAIN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 📝 Notes\n",
    "\n",
    "**`EXPLAIN`**  \n",
    "\n",
    "- **EXPLAIN**: Displays the execution plan of a SQL query, showing how PostgreSQL will execute it.\n",
    "\n",
    "- Syntax:  \n",
    "  ```sql\n",
    "  EXPLAIN \n",
    "  SELECT column \n",
    "  FROM table;\n",
    "  ```\n",
    "  \n",
    "**`EXPLAIN ANALYZE`**: \n",
    "- **EXPLAIN ANALYZE**: Executes the query and provides actual execution times, row estimates, and other runtime details.\n",
    "  ```sql\n",
    "  EXPLAIN ANALYZE \n",
    "  SELECT column \n",
    "  FROM table;\n",
    "\n",
    "  ```\n",
    "\n",
    "- Helps with query optimization by showing:\n",
    "  - Index usage\n",
    "  - Join methods (`Nested Loop`, `Hash Join`, `Merge Join`)\n",
    "  - Sequential vs. index scans\n",
    "  - Estimated vs. actual row counts\n",
    "\n",
    "- **Example Output** (simplified):\n",
    "  ```\n",
    "  Seq Scan on users  (cost=0.00..18.50 rows=850 width=64)\n",
    "  ```\n",
    "  - `Seq Scan`: PostgreSQL is doing a sequential scan (no index used).\n",
    "  - `cost`: An arbitrary unit for cost (...basically made up units specific to PostgreSQL)\n",
    "  - `rows`: Estimated number of rows.\n",
    "  - `width`: Estimated row size in bytes.\n",
    "\n",
    "- **Use Cases**:\n",
    "  - Debugging slow queries\n",
    "  - Checking if indexes are being used\n",
    "  - Understanding query performance bottlenecks\n",
    "\n",
    "### 🔑 Key Concepts\n",
    "- **📊 Business Terms**: \n",
    "  - Query Performance: Speed of data retrieval\n",
    "  - Execution Cost: Resources needed to run query\n",
    "  - Data Processing: How database handles requests\n",
    "- **💡 Why It Matters**: Optimizes complex customer revenue analysis\n",
    "    - Reduces query execution time for large customer datasets\n",
    "    - Lowers computational costs for frequent cohort analysis\n",
    "    - Enables faster business decision making\n",
    "    - Improves efficiency of revenue tracking systems\n",
    "\n",
    "### 📈 Analysis\n",
    "\n",
    "- Understand how PostgreSQL plans to execute the query without running it, helping identify potential inefficiencies like sequential scans or costly joins.\n",
    "- Execute the query while collecting actual performance metrics, allowing comparison between estimated and real execution times to optimize query performance.\n",
    "\n",
    "> **⚠️ Note**: For the queries below the explanation is focused on explaining the results of the `EXPLAIN` plan."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Quick Demo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### EXPLAIN \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">1 rows affected.</span>"
      ],
      "text/plain": [
       "1 rows affected."
      ]
     },
     "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 PLAN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Seq Scan on sales  (cost=0.00..4518.73 rows=19...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          QUERY PLAN\n",
       "0  Seq Scan on sales  (cost=0.00..4518.73 rows=19..."
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "EXPLAIN \n",
    "SELECT *\n",
    "FROM sales"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### EXPLAIN ANALYZE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">3 rows affected.</span>"
      ],
      "text/plain": [
       "3 rows affected."
      ]
     },
     "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 PLAN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Seq Scan on sales  (cost=0.00..4518.73 rows=19...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Planning Time: 0.108 ms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Execution Time: 24.002 ms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          QUERY PLAN\n",
       "0  Seq Scan on sales  (cost=0.00..4518.73 rows=19...\n",
       "1                            Planning Time: 0.108 ms\n",
       "2                          Execution Time: 24.002 ms"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "EXPLAIN ANALYZE\n",
    "SELECT *\n",
    "FROM sales"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What happens if we add a filter?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">5 rows affected.</span>"
      ],
      "text/plain": [
       "5 rows affected."
      ]
     },
     "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 PLAN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Seq Scan on sales  (cost=0.00..5018.41 rows=10...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Filter: (orderdate &gt;= '2024-01-01'::date)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Rows Removed by Filter: 189742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Planning Time: 0.159 ms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Execution Time: 20.901 ms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          QUERY PLAN\n",
       "0  Seq Scan on sales  (cost=0.00..5018.41 rows=10...\n",
       "1          Filter: (orderdate >= '2024-01-01'::date)\n",
       "2                     Rows Removed by Filter: 189742\n",
       "3                            Planning Time: 0.159 ms\n",
       "4                          Execution Time: 20.901 ms"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "EXPLAIN ANALYZE\n",
    "SELECT *\n",
    "FROM sales\n",
    "WHERE orderdate >= '2024-01-01'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Total Customer Revenue\n",
    "\n",
    "`EXPLAIN ANALYZE`\n",
    "\n",
    "1. Use `EXPLAIN ANALYZE` on a query to get the total net revenue by customer.\n",
    "    - `Seq scan on sales`: Get all the rows needed from the sales table.\n",
    "    - `HashAggregate`: Hash system to form aggregation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">6 rows affected.</span>"
      ],
      "text/plain": [
       "6 rows affected."
      ]
     },
     "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 PLAN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HashAggregate  (cost=7017.14..7387.38 rows=370...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Group Key: customerkey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Batches: 1  Memory Usage: 4881kB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-&gt;  Seq Scan on sales  (cost=0.00..4518.73 r...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Planning Time: 0.094 ms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Execution Time: 61.912 ms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          QUERY PLAN\n",
       "0  HashAggregate  (cost=7017.14..7387.38 rows=370...\n",
       "1                             Group Key: customerkey\n",
       "2                   Batches: 1  Memory Usage: 4881kB\n",
       "3    ->  Seq Scan on sales  (cost=0.00..4518.73 r...\n",
       "4                            Planning Time: 0.094 ms\n",
       "5                          Execution Time: 61.912 ms"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "EXPLAIN ANALYZE\n",
    "SELECT \n",
    "    customerkey,\n",
    "    SUM(quantity * netprice * exchangerate) AS net_revenue\n",
    "FROM sales\n",
    "GROUP BY customerkey"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Add a filter on `orderdate` to get all orders after 2024.\n",
    "    - `Seq scan on sales`: Get all the rows needed from the sales table.\n",
    "        - 🔔`Filter`: Filters by the dates\n",
    "    - `HashAggregate`: Hash system to form aggregation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">8 rows affected.</span>"
      ],
      "text/plain": [
       "8 rows affected."
      ]
     },
     "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 PLAN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HashAggregate  (cost=5143.69..5233.47 rows=897...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Group Key: customerkey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Batches: 1  Memory Usage: 913kB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-&gt;  Seq Scan on sales  (cost=0.00..5018.41 r...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Filter: (orderdate &gt;= '2024-01-01'::date)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Rows Removed by Filter: 189742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Planning Time: 0.209 ms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Execution Time: 27.828 ms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          QUERY PLAN\n",
       "0  HashAggregate  (cost=5143.69..5233.47 rows=897...\n",
       "1                             Group Key: customerkey\n",
       "2                    Batches: 1  Memory Usage: 913kB\n",
       "3    ->  Seq Scan on sales  (cost=0.00..5018.41 r...\n",
       "4          Filter: (orderdate >= '2024-01-01'::date)\n",
       "5                     Rows Removed by Filter: 189742\n",
       "6                            Planning Time: 0.209 ms\n",
       "7                          Execution Time: 27.828 ms"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "EXPLAIN ANALYZE\n",
    "SELECT \n",
    "    customerkey,\n",
    "    SUM(quantity * netprice * exchangerate) AS net_revenue\n",
    "FROM sales\n",
    "WHERE orderdate >= '2024-01-01'\n",
    "GROUP BY customerkey"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. Alternatively you can view the `EXPLAIN` using `dbeaver` by selecting `Explain Execution Plan`. Then select what you want else you want to include e.g. `ANALYZE`, `VERBOSE`, etc. For this you don't need the `EXPLAIN` keyword.\n",
    "\n",
    "    <img src=\"../Resources/images/7.1_explain_1.gif\" alt=\"View Explain using dbeaver\" style=\"width: 70%; height: auto;\">\n",
    "\n",
    "> **NOTE:** The results of this `EXPLAIN` are of another query, not the one shown in the video."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "## Optimization Techniques\n",
    "\n",
    "#### 🔰 Beginner (Fundamental Optimizations)\n",
    "- 🚫 Avoid `SELECT *`: Retrieve only necessary columns.\n",
    "- 📉 Use `LIMIT` for Large Datasets: Improve performance on large queries.\n",
    "- 🛠 Use `WHERE` Instead of `HAVING`: Filter before aggregation for efficiency.\n",
    "\n",
    "#### ⚡ Intermediate (Query Structure & Execution Plan Optimizations)\n",
    "- 📖 Use Query Execution Plans: Identify slow queries and optimize execution paths.\n",
    "- 📌 Minimize `GROUP BY` Usage: Avoid unnecessary aggregations.\n",
    "- 🔗 Reduce Joins When Possible: Optimize relationships to prevent expensive joins.\n",
    "- 📊 Optimize `ORDER BY`: Use indexed columns for sorting.\n",
    "\n",
    "#### 🚀 Advanced (Database-Level Optimizations)\n",
    "- 🧠 Use Proper Data Types: Ensure numeric vs. string-based filtering is efficient.\n",
    "- ⚡ Use Proper Indexing: Speed up queries with strategic indexes.\n",
    "- 🗃 Use Partitioning for Large Tables: Improve performance on large datasets.\n",
    "\n",
    "> NOTE: We'll cover intermediate techniques in the next lesson\n",
    "\n",
    "---\n",
    "\n",
    "## 🔰 Beginner Techniques\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 📉 Use `LIMIT` for Large Datasets\n",
    "- Improve performance on large queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">3 rows affected.</span>"
      ],
      "text/plain": [
       "3 rows affected."
      ]
     },
     "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 PLAN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Seq Scan on sales  (cost=0.00..4518.73 rows=19...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Planning Time: 0.046 ms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Execution Time: 10.028 ms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          QUERY PLAN\n",
       "0  Seq Scan on sales  (cost=0.00..4518.73 rows=19...\n",
       "1                            Planning Time: 0.046 ms\n",
       "2                          Execution Time: 10.028 ms"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "EXPLAIN ANALYZE\n",
    "SELECT\n",
    "    customerkey,\n",
    "    orderdate,\n",
    "    orderkey,\n",
    "    linenumber\n",
    "FROM sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">4 rows affected.</span>"
      ],
      "text/plain": [
       "4 rows affected."
      ]
     },
     "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 PLAN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Limit  (cost=0.00..0.23 rows=10 width=16) (act...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-&gt;  Seq Scan on sales  (cost=0.00..4518.73 r...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Planning Time: 0.030 ms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Execution Time: 0.010 ms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          QUERY PLAN\n",
       "0  Limit  (cost=0.00..0.23 rows=10 width=16) (act...\n",
       "1    ->  Seq Scan on sales  (cost=0.00..4518.73 r...\n",
       "2                            Planning Time: 0.030 ms\n",
       "3                           Execution Time: 0.010 ms"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "EXPLAIN ANALYZE\n",
    "SELECT\n",
    "    customerkey,\n",
    "    orderdate,\n",
    "    orderkey,\n",
    "    linenumber\n",
    "FROM sales\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 🚫 Avoid `SELECT *`\n",
    "- Retrieve only the necessary columns for a query.\n",
    "- Note some databases may be more efficient at retrieving  this negating gains"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">3 rows affected.</span>"
      ],
      "text/plain": [
       "3 rows affected."
      ]
     },
     "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 PLAN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Seq Scan on sales  (cost=0.00..4518.73 rows=19...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Planning Time: 0.089 ms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Execution Time: 7.336 ms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          QUERY PLAN\n",
       "0  Seq Scan on sales  (cost=0.00..4518.73 rows=19...\n",
       "1                            Planning Time: 0.089 ms\n",
       "2                           Execution Time: 7.336 ms"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "EXPLAIN ANALYZE\n",
    "SELECT *\n",
    "FROM sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">3 rows affected.</span>"
      ],
      "text/plain": [
       "3 rows affected."
      ]
     },
     "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 PLAN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Seq Scan on sales  (cost=0.00..4518.73 rows=19...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Planning Time: 0.019 ms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Execution Time: 9.481 ms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          QUERY PLAN\n",
       "0  Seq Scan on sales  (cost=0.00..4518.73 rows=19...\n",
       "1                            Planning Time: 0.019 ms\n",
       "2                           Execution Time: 9.481 ms"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "EXPLAIN ANALYZE\n",
    "SELECT\n",
    "    customerkey,\n",
    "    orderdate,\n",
    "    orderkey,\n",
    "    linenumber\n",
    "FROM sales"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> #### Why is this query slower?\n",
    "> PostgreSQL Uses a More Efficient Query Plan for `SELECT *`\n",
    "> - If all columns are needed frequently, PostgreSQL might use a more optimized data retrieval strategy for `SELECT *`, such as a sequential scan (if no indexes are used).\n",
    "> - If only some columns are selected, PostgreSQL might use an index scan or another method that requires additional lookups.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 🛠 Use `WHERE` Instead of `HAVING`\n",
    "- Filter before aggregation for efficiency."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">8 rows affected.</span>"
      ],
      "text/plain": [
       "8 rows affected."
      ]
     },
     "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 PLAN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HashAggregate  (cost=7516.82..7979.62 rows=123...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Group Key: customerkey, orderdate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Filter: (sum((((quantity)::double precision ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Batches: 5  Memory Usage: 8241kB  Disk Usage...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Rows Removed by Filter: 35607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-&gt;  Seq Scan on sales  (cost=0.00..4518.73 r...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Planning Time: 0.229 ms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Execution Time: 82.065 ms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          QUERY PLAN\n",
       "0  HashAggregate  (cost=7516.82..7979.62 rows=123...\n",
       "1                  Group Key: customerkey, orderdate\n",
       "2    Filter: (sum((((quantity)::double precision ...\n",
       "3    Batches: 5  Memory Usage: 8241kB  Disk Usage...\n",
       "4                      Rows Removed by Filter: 35607\n",
       "5    ->  Seq Scan on sales  (cost=0.00..4518.73 r...\n",
       "6                            Planning Time: 0.229 ms\n",
       "7                          Execution Time: 82.065 ms"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "EXPLAIN ANALYZE\n",
    "SELECT\n",
    "    customerkey,\n",
    "    orderdate,\n",
    "    SUM(quantity * netprice * exchangerate) AS net_revenue\n",
    "FROM sales\n",
    "GROUP BY\n",
    "    customerkey,\n",
    "    orderdate\n",
    "HAVING SUM(quantity * netprice * exchangerate) > 1000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;postgresql://postgres:***@localhost:5432/contoso_100k&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'postgresql://postgres:***@localhost:5432/contoso_100k'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">16 rows affected.</span>"
      ],
      "text/plain": [
       "16 rows affected."
      ]
     },
     "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 PLAN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Finalize GroupAggregate  (cost=4990.18..4993.9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Group Key: customerkey, orderdate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-&gt;  Gather Merge  (cost=4990.18..4993.40 row...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Workers Planned: 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Workers Launched: 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-&gt;  Partial GroupAggregate  (cost=3990...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Group Key: customerkey, orderdate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-&gt;  Sort  (cost=3990.17..3990.23...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Sort Key: customerkey, ord...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Sort Method: quicksort  Me...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Worker 0:  Sort Method: qu...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>-&gt;  Parallel Seq Scan on s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Filter: (customerkey...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Rows Removed by Filt...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Planning Time: 0.207 ms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Execution Time: 22.442 ms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           QUERY PLAN\n",
       "0   Finalize GroupAggregate  (cost=4990.18..4993.9...\n",
       "1                   Group Key: customerkey, orderdate\n",
       "2     ->  Gather Merge  (cost=4990.18..4993.40 row...\n",
       "3                                  Workers Planned: 1\n",
       "4                                 Workers Launched: 1\n",
       "5           ->  Partial GroupAggregate  (cost=3990...\n",
       "6                   Group Key: customerkey, orderdate\n",
       "7                 ->  Sort  (cost=3990.17..3990.23...\n",
       "8                       Sort Key: customerkey, ord...\n",
       "9                       Sort Method: quicksort  Me...\n",
       "10                      Worker 0:  Sort Method: qu...\n",
       "11                      ->  Parallel Seq Scan on s...\n",
       "12                            Filter: (customerkey...\n",
       "13                            Rows Removed by Filt...\n",
       "14                            Planning Time: 0.207 ms\n",
       "15                          Execution Time: 22.442 ms"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "EXPLAIN ANALYZE\n",
    "SELECT\n",
    "    customerkey,\n",
    "    orderdate,\n",
    "    SUM(quantity * netprice * exchangerate) AS net_revenue\n",
    "FROM sales\n",
    "WHERE customerkey < 1000\n",
    "GROUP BY\n",
    "    customerkey,\n",
    "    orderdate"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "sql_course",
   "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.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}