pg10_examples.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Setup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Postgres docker images"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9.6: Pulling from library/postgres\n",
      "Digest: sha256:2f2b1f4d9d83db7378584d7f41b15a49b2cea25956af67698f8ea80e3bdc28ba\n",
      "Status: Image is up to date for postgres:9.6\n",
      "2dc70f1982495634a4e24499bfe63c34c6ba3a8b40b6db5dfffb3fe7e625d213\n"
     ]
    }
   ],
   "source": [
    "!docker pull postgres:9.6\n",
    "!docker run -p 5430:5432 --name jupy-old-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres:9.6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10: Pulling from library/postgres\n",
      "Digest: sha256:95e54755cb3a59a56dc2d98d8e8a0b4436537185c7b240eb140069208ec35e22\n",
      "Status: Image is up to date for postgres:10\n",
      "dac7cbe6579a9a1a4386396ce3f23b59e73e443ff649124b52c32094399ae166\n"
     ]
    }
   ],
   "source": [
    "!docker pull postgres:10\n",
    "!docker run -p 5431:5432 --name jupy-new-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres:10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connection to database(s)\n",
    "\n",
    "### [jupyter sql magic](https://github.com/catherinedevlin/ipython-sql) used"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext sql\n",
    "connection96=\"postgresql+psycopg2://postgres:mysecretpassword@localhost:5430/postgres\"\n",
    "connection10=\"postgresql+psycopg2://postgres:mysecretpassword@localhost:5431/postgres\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_setting</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9.6.5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'9.6.5',)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection96\n",
    "select current_setting('server_version');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_setting</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10.0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'10.0',)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "select current_setting('server_version')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  1. Identity columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "CREATE TABLE foo (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, val1 INTEGER);\n",
    "INSERT INTO foo(val1) VALUES (1);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "ALTER TABLE foo ALTER COLUMN id RESTART WITH 1000;\n",
    "INSERT INTO foo(val1) VALUES (2);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "CREATE TABLE bar (LIKE foo INCLUDING ALL);\n",
    "INSERT INTO bar(val1) VALUES (3);\n",
    "INSERT INTO foo(val1) VALUES (4);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>val1</th>\n",
       "        <th>tbl</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>foo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1000</td>\n",
       "        <td>2</td>\n",
       "        <td>foo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>3</td>\n",
       "        <td>bar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1001</td>\n",
       "        <td>4</td>\n",
       "        <td>foo</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1, u'foo'), (1000, 2, u'foo'), (1, 3, u'bar'), (1001, 4, u'foo')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10 \n",
    "SELECT id, val1, 'foo' as tbl FROM foo\n",
    "UNION\n",
    "SELECT id, val1, 'bar' as tbl FROM bar\n",
    "ORDER BY val1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Meanwhile in pg 9.6...\n",
    "#### (Above steps were repeated using old syntax)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "Done.\n",
      "1 rows affected.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection96\n",
    "DROP TABLE IF EXISTS bar;\n",
    "DROP TABLE IF EXISTS foo;\n",
    "\n",
    "CREATE TABLE foo (id SERIAL PRIMARY KEY, val1 INTEGER);\n",
    "INSERT INTO foo(val1) VALUES (1);\n",
    "ALTER SEQUENCE foo_id_seq RESTART WITH 1000;\n",
    "INSERT INTO foo(val1) VALUES (2);\n",
    "CREATE TABLE bar (LIKE foo INCLUDING ALL);\n",
    "INSERT INTO bar(val1) VALUES (3);\n",
    "INSERT INTO foo(val1) VALUES (4);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>val1</th>\n",
       "        <th>tbl</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>foo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1000</td>\n",
       "        <td>2</td>\n",
       "        <td>foo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1001</td>\n",
       "        <td>3</td>\n",
       "        <td>bar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1002</td>\n",
       "        <td>4</td>\n",
       "        <td>foo</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1, u'foo'), (1000, 2, u'foo'), (1001, 3, u'bar'), (1002, 4, u'foo')]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection96 \n",
    "SELECT id, val1, 'foo' as tbl FROM foo\n",
    "UNION\n",
    "SELECT id, val1, 'bar' as tbl FROM bar\n",
    "ORDER BY val1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "ename": "IntegrityError",
     "evalue": "(psycopg2.IntegrityError) null value in column \"id\" violates not-null constraint\nDETAIL:  Failing row contains (null, 5).\n [SQL: 'INSERT INTO bar(val1) VALUES (5);']",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mIntegrityError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-23-1fea093d3da6>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mget_ipython\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun_cell_magic\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mu'sql'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34mu'$connection96'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34mu'DROP TABLE foo CASCADE;\\nINSERT INTO bar(val1) VALUES (5);'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/home/jakubwilkowski/projects/presentation/pg10/venv/local/lib/python2.7/site-packages/IPython/core/interactiveshell.pyc\u001b[0m in \u001b[0;36mrun_cell_magic\u001b[0;34m(self, magic_name, line, cell)\u001b[0m\n\u001b[1;32m   2115\u001b[0m             \u001b[0mmagic_arg_s\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mvar_expand\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mline\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstack_depth\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2116\u001b[0m             \u001b[0;32mwith\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbuiltin_trap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2117\u001b[0;31m                 \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmagic_arg_s\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcell\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2118\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2119\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<decorator-gen-124>\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n",
      "\u001b[0;32m/home/jakubwilkowski/projects/presentation/pg10/venv/local/lib/python2.7/site-packages/IPython/core/magic.pyc\u001b[0m in \u001b[0;36m<lambda>\u001b[0;34m(f, *a, **k)\u001b[0m\n\u001b[1;32m    186\u001b[0m     \u001b[0;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    187\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 188\u001b[0;31m         \u001b[0mcall\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    189\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    190\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m<decorator-gen-123>\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n",
      "\u001b[0;32m/home/jakubwilkowski/projects/presentation/pg10/venv/local/lib/python2.7/site-packages/IPython/core/magic.pyc\u001b[0m in \u001b[0;36m<lambda>\u001b[0;34m(f, *a, **k)\u001b[0m\n\u001b[1;32m    186\u001b[0m     \u001b[0;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    187\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 188\u001b[0;31m         \u001b[0mcall\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    189\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    190\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/jakubwilkowski/projects/presentation/pg10/venv/local/lib/python2.7/site-packages/sql/magic.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n\u001b[1;32m     85\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     86\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 87\u001b[0;31m             \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparsed\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'sql'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0muser_ns\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     88\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     89\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mresult\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0;34m~\u001b[0m\u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumn_local_vars\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/jakubwilkowski/projects/presentation/pg10/venv/local/lib/python2.7/site-packages/sql/run.pyc\u001b[0m in \u001b[0;36mrun\u001b[0;34m(conn, sql, config, user_namespace)\u001b[0m\n\u001b[1;32m    274\u001b[0m                 \u001b[0;32mraise\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"ipython_sql does not support transactions\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    275\u001b[0m             \u001b[0mtxt\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msqlalchemy\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 276\u001b[0;31m             \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtxt\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0muser_namespace\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    277\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    278\u001b[0m                 \u001b[0;31m# mssql has autocommit\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/jakubwilkowski/projects/presentation/pg10/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, object, *multiparams, **params)\u001b[0m\n\u001b[1;32m    943\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobject\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    944\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 945\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    946\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    947\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_function\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/jakubwilkowski/projects/presentation/pg10/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.pyc\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[0;34m(self, connection, multiparams, params)\u001b[0m\n\u001b[1;32m    261\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_on_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    262\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msupports_execution\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 263\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_clauseelement\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    264\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    265\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/jakubwilkowski/projects/presentation/pg10/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[0;34m(self, elem, multiparams, params)\u001b[0m\n\u001b[1;32m   1051\u001b[0m             \u001b[0mcompiled_sql\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1052\u001b[0m             \u001b[0mdistilled_params\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1053\u001b[0;31m             \u001b[0mcompiled_sql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdistilled_params\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1054\u001b[0m         )\n\u001b[1;32m   1055\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/jakubwilkowski/projects/presentation/pg10/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1187\u001b[0m                 \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1188\u001b[0m                 \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1189\u001b[0;31m                 context)\n\u001b[0m\u001b[1;32m   1190\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1191\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/jakubwilkowski/projects/presentation/pg10/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   1400\u001b[0m                 util.raise_from_cause(\n\u001b[1;32m   1401\u001b[0m                     \u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1402\u001b[0;31m                     \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1403\u001b[0m                 )\n\u001b[1;32m   1404\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/jakubwilkowski/projects/presentation/pg10/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc\u001b[0m in \u001b[0;36mraise_from_cause\u001b[0;34m(exception, exc_info)\u001b[0m\n\u001b[1;32m    201\u001b[0m     \u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    202\u001b[0m     \u001b[0mcause\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mexception\u001b[0m \u001b[0;32melse\u001b[0m \u001b[0mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 203\u001b[0;31m     \u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexception\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcause\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcause\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    204\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    205\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mpy3k\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/jakubwilkowski/projects/presentation/pg10/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1180\u001b[0m                         \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1181\u001b[0m                         \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1182\u001b[0;31m                         context)\n\u001b[0m\u001b[1;32m   1183\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1184\u001b[0m             self._handle_dbapi_exception(\n",
      "\u001b[0;32m/home/jakubwilkowski/projects/presentation/pg10/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    468\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    469\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 470\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    471\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    472\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mIntegrityError\u001b[0m: (psycopg2.IntegrityError) null value in column \"id\" violates not-null constraint\nDETAIL:  Failing row contains (null, 5).\n [SQL: 'INSERT INTO bar(val1) VALUES (5);']"
     ]
    }
   ],
   "source": [
    "%%sql $connection96\n",
    "DROP TABLE foo CASCADE;\n",
    "INSERT INTO bar(val1) VALUES (5);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Native partitioning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Messing with partitions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "4 rows affected.\n",
      "Done.\n",
      "3 rows affected.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "DROP TABLE IF EXISTS measurement;\n",
    "DROP TABLE IF EXISTS site;\n",
    "DROP TABLE IF EXISTS pollutant;\n",
    "\n",
    "CREATE TABLE site (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT);\n",
    "INSERT INTO site(name) values ('Marszalkowska'), ('Niepodleglosci'), ('Podlesna'), ('Wokalna');\n",
    "\n",
    "CREATE TABLE pollutant (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT);\n",
    "INSERT INTO pollutant(name) values ('PM10'), ('PM2.5'), ('CO2');\n",
    "\n",
    "CREATE TABLE measurement(\n",
    "id INTEGER GENERATED ALWAYS AS IDENTITY,\n",
    "datetime TIMESTAMPTZ,\n",
    "site_id INTEGER,\n",
    "pollutant_id INTEGER,\n",
    "value FLOAT)\n",
    "PARTITION BY RANGE (datetime);\n",
    "\n",
    "CREATE TABLE measurement_201708\n",
    "PARTITION OF measurement(datetime)\n",
    "FOR VALUES FROM ('2017-08-01') TO ('2017-09-01');\n",
    "CREATE TABLE measurement_201709\n",
    "PARTITION OF measurement(datetime)\n",
    "FOR VALUES FROM ('2017-09-01') TO ('2017-10-01');\n",
    "CREATE TABLE measurement_201710\n",
    "PARTITION OF measurement(datetime)\n",
    "FOR VALUES FROM ('2017-10-01') TO ('2017-11-01');\n",
    "\n",
    "ALTER TABLE measurement_201708 ADD PRIMARY KEY (id);\n",
    "ALTER TABLE measurement_201708 ADD CONSTRAINT fk_measurement_201708_site FOREIGN KEY (site_id) REFERENCES site(id);\n",
    "CREATE INDEX idx_measurement_201708_datetime ON measurement_201708(datetime);\n",
    "\n",
    "ALTER TABLE measurement_201709 ADD PRIMARY KEY (id);\n",
    "ALTER TABLE measurement_201709 ADD CONSTRAINT fk_measurement_201709_site FOREIGN KEY (site_id) REFERENCES site(id);\n",
    "CREATE INDEX idx_measurement_201709_datetime ON measurement_201709(datetime);\n",
    "\n",
    "ALTER TABLE measurement_201710 ADD PRIMARY KEY (id);\n",
    "ALTER TABLE measurement_201710 ADD CONSTRAINT fk_measurement_201710_site FOREIGN KEY (site_id) REFERENCES site(id);\n",
    "CREATE INDEX idx_measurement_201710_datetime ON measurement_201710(datetime);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1000 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "INSERT INTO measurement(datetime, site_id, pollutant_id, value)\n",
    "SELECT '2017-08-01'::TIMESTAMPTZ + ((random()*90)::int) * INTERVAL '1 day',\n",
    "(1 + random()*(SELECT max(id)-1 FROM site))::int,\n",
    "(1 + random()*(SELECT max(id)-1 FROM pollutant))::int,\n",
    "random()\n",
    "FROM generate_series(1,1000);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>datetime</th>\n",
       "        <th>site_id</th>\n",
       "        <th>pollutant_id</th>\n",
       "        <th>value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>2017-09-20 00:00:00+00:00</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>0.974444536958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>20</td>\n",
       "        <td>2017-09-20 00:00:00+00:00</td>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "        <td>0.518974691164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>21</td>\n",
       "        <td>2017-09-25 00:00:00+00:00</td>\n",
       "        <td>3</td>\n",
       "        <td>2</td>\n",
       "        <td>0.689467356075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23</td>\n",
       "        <td>2017-09-27 00:00:00+00:00</td>\n",
       "        <td>3</td>\n",
       "        <td>2</td>\n",
       "        <td>0.846892023925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>26</td>\n",
       "        <td>2017-09-23 00:00:00+00:00</td>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "        <td>0.278113745619</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, datetime.datetime(2017, 9, 20, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 4, 2, 0.97444453695789),\n",
       " (20, datetime.datetime(2017, 9, 20, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 1, 2, 0.518974691163749),\n",
       " (21, datetime.datetime(2017, 9, 25, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 3, 2, 0.689467356074601),\n",
       " (23, datetime.datetime(2017, 9, 27, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 3, 2, 0.846892023924738),\n",
       " (26, datetime.datetime(2017, 9, 23, 0, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)), 3, 1, 0.278113745618612)]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "SELECT * FROM measurement WHERE datetime BETWEEN '2017-09-20' AND '2017-09-27' limit 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Limit  (cost=4.22..11.48 rows=5 width=28)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;-&gt;  Append  (cost=4.22..14.39 rows=7 width=28)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Bitmap Heap Scan on measurement_201709  (cost=4.22..14.39 rows=7 width=28)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Recheck Cond: ((datetime &gt;= '2017-09-20 00:00:00+00'::timestamp with time zone) AND (datetime &lt;= '2017-09-27 00:00:00+00'::timestamp with time zone))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Bitmap Index Scan on idx_measurement_201709_datetime  (cost=0.00..4.22 rows=7 width=0)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Index Cond: ((datetime &gt;= '2017-09-20 00:00:00+00'::timestamp with time zone) AND (datetime &lt;= '2017-09-27 00:00:00+00'::timestamp with time zone))</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'Limit  (cost=4.22..11.48 rows=5 width=28)',),\n",
       " (u'  ->  Append  (cost=4.22..14.39 rows=7 width=28)',),\n",
       " (u'        ->  Bitmap Heap Scan on measurement_201709  (cost=4.22..14.39 rows=7 width=28)',),\n",
       " (u\"              Recheck Cond: ((datetime >= '2017-09-20 00:00:00+00'::timestamp with time zone) AND (datetime <= '2017-09-27 00:00:00+00'::timestamp with time zone))\",),\n",
       " (u'              ->  Bitmap Index Scan on idx_measurement_201709_datetime  (cost=0.00..4.22 rows=7 width=0)',),\n",
       " (u\"                    Index Cond: ((datetime >= '2017-09-20 00:00:00+00'::timestamp with time zone) AND (datetime <= '2017-09-27 00:00:00+00'::timestamp with time zone))\",)]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "EXPLAIN SELECT * FROM measurement WHERE datetime BETWEEN '2017-09-20' AND '2017-09-27' limit 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Multicolumn statistics\n",
    "## aka correlated statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "1000000 rows affected.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "DROP TABLE IF EXISTS counting_log;\n",
    "CREATE TABLE counting_log (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, datetime TIMESTAMP WITH TIME ZONE, child_id INTEGER, word TEXT);\n",
    "INSERT INTO counting_log(datetime, child_id, word) \n",
    "SELECT current_timestamp, i%1000, \n",
    "CASE WHEN i%4=1 THEN 'eeny' \n",
    "     WHEN i%4=2 THEN 'meeny' \n",
    "     WHEN i%4=3 THEN 'miny' \n",
    "     WHEN i%4=0 THEN 'moe' \n",
    "     ELSE 'nope' END \n",
    "FROM generate_series(1, 1000000) i;\n",
    "CREATE INDEX idx_counting_log_child_id on counting_log(child_id);\n",
    "CREATE INDEX idx_counting_log_datetime on counting_log(datetime);\n",
    "ANALYZE counting_log;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bitmap Heap Scan on counting_log  (cost=19.96..2700.09 rows=972 width=8)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;Recheck Cond: (child_id = 123)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;-&gt;  Bitmap Index Scan on idx_counting_log_child_id  (cost=0.00..19.71 rows=972 width=0)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Index Cond: (child_id = 123)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'Bitmap Heap Scan on counting_log  (cost=19.96..2700.09 rows=972 width=8)',),\n",
       " (u'  Recheck Cond: (child_id = 123)',),\n",
       " (u'  ->  Bitmap Index Scan on idx_counting_log_child_id  (cost=0.00..19.71 rows=972 width=0)',),\n",
       " (u'        Index Cond: (child_id = 123)',)]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10 \n",
    "EXPLAIN SELECT datetime FROM counting_log WHERE child_id=123;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Seq Scan on counting_log  (cost=0.00..19643.00 rows=248333 width=8)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;Filter: (word = 'miny'::text)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'Seq Scan on counting_log  (cost=0.00..19643.00 rows=248333 width=8)',),\n",
       " (u\"  Filter: (word = 'miny'::text)\",)]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10 \n",
    "EXPLAIN SELECT datetime FROM counting_log WHERE word='miny';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bitmap Heap Scan on counting_log  (cost=19.78..2702.34 rows=241 width=8)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;Recheck Cond: (child_id = 123)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;Filter: (word = 'miny'::text)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;-&gt;  Bitmap Index Scan on idx_counting_log_child_id  (cost=0.00..19.71 rows=972 width=0)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Index Cond: (child_id = 123)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'Bitmap Heap Scan on counting_log  (cost=19.78..2702.34 rows=241 width=8)',),\n",
       " (u'  Recheck Cond: (child_id = 123)',),\n",
       " (u\"  Filter: (word = 'miny'::text)\",),\n",
       " (u'  ->  Bitmap Index Scan on idx_counting_log_child_id  (cost=0.00..19.71 rows=972 width=0)',),\n",
       " (u'        Index Cond: (child_id = 123)',)]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10 \n",
    "EXPLAIN SELECT datetime FROM counting_log WHERE word='miny' and child_id=123;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1000</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1000L,)]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10 \n",
    "SELECT count(datetime) FROM counting_log WHERE word='miny' and child_id=123;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Postgres 10 to the rescue!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "CREATE STATISTICS st_counting_log_child_id_word ON child_id, word FROM counting_log;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bitmap Heap Scan on counting_log  (cost=19.97..2702.54 rows=973 width=8)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;Recheck Cond: (child_id = 123)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;Filter: (word = 'miny'::text)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;-&gt;  Bitmap Index Scan on idx_counting_log_child_id  (cost=0.00..19.72 rows=973 width=0)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Index Cond: (child_id = 123)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'Bitmap Heap Scan on counting_log  (cost=19.97..2702.54 rows=973 width=8)',),\n",
       " (u'  Recheck Cond: (child_id = 123)',),\n",
       " (u\"  Filter: (word = 'miny'::text)\",),\n",
       " (u'  ->  Bitmap Index Scan on idx_counting_log_child_id  (cost=0.00..19.72 rows=973 width=0)',),\n",
       " (u'        Index Cond: (child_id = 123)',)]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "ANALYZE counting_log;\n",
    "EXPLAIN SELECT datetime FROM counting_log WHERE word='miny' and child_id=123;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>stxname</th>\n",
       "        <th>stxkeys</th>\n",
       "        <th>stxkind</th>\n",
       "        <th>stxndistinct</th>\n",
       "        <th>stxdependencies</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>st_counting_log_child_id_word</td>\n",
       "        <td>3 4</td>\n",
       "        <td>[u'd', u'f']</td>\n",
       "        <td>{\"3, 4\": 1000}</td>\n",
       "        <td>{\"3 =&gt; 4\": 1.000000}</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'st_counting_log_child_id_word', '3 4', [u'd', u'f'], '{\"3, 4\": 1000}', '{\"3 => 4\": 1.000000}')]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "SELECT stxname, stxkeys, stxkind, stxndistinct, stxdependencies \n",
    "  FROM pg_statistic_ext \n",
    "  WHERE stxname = 'st_counting_log_child_id_word';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4. More parallelism!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>min_parallel_table_scan_size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8MB</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'8MB',)]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "show min_parallel_table_scan_size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>min_parallel_index_scan_size</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>512kB</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'512kB',)]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "show min_parallel_index_scan_size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>max_parallel_workers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'8',)]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "show max_parallel_workers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "10000001 rows affected.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "DROP TABLE IF EXISTS trigonometry;\n",
    "CREATE TABLE trigonometry AS SELECT i AS arg, sin(i) AS sine, cos(i) AS cosine, tan(i) AS tangent \n",
    "FROM generate_series(0, 100000, 0.01) i;\n",
    "\n",
    "CREATE INDEX idx_trigonometry_arg ON trigonometry(arg);\n",
    "CREATE INDEX idx_trigonometry_sine ON trigonometry(sine);\n",
    "CREATE INDEX idx_trigonometry_cosine ON trigonometry(cosine);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parallel aggregate (old stuff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Finalize Aggregate  (cost=140657.84..140657.85 rows=1 width=8)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;-&gt;  Gather  (cost=140657.63..140657.84 rows=2 width=8)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Workers Planned: 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Partial Aggregate  (cost=139657.63..139657.64 rows=1 width=8)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;  Parallel Seq Scan on trigonometry  (cost=0.00..134437.89 rows=2087896 width=8)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Filter: (arg &gt; '50000'::numeric)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'Finalize Aggregate  (cost=140657.84..140657.85 rows=1 width=8)',),\n",
       " (u'  ->  Gather  (cost=140657.63..140657.84 rows=2 width=8)',),\n",
       " (u'        Workers Planned: 2',),\n",
       " (u'        ->  Partial Aggregate  (cost=139657.63..139657.64 rows=1 width=8)',),\n",
       " (u'              ->  Parallel Seq Scan on trigonometry  (cost=0.00..134437.89 rows=2087896 width=8)',),\n",
       " (u\"                    Filter: (arg > '50000'::numeric)\",)]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "EXPLAIN SELECT count(arg) FROM trigonometry WHERE arg > 50000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Parallel index scan (new and shiny)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Index Scan using idx_trigonometry_arg on trigonometry  (cost=0.43..203653.08 rows=5010951 width=32)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;Index Cond: (arg &gt; '50000'::numeric)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'Index Scan using idx_trigonometry_arg on trigonometry  (cost=0.43..203653.08 rows=5010951 width=32)',),\n",
       " (u\"  Index Cond: (arg > '50000'::numeric)\",)]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "EXPLAIN SELECT * FROM trigonometry WHERE arg > 50000;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Index Scan using idx_trigonometry_arg on trigonometry  (cost=0.43..203653.08 rows=5010951 width=32)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;Index Cond: (arg &gt; '50000'::numeric)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'Index Scan using idx_trigonometry_arg on trigonometry  (cost=0.43..203653.08 rows=5010951 width=32)',),\n",
       " (u\"  Index Cond: (arg > '50000'::numeric)\",)]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "SET parallel_setup_cost=100;\n",
    "EXPLAIN SELECT * FROM trigonometry WHERE arg > 50000;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gather  (cost=1000.43..40197.28 rows=13576 width=8)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;Workers Planned: 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;-&gt;  Parallel Index Scan using idx_trigonometry_arg on trigonometry  (cost=0.43..37839.68 rows=5657 width=8)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Index Cond: ((arg &gt; '100'::numeric) AND (arg &lt; '10000'::numeric))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Filter: (sine &gt; '0.999'::double precision)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'Gather  (cost=1000.43..40197.28 rows=13576 width=8)',),\n",
       " (u'  Workers Planned: 2',),\n",
       " (u'  ->  Parallel Index Scan using idx_trigonometry_arg on trigonometry  (cost=0.43..37839.68 rows=5657 width=8)',),\n",
       " (u\"        Index Cond: ((arg > '100'::numeric) AND (arg < '10000'::numeric))\",),\n",
       " (u\"        Filter: (sine > '0.999'::double precision)\",)]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "SET parallel_setup_cost=1000;\n",
    "EXPLAIN SELECT arg FROM trigonometry WHERE sine > 0.999 AND arg >100 AND arg < 10000;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Let's spread some chaos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "9 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gather  (cost=1000.43..40197.28 rows=13576 width=8) (actual time=0.392..260.467 rows=14097 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;Workers Planned: 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;Workers Launched: 0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;-&gt;  Parallel Index Scan using idx_trigonometry_arg on trigonometry  (cost=0.43..37839.68 rows=5657 width=8) (actual time=0.169..258.799 rows=14097 loops=1)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Index Cond: ((arg &gt; '100'::numeric) AND (arg &lt; '10000'::numeric))</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Filter: (sine &gt; '0.999'::double precision)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Rows Removed by Filter: 975902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Planning time: 0.187 ms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Execution time: 261.186 ms</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'Gather  (cost=1000.43..40197.28 rows=13576 width=8) (actual time=0.392..260.467 rows=14097 loops=1)',),\n",
       " (u'  Workers Planned: 2',),\n",
       " (u'  Workers Launched: 0',),\n",
       " (u'  ->  Parallel Index Scan using idx_trigonometry_arg on trigonometry  (cost=0.43..37839.68 rows=5657 width=8) (actual time=0.169..258.799 rows=14097 loops=1)',),\n",
       " (u\"        Index Cond: ((arg > '100'::numeric) AND (arg < '10000'::numeric))\",),\n",
       " (u\"        Filter: (sine > '0.999'::double precision)\",),\n",
       " (u'        Rows Removed by Filter: 975902',),\n",
       " (u'Planning time: 0.187 ms',),\n",
       " (u'Execution time: 261.186 ms',)]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "SET max_parallel_workers =0;\n",
    "SET force_parallel_mode=on;\n",
    "EXPLAIN ANALYZE SELECT arg FROM trigonometry WHERE sine > 0.999 AND arg >100 AND arg < 10000;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "SET max_parallel_workers = 8;\n",
    "SET force_parallel_mode=off;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5. Full text search support JSON & JSONB columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "CREATE TABLE transactions(\n",
    "  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, \n",
    "  transaction_id VARCHAR(10), \n",
    "  user_id INTEGER, \n",
    "  created_datetime TIMESTAMP WITH TIME ZONE, \n",
    "  result BOOL, \n",
    "  amount INT ,\n",
    "  response_data JSON);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1000 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "INSERT INTO transactions(\n",
    "    transaction_id, \n",
    "    user_id, \n",
    "    created_datetime, \n",
    "    result, \n",
    "    amount, \n",
    "    response_data)\n",
    "SELECT \n",
    "  tran.id, \n",
    "  ceil(random()*100), \n",
    "  tran.datetime, \n",
    "  tran.result, \n",
    "  tran.amount, \n",
    "  ('{\"transaction\": \n",
    "    {\"id\": \"'|| tran.id ||'\", \n",
    "     \"transaction_datetime\": \"'|| tran.datetime || '\",\n",
    "     \"amount\": '|| tran.amount::text || ',\n",
    "     \"is_success\": \"'|| tran.result || '\",\n",
    "     \"message\": \"'|| tran.msg || '\"}}')::json\n",
    "FROM (\n",
    "  SELECT \n",
    "    substring(md5(random()::text), 1, 10) as id, \n",
    "    current_timestamp + (ceil(random()*1000)-500) * INTERVAL '1 minute' as datetime,\n",
    "    ceil(random()*1000) as amount,\n",
    "    NOT (i%3=1) as result,\n",
    "    CASE WHEN i%9=1 THEN 'insufficient funds' \n",
    "         WHEN i%9=4 THEN 'blocked account' \n",
    "         WHEN i%9=7 THEN 'fraud detected' \n",
    "         ELSE 'accepted' END as msg\n",
    "    FROM generate_series(1,1000) i) tran;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "CREATE INDEX idx_transactions_response_data \n",
    "  ON transactions USING GIN (to_tsvector('english', response_data));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>response_data</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>{u'transaction': {u'message': u'insufficient funds', u'is_success': u'false', u'transaction_datetime': u'2017-11-08 05:04:56.859288+00', u'amount': 592, u'id': u'428ef960c5'}}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>{u'transaction': {u'message': u'accepted', u'is_success': u'true', u'transaction_datetime': u'2017-11-08 17:08:56.859288+00', u'amount': 979, u'id': u'03d857af3c'}}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>{u'transaction': {u'message': u'accepted', u'is_success': u'true', u'transaction_datetime': u'2017-11-08 16:48:56.859288+00', u'amount': 325, u'id': u'd7af7d4046'}}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>{u'transaction': {u'message': u'blocked account', u'is_success': u'false', u'transaction_datetime': u'2017-11-08 18:34:56.859288+00', u'amount': 355, u'id': u'892adc1556'}}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>{u'transaction': {u'message': u'accepted', u'is_success': u'true', u'transaction_datetime': u'2017-11-08 17:16:56.859288+00', u'amount': 103, u'id': u'09295d285b'}}</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[({u'transaction': {u'message': u'insufficient funds', u'amount': 592, u'is_success': u'false', u'transaction_datetime': u'2017-11-08 05:04:56.859288+00', u'id': u'428ef960c5'}},),\n",
       " ({u'transaction': {u'message': u'accepted', u'amount': 979, u'is_success': u'true', u'transaction_datetime': u'2017-11-08 17:08:56.859288+00', u'id': u'03d857af3c'}},),\n",
       " ({u'transaction': {u'message': u'accepted', u'amount': 325, u'is_success': u'true', u'transaction_datetime': u'2017-11-08 16:48:56.859288+00', u'id': u'd7af7d4046'}},),\n",
       " ({u'transaction': {u'message': u'blocked account', u'amount': 355, u'is_success': u'false', u'transaction_datetime': u'2017-11-08 18:34:56.859288+00', u'id': u'892adc1556'}},),\n",
       " ({u'transaction': {u'message': u'accepted', u'amount': 103, u'is_success': u'true', u'transaction_datetime': u'2017-11-08 17:16:56.859288+00', u'id': u'09295d285b'}},)]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "SELECT response_data FROM transactions limit 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>transaction_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>428ef960c5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>be93dcbf0e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>793b943f2b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9ca79fc79d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>d880943fb7</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'428ef960c5',),\n",
       " (u'be93dcbf0e',),\n",
       " (u'793b943f2b',),\n",
       " (u'9ca79fc79d',),\n",
       " (u'd880943fb7',)]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "SELECT transaction_id \n",
    "  FROM transactions \n",
    "  WHERE to_tsvector('english', response_data->'transaction'->'message') @@ \n",
    "    to_tsquery('english', 'insufficient') LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>transaction_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>428ef960c5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>be93dcbf0e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>793b943f2b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>9ca79fc79d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>d880943fb7</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'428ef960c5',),\n",
       " (u'be93dcbf0e',),\n",
       " (u'793b943f2b',),\n",
       " (u'9ca79fc79d',),\n",
       " (u'd880943fb7',)]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "SELECT transaction_id \n",
    "  FROM transactions\n",
    "  WHERE to_tsvector('english', response_data->'transaction'->'message') @@ \n",
    "    'insuffici' LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>QUERY PLAN</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Limit  (cost=0.00..299.50 rows=5 width=38)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;-&gt;  Seq Scan on transactions  (cost=0.00..299.50 rows=5 width=38)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Filter: (to_tsvector('english'::regconfig, ((response_data -&gt; 'transaction'::text) -&gt; 'message'::text)) @@ '''insuffici'''::tsquery)</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'Limit  (cost=0.00..299.50 rows=5 width=38)',),\n",
       " (u'  ->  Seq Scan on transactions  (cost=0.00..299.50 rows=5 width=38)',),\n",
       " (u\"        Filter: (to_tsvector('english'::regconfig, ((response_data -> 'transaction'::text) -> 'message'::text)) @@ '''insuffici'''::tsquery)\",)]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql $connection10\n",
    "EXPLAIN SELECT transaction_id \n",
    "  FROM transactions\n",
    "  WHERE to_tsvector('english', response_data->'transaction'->'message') @@ \n",
    "    to_tsquery('english', 'insufficient') LIMIT 5;"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  },
  "toc": {
   "nav_menu": {
    "height": "268px",
    "width": "618px"
   },
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": "block",
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}