1_production_databases/08_debugging_queries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Optimizing Postgres Databases: Debugging Postgres Queries\n",
    "#### These are exercises done as part of <a href = \"www.dataquest.io\"> DataQuest</a>'s Data Engineer Path\n",
    "This is not replicated for commercial use; strictly personal development.<br>\n",
    "All exercises are (c) DataQuest, with slight modifications so they use my PostGres server on my localhost"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    ">We will be building on our understanding of the tables and we will learn how to debug Postgres queries. Using internal tables and Postgres sepecific commands, we will analyze how the Postgres engine runs analysis queries. Then, we will use that knowledge to determine what steps we need to take to make them more efficient and faster to run.\n",
    ">\n",
    "DataQuest"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Debugging Postgres Queries\n",
    "<b>1.  </b>Instructions:\n",
    "- Use the provided `cur` object.\n",
    "- Run the `EXPLAIN` command for a `SELECT` all query on the `vbstatic` table.\n",
    "- Call `.fetchall()` and pretty print the output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Seq Scan on vbstatic  (cost=0.00..7212.73 rows=273273 width=100)',)]\n"
     ]
    }
   ],
   "source": [
    "import psycopg2\n",
    "import pprint as pp\n",
    "\n",
    "conn = psycopg2.connect(dbname=\"valenbisi2018\", user=\"nmolivo\")\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute(\"EXPLAIN SELECT * FROM vbstatic\")\n",
    "pp.pprint(cur.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Let's describe the path a query takes when you call `cur.execute()`.\n",
    ">\n",
    ">Path of a query:\n",
    ">\n",
    ">1. The query is parsed for correct syntax. If there are any errors, the query does not execute and you receive an error message. If error-free, then the query is transformed into a query tree.\n",
    ">\n",
    ">2. A rewrite system takes the query tree and checks against the system catalog internal tables for any special rules. Then, if there are any rules, it rewrites them into the query tree.\n",
    ">\n",
    ">3. The rewritten query tree is then processed by the planner/optimizer which creates a query plan to send to the executor. The planner ensures that this is the fastest possible route for query execution.\n",
    "\n",
    ">4. The executor takes in the query plan, runs each step, then returns back any rows it found.\n",
    ">\n",
    "><img src = \"https://github.com/nmolivo/dataquest_eng/blob/master/images/007_sqlpath.png?raw=true\"></img><br>\n",
    ">\n",
    ">When we run the EXPLAIN command, we are examining the query at the third step in its path. In this step, the planner (or optimizer) is responsible for taking the written query and finding the fastest and most efficient way of returning the results.\n",
    ">\n",
    ">DataQuest"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>2.  </b>Instructions:\n",
    "- Use the provided `cur` object.\n",
    "- Run the `EXPLAIN` command on a query that returns a `COUNT` of rows greater than the year 2012-01-01 for `homeless_by_coc`.\n",
    "- Call `.fetchall()` and <a href=\"https://docs.python.org/3/library/pprint.html\">pretty print</a> the output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Finalize Aggregate  (cost=7139.46..7139.47 rows=1 width=8)',),\n",
      " ('  ->  Gather  (cost=7139.25..7139.46 rows=2 width=8)',),\n",
      " ('        Workers Planned: 2',),\n",
      " ('        ->  Partial Aggregate  (cost=6139.25..6139.26 rows=1 width=8)',),\n",
      " ('              ->  Parallel Seq Scan on vbstatic  (cost=0.00..5903.30 '\n",
      "  'rows=94380 width=0)',),\n",
      " (\"                    Filter: (update > '2018-02-22 00:00:00'::timestamp \"\n",
      "  'without time zone)',)]\n"
     ]
    }
   ],
   "source": [
    "conn = psycopg2.connect(dbname=\"valenbisi2018\", user=\"nmolivo\")\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute(\"EXPLAIN SELECT COUNT(*) from vbstatic WHERE update > '2018-02-22'\")\n",
    "pp.pprint(cur.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The executor will start by running a sequential scan on the table, filter by the year value, and then run the aggregator on those returned results. The plan of execution closely resembles a tree of commands – starting from the bottom and working its way up – but it is not clearly shown by this output format.\n",
    ">\n",
    ">DataQuest"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>3.  </b>Instructions:\n",
    "- Use the provided `cur` object.\n",
    "- Run the `EXPLAIN` command on a query that returns a `COUNT` of rows greater than the year 2012-01-01 for `homeless_by_coc`.\n",
    "    - Format the ouptut with the `json` type.\n",
    "- Call `.fetchall()` and pretty print the output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[([{'Plan': {'Node Type': 'Aggregate',\n",
      "             'Parallel Aware': False,\n",
      "             'Partial Mode': 'Finalize',\n",
      "             'Plan Rows': 1,\n",
      "             'Plan Width': 8,\n",
      "             'Plans': [{'Node Type': 'Gather',\n",
      "                        'Parallel Aware': False,\n",
      "                        'Parent Relationship': 'Outer',\n",
      "                        'Plan Rows': 2,\n",
      "                        'Plan Width': 8,\n",
      "                        'Plans': [{'Node Type': 'Aggregate',\n",
      "                                   'Parallel Aware': False,\n",
      "                                   'Parent Relationship': 'Outer',\n",
      "                                   'Partial Mode': 'Partial',\n",
      "                                   'Plan Rows': 1,\n",
      "                                   'Plan Width': 8,\n",
      "                                   'Plans': [{'Alias': 'vbstatic',\n",
      "                                              'Filter': \"(update > '2018-02-22 \"\n",
      "                                                        \"00:00:00'::timestamp \"\n",
      "                                                        'without time zone)',\n",
      "                                              'Node Type': 'Seq Scan',\n",
      "                                              'Parallel Aware': True,\n",
      "                                              'Parent Relationship': 'Outer',\n",
      "                                              'Plan Rows': 94380,\n",
      "                                              'Plan Width': 0,\n",
      "                                              'Relation Name': 'vbstatic',\n",
      "                                              'Startup Cost': 0.0,\n",
      "                                              'Total Cost': 5903.3}],\n",
      "                                   'Startup Cost': 6139.25,\n",
      "                                   'Strategy': 'Plain',\n",
      "                                   'Total Cost': 6139.26}],\n",
      "                        'Single Copy': False,\n",
      "                        'Startup Cost': 7139.25,\n",
      "                        'Total Cost': 7139.46,\n",
      "                        'Workers Planned': 2}],\n",
      "             'Startup Cost': 7139.46,\n",
      "             'Strategy': 'Plain',\n",
      "             'Total Cost': 7139.47}}],)]\n"
     ]
    }
   ],
   "source": [
    "conn = psycopg2.connect(dbname=\"valenbisi2018\", user=\"nmolivo\")\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute(\"EXPLAIN (format json) SELECT COUNT(*) from vbstatic WHERE update > '2018-02-22'\")\n",
    "pp.pprint(cur.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color = 'blue'><b>Q: What does 'cost' mean?</b><br>\n",
    "<b>A: </b>The cost of the operation as estimated by the optimizer's cost-based approach. For statements that use the rule-based approach, this column is null. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement, it is merely a weighted value used to compare costs of execution plans.  \n",
    "https://docs.oracle.com/cd/A58617_01/server.804/a58246/explan.htm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The `Startup Cost` represents the time it takes before a rows can be returned (something like sorting, or collecting the rows and aggregating them). <br>\n",
    ">`Total Cost` includes `Startup Cost` and is the total time it takes to run the node plan until completion. \n",
    ">\n",
    ">DataQuest"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>4.  </b>Instructions:\n",
    "- Use the provided `cur` object.\n",
    "- Practice running different `EXPLAIN` commands on any of the tables in the database. Here is one to try:\n",
    "    - `SELECT count FROM homeless_by_coc`\n",
    "    - `SELECT postal FROM state_info WHERE state='Alabama'`\n",
    "    - `SELECT state, SUM(count) FROM homeless_by_coc GROUP BY state HAVING SUM(count) > 100000 ORDER BY state`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<font color = 'blue'>Here I will query `vbstatic` instead of `homeless_by_coc`</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Finalize Aggregate  (cost=6903.51..6903.52 rows=1 width=8)',),\n",
      " ('  ->  Gather  (cost=6903.30..6903.51 rows=2 width=8)',),\n",
      " ('        Workers Planned: 2',),\n",
      " ('        ->  Partial Aggregate  (cost=5903.30..5903.31 rows=1 width=8)',),\n",
      " ('              ->  Parallel Seq Scan on vbstatic  (cost=0.00..5618.64 '\n",
      "  'rows=113864 width=0)',)]\n"
     ]
    }
   ],
   "source": [
    "conn = psycopg2.connect(dbname=\"valenbisi2018\", user=\"nmolivo\")\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute(\"EXPLAIN SELECT count(*) FROM vbstatic\")\n",
    "pp.pprint(cur.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Seq Scan on vbstatic  (cost=0.00..7895.91 rows=20696 width=4)',),\n",
      " ('  Filter: (free = 0)',)]\n"
     ]
    }
   ],
   "source": [
    "conn = psycopg2.connect(dbname=\"valenbisi2018\", user=\"nmolivo\")\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute(\"EXPLAIN SELECT available FROM vbstatic WHERE free=0\")\n",
    "pp.pprint(cur.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Finalize GroupAggregate  (cost=7199.17..7200.20 rows=41 width=12)',),\n",
      " ('  Group Key: available',),\n",
      " ('  ->  Sort  (cost=7199.17..7199.38 rows=82 width=12)',),\n",
      " ('        Sort Key: available',),\n",
      " ('        ->  Gather  (cost=7187.96..7196.57 rows=82 width=12)',),\n",
      " ('              Workers Planned: 2',),\n",
      " ('              ->  Partial HashAggregate  (cost=6187.96..6188.37 rows=41 '\n",
      "  'width=12)',),\n",
      " ('                    Group Key: available',),\n",
      " ('                    ->  Parallel Seq Scan on vbstatic  (cost=0.00..5618.64 '\n",
      "  'rows=113864 width=8)',)]\n"
     ]
    }
   ],
   "source": [
    "conn = psycopg2.connect(dbname=\"valenbisi2018\", user=\"nmolivo\")\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute(\"EXPLAIN SELECT available, SUM(free) FROM vbstatic GROUP BY available\")\n",
    "pp.pprint(cur.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Under the hood, `EXPLAIN` runs several queries on internal tables to give us the estimated data. One of these tables is the `pg_class` table where the estimated costs and rows are stored. This table only stores estimates of rows and costs (not actual values) so `EXPLAIN` can only give us approximate values for our queries.\n",
    ">\n",
    ">If we want to see, and force, actual runtime statistics of our queries, we need to use the `ANALYZE` option of the `EXPLAIN` query. With `ANALYZE`, the `EXPLAIN` command will execute our given query, wait for the results, then return the output with the recorded values.\n",
    ">\n",
    ">DataQuest & https://www.postgresql.org/docs/9.3/static/sql-explain.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>5.  </b>Instructions:\n",
    "- Use the provided `cur` object.\n",
    "- Run the `EXPLAIN` command on a query that returns a `COUNT` of rows greater than the year 2012-01-01 for `homeless_by_coc`.\n",
    "    - Add the `ANALYZE` option to the `EXPLAIN` command.\n",
    "    - Format the ouptut with the `json` type.\n",
    "    - Note that we are trying to add two options for `EXPLAIN`.\n",
    "- Call `.fetchall()` and pretty print the output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[([{'Execution Time': 72.429,\n",
      "    'Plan': {'Actual Loops': 1,\n",
      "             'Actual Rows': 1,\n",
      "             'Actual Startup Time': 71.276,\n",
      "             'Actual Total Time': 71.276,\n",
      "             'Node Type': 'Aggregate',\n",
      "             'Parallel Aware': False,\n",
      "             'Partial Mode': 'Finalize',\n",
      "             'Plan Rows': 1,\n",
      "             'Plan Width': 8,\n",
      "             'Plans': [{'Actual Loops': 1,\n",
      "                        'Actual Rows': 3,\n",
      "                        'Actual Startup Time': 71.261,\n",
      "                        'Actual Total Time': 71.266,\n",
      "                        'Node Type': 'Gather',\n",
      "                        'Parallel Aware': False,\n",
      "                        'Parent Relationship': 'Outer',\n",
      "                        'Plan Rows': 2,\n",
      "                        'Plan Width': 8,\n",
      "                        'Plans': [{'Actual Loops': 3,\n",
      "                                   'Actual Rows': 1,\n",
      "                                   'Actual Startup Time': 65.144,\n",
      "                                   'Actual Total Time': 65.144,\n",
      "                                   'Node Type': 'Aggregate',\n",
      "                                   'Parallel Aware': False,\n",
      "                                   'Parent Relationship': 'Outer',\n",
      "                                   'Partial Mode': 'Partial',\n",
      "                                   'Plan Rows': 1,\n",
      "                                   'Plan Width': 8,\n",
      "                                   'Plans': [{'Actual Loops': 3,\n",
      "                                              'Actual Rows': 75439,\n",
      "                                              'Actual Startup Time': 9.033,\n",
      "                                              'Actual Total Time': 50.905,\n",
      "                                              'Alias': 'vbstatic',\n",
      "                                              'Filter': \"(update > '2018-02-22 \"\n",
      "                                                        \"00:00:00'::timestamp \"\n",
      "                                                        'without time zone)',\n",
      "                                              'Node Type': 'Seq Scan',\n",
      "                                              'Parallel Aware': True,\n",
      "                                              'Parent Relationship': 'Outer',\n",
      "                                              'Plan Rows': 94380,\n",
      "                                              'Plan Width': 0,\n",
      "                                              'Relation Name': 'vbstatic',\n",
      "                                              'Rows Removed by Filter': 15652,\n",
      "                                              'Startup Cost': 0.0,\n",
      "                                              'Total Cost': 5903.3}],\n",
      "                                   'Startup Cost': 6139.25,\n",
      "                                   'Strategy': 'Plain',\n",
      "                                   'Total Cost': 6139.26}],\n",
      "                        'Single Copy': False,\n",
      "                        'Startup Cost': 7139.25,\n",
      "                        'Total Cost': 7139.46,\n",
      "                        'Workers Launched': 2,\n",
      "                        'Workers Planned': 2}],\n",
      "             'Startup Cost': 7139.46,\n",
      "             'Strategy': 'Plain',\n",
      "             'Total Cost': 7139.47},\n",
      "    'Planning Time': 1.111,\n",
      "    'Triggers': []}],)]\n"
     ]
    }
   ],
   "source": [
    "conn = psycopg2.connect(dbname=\"valenbisi2018\", user=\"nmolivo\")\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute(\"EXPLAIN (ANALYZE, format json) SELECT COUNT(*) from vbstatic WHERE update > '2018-02-22'\")\n",
    "pp.pprint(cur.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    ">Using the `ANALYZE` option, we have both estimates and actual times (in milliseconds) side by side. Furthermore, we are presented with the total execution time."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>6.  </b>Instructions:\n",
    "- Use the provided `cur` and conn objects.\n",
    "- Run the `EXPLAIN ANALYZE` command on a `DELETE` query that deletes all the rows in `state_household_incomes`.\n",
    "    - Format the ouptut with the `json` type.\n",
    "- Rollback the delete command.\n",
    "- Call `.fetchall()` and pretty print the output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[([{'Execution Time': 880.858,\n",
      "    'Plan': {'Actual Loops': 1,\n",
      "             'Actual Rows': 0,\n",
      "             'Actual Startup Time': 880.032,\n",
      "             'Actual Total Time': 880.032,\n",
      "             'Alias': 'vbstatic',\n",
      "             'Node Type': 'ModifyTable',\n",
      "             'Operation': 'Delete',\n",
      "             'Parallel Aware': False,\n",
      "             'Plan Rows': 273273,\n",
      "             'Plan Width': 6,\n",
      "             'Plans': [{'Actual Loops': 1,\n",
      "                        'Actual Rows': 273273,\n",
      "                        'Actual Startup Time': 0.097,\n",
      "                        'Actual Total Time': 101.738,\n",
      "                        'Alias': 'vbstatic',\n",
      "                        'Node Type': 'Seq Scan',\n",
      "                        'Parallel Aware': False,\n",
      "                        'Parent Relationship': 'Member',\n",
      "                        'Plan Rows': 273273,\n",
      "                        'Plan Width': 6,\n",
      "                        'Relation Name': 'vbstatic',\n",
      "                        'Startup Cost': 0.0,\n",
      "                        'Total Cost': 7212.73}],\n",
      "             'Relation Name': 'vbstatic',\n",
      "             'Startup Cost': 0.0,\n",
      "             'Total Cost': 7212.73},\n",
      "    'Planning Time': 1.739,\n",
      "    'Triggers': []}],)]\n"
     ]
    }
   ],
   "source": [
    "conn = psycopg2.connect(dbname=\"valenbisi2018\", user=\"nmolivo\")\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute(\"EXPLAIN (ANALYZE, FORMAT json) DELETE FROM vbstatic\")\n",
    "# Rollback the change.\n",
    "conn.rollback()\n",
    "pp.pprint(cur.fetchall())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>7.  </b>Instructions:\n",
    "- Use the provided `cur` object.\n",
    "- Run `EXPLAIN ANALYZE` on a select from `homeless_by_coc` and `state_info`:\n",
    "    - Select columns `state`, `coc_number`, and `coc_name` from `homeless_by_coc`.\n",
    "    - Select column name from `state_info`.\n",
    "    - Join on `homless_by_coc.state` and `state_info.postal`.\n",
    "    - Format the ouptut with the `json` type.\n",
    "- Call `fetchall()` and pretty print the output."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "```python\n",
    "conn = psycopg2.connect(dbname=\"dq\", user=\"hud_admin\", password=\"abc123\")\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute(\"EXPLAIN (ANALYZE, FORMAT json) SELECT hbc.state, hbc.coc_number, hbc.coc_name, si.name FROM homeless_by_coc as hbc, state_info as si WHERE hbc.state = si.postal\")\n",
    "pp.pprint(cur.fetchall())\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "><img src = 'https://github.com/nmolivo/dataquest_eng/blob/master/images/008_query_plan.png?raw=true'></img><br>\n",
    "> The output of the `EXPLAIN ANALYZE` command reveals the inefficiency of the join. In the list of plans, each node must first run a Seq Scan which is a loop through each of the tables. Before the join can occur, a loop is performed twice: once in `homeless_by_coc` and once in `state_info`.\n",
    ">\n",
    ">DataQuest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}