<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" lang="" xml:lang="">
<head>
  <meta charset="utf-8" />
  <meta name="generator" content="pandoc" />
  <meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=yes" />
  <title>in</title>
  <style>
    html {
      color: #1a1a1a;
      background-color: #fdfdfd;
    }
    body {
      margin: 0 auto;
      max-width: 36em;
      padding-left: 50px;
      padding-right: 50px;
      padding-top: 50px;
      padding-bottom: 50px;
      hyphens: auto;
      overflow-wrap: break-word;
      text-rendering: optimizeLegibility;
      font-kerning: normal;
    }
    @media (max-width: 600px) {
      body {
        font-size: 0.9em;
        padding: 12px;
      }
      h1 {
        font-size: 1.8em;
      }
    }
    @media print {
      html {
        background-color: white;
      }
      body {
        background-color: transparent;
        color: black;
        font-size: 12pt;
      }
      p, h2, h3 {
        orphans: 3;
        widows: 3;
      }
      h2, h3, h4 {
        page-break-after: avoid;
      }
    }
    p {
      margin: 1em 0;
    }
    a {
      color: #1a1a1a;
    }
    a:visited {
      color: #1a1a1a;
    }
    img {
      max-width: 100%;
    }
    h1, h2, h3, h4, h5, h6 {
      margin-top: 1.4em;
    }
    h5, h6 {
      font-size: 1em;
      font-style: italic;
    }
    h6 {
      font-weight: normal;
    }
    ol, ul {
      padding-left: 1.7em;
      margin-top: 1em;
    }
    li > ol, li > ul {
      margin-top: 0;
    }
    blockquote {
      margin: 1em 0 1em 1.7em;
      padding-left: 1em;
      border-left: 2px solid #e6e6e6;
      color: #606060;
    }
    code {
      font-family: Menlo, Monaco, Consolas, 'Lucida Console', monospace;
      font-size: 85%;
      margin: 0;
      hyphens: manual;
    }
    pre {
      margin: 1em 0;
      overflow: auto;
    }
    pre code {
      padding: 0;
      overflow: visible;
      overflow-wrap: normal;
    }
    .sourceCode {
     background-color: transparent;
     overflow: visible;
    }
    hr {
      background-color: #1a1a1a;
      border: none;
      height: 1px;
      margin: 1em 0;
    }
    table {
      margin: 1em 0;
      border-collapse: collapse;
      width: 100%;
      overflow-x: auto;
      display: block;
      font-variant-numeric: lining-nums tabular-nums;
    }
    table caption {
      margin-bottom: 0.75em;
    }
    tbody {
      margin-top: 0.5em;
      border-top: 1px solid #1a1a1a;
      border-bottom: 1px solid #1a1a1a;
    }
    th {
      border-top: 1px solid #1a1a1a;
      padding: 0.25em 0.5em 0.25em 0.5em;
    }
    td {
      padding: 0.125em 0.5em 0.25em 0.5em;
    }
    header {
      margin-bottom: 4em;
      text-align: center;
    }
    #TOC li {
      list-style: none;
    }
    #TOC ul {
      padding-left: 1.3em;
    }
    #TOC > ul {
      padding-left: 0;
    }
    #TOC a:not(:hover) {
      text-decoration: none;
    }
    code{white-space: pre-wrap;}
    span.smallcaps{font-variant: small-caps;}
    div.columns{display: flex; gap: min(4vw, 1.5em);}
    div.column{flex: auto; overflow-x: auto;}
    div.hanging-indent{margin-left: 1.5em; text-indent: -1.5em;}
    /* The extra [class] is a hack that increases specificity enough to
       override a similar rule in reveal.js */
    ul.task-list[class]{list-style: none;}
    ul.task-list li input[type="checkbox"] {
      font-size: inherit;
      width: 0.8em;
      margin: 0 0.8em 0.2em -1.6em;
      vertical-align: middle;
    }
    .display.math{display: block; text-align: center; margin: 0.5rem auto;}
    /* CSS for syntax highlighting */
    pre > code.sourceCode { white-space: pre; position: relative; }
    pre > code.sourceCode > span { display: inline-block; line-height: 1.25; }
    pre > code.sourceCode > span:empty { height: 1.2em; }
    .sourceCode { overflow: visible; }
    code.sourceCode > span { color: inherit; text-decoration: inherit; }
    div.sourceCode { margin: 1em 0; }
    pre.sourceCode { margin: 0; }
    @media screen {
    div.sourceCode { overflow: auto; }
    }
    @media print {
    pre > code.sourceCode { white-space: pre-wrap; }
    pre > code.sourceCode > span { text-indent: -5em; padding-left: 5em; }
    }
    pre.numberSource code
      { counter-reset: source-line 0; }
    pre.numberSource code > span
      { position: relative; left: -4em; counter-increment: source-line; }
    pre.numberSource code > span > a:first-child::before
      { content: counter(source-line);
        position: relative; left: -1em; text-align: right; vertical-align: baseline;
        border: none; display: inline-block;
        -webkit-touch-callout: none; -webkit-user-select: none;
        -khtml-user-select: none; -moz-user-select: none;
        -ms-user-select: none; user-select: none;
        padding: 0 4px; width: 4em;
        color: #aaaaaa;
      }
    pre.numberSource { margin-left: 3em; border-left: 1px solid #aaaaaa;  padding-left: 4px; }
    div.sourceCode
      {   }
    @media screen {
    pre > code.sourceCode > span > a:first-child::before { text-decoration: underline; }
    }
    code span.al { color: #ff0000; font-weight: bold; } /* Alert */
    code span.an { color: #60a0b0; font-weight: bold; font-style: italic; } /* Annotation */
    code span.at { color: #7d9029; } /* Attribute */
    code span.bn { color: #40a070; } /* BaseN */
    code span.bu { color: #008000; } /* BuiltIn */
    code span.cf { color: #007020; font-weight: bold; } /* ControlFlow */
    code span.ch { color: #4070a0; } /* Char */
    code span.cn { color: #880000; } /* Constant */
    code span.co { color: #60a0b0; font-style: italic; } /* Comment */
    code span.cv { color: #60a0b0; font-weight: bold; font-style: italic; } /* CommentVar */
    code span.do { color: #ba2121; font-style: italic; } /* Documentation */
    code span.dt { color: #902000; } /* DataType */
    code span.dv { color: #40a070; } /* DecVal */
    code span.er { color: #ff0000; font-weight: bold; } /* Error */
    code span.ex { } /* Extension */
    code span.fl { color: #40a070; } /* Float */
    code span.fu { color: #06287e; } /* Function */
    code span.im { color: #008000; font-weight: bold; } /* Import */
    code span.in { color: #60a0b0; font-weight: bold; font-style: italic; } /* Information */
    code span.kw { color: #007020; font-weight: bold; } /* Keyword */
    code span.op { color: #666666; } /* Operator */
    code span.ot { color: #007020; } /* Other */
    code span.pp { color: #bc7a00; } /* Preprocessor */
    code span.sc { color: #4070a0; } /* SpecialChar */
    code span.ss { color: #bb6688; } /* SpecialString */
    code span.st { color: #4070a0; } /* String */
    code span.va { color: #19177c; } /* Variable */
    code span.vs { color: #4070a0; } /* VerbatimString */
    code span.wa { color: #60a0b0; font-weight: bold; font-style: italic; } /* Warning */
  </style>
  <!--[if lt IE 9]>
    <script src="//cdnjs.cloudflare.com/ajax/libs/html5shiv/3.7.3/html5shiv-printshiv.min.js"></script>
  <![endif]-->
</head>
<body>
<h1 id="lab-1">Lab 1</h1>
<h2 id="krzysztof-łazarz">Krzysztof Łazarz</h2>
<hr />
<h1 id="ćwiczenia">Ćwiczenia</h1>
<h3 id="indeksy-oparte-o-haszowanie">Indeksy oparte o haszowanie</h3>
<p>Wykonaj w tabeli zamowienia zapytanie, które wyświetla wszystkie
zamówienia na kompozycję buk1. Sprawdź plan wykonania zapytania i zapisz
czas jego wykonania.</p>
<div class="sourceCode" id="cb1"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb1-1"><a href="#cb1-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">SELECT</span> <span class="op">*</span></span>
<span id="cb1-2"><a href="#cb1-2" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia</span>
<span id="cb1-3"><a href="#cb1-3" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>;</span>
<span id="cb1-4"><a href="#cb1-4" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb1-5"><a href="#cb1-5" aria-hidden="true" tabindex="-1"></a> idzamowienia | idklienta | idodbiorcy | idkompozycji |   termin   | cena  | zaplacone |            uwagi             </span>
<span id="cb1-6"><a href="#cb1-6" aria-hidden="true" tabindex="-1"></a><span class="co">--------------+-----------+------------+--------------+------------+-------+-----------+------------------------------</span></span>
<span id="cb1-7"><a href="#cb1-7" aria-hidden="true" tabindex="-1"></a>      <span class="dv">2703001</span> | msowins   |          <span class="dv">1</span> | buk1         | <span class="dv">2006</span><span class="op">-</span><span class="dv">04</span><span class="op">-</span><span class="dv">27</span> | <span class="fl">95.00</span> | f         | dolaczyc zyczenia urodzinowe</span></code></pre></div>
<div class="sourceCode" id="cb2"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb2-1"><a href="#cb2-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> </span>
<span id="cb2-2"><a href="#cb2-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span></span>
<span id="cb2-3"><a href="#cb2-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia</span>
<span id="cb2-4"><a href="#cb2-4" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>;</span>
<span id="cb2-5"><a href="#cb2-5" aria-hidden="true" tabindex="-1"></a>                                              <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                               </span>
<span id="cb2-6"><a href="#cb2-6" aria-hidden="true" tabindex="-1"></a><span class="co">-------------------------------------------------------------------------------------------------------</span></span>
<span id="cb2-7"><a href="#cb2-7" aria-hidden="true" tabindex="-1"></a> Seq <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">11.88</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> width<span class="op">=</span><span class="dv">507</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">007</span><span class="op">..</span><span class="fl">0.008</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb2-8"><a href="#cb2-8" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Filter</span>: (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb2-9"><a href="#cb2-9" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Rows</span> Removed <span class="kw">by</span> <span class="kw">Filter</span>: <span class="dv">14</span></span>
<span id="cb2-10"><a href="#cb2-10" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.042</span> ms</span>
<span id="cb2-11"><a href="#cb2-11" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">0.020</span> ms</span>
<span id="cb2-12"><a href="#cb2-12" aria-hidden="true" tabindex="-1"></a>(<span class="dv">5</span> <span class="kw">rows</span>)</span></code></pre></div>
<p>Dodaj do tabeli zamowienia indeks oparty o haszowanie, który
przyspieszy powyższe zapytanie. Sprawdź plan i zapisz czas
wykonania.</p>
<div class="sourceCode" id="cb3"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb3-1"><a href="#cb3-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">CREATE</span> <span class="kw">INDEX</span> idx_zamowienia_idkompozycji <span class="kw">ON</span> zamowienia <span class="kw">USING</span> <span class="kw">HASH</span> (idkompozycji);</span>
<span id="cb3-2"><a href="#cb3-2" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">INDEX</span></span>
<span id="cb3-3"><a href="#cb3-3" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> </span>
<span id="cb3-4"><a href="#cb3-4" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span></span>
<span id="cb3-5"><a href="#cb3-5" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia</span>
<span id="cb3-6"><a href="#cb3-6" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>;</span>
<span id="cb3-7"><a href="#cb3-7" aria-hidden="true" tabindex="-1"></a>                                                               <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                                </span>
<span id="cb3-8"><a href="#cb3-8" aria-hidden="true" tabindex="-1"></a><span class="co">-----------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb3-9"><a href="#cb3-9" aria-hidden="true" tabindex="-1"></a> <span class="kw">Bitmap</span> <span class="kw">Heap</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">30</span>.<span class="dv">56</span><span class="op">..</span><span class="fl">175.15</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">068</span><span class="op">..</span><span class="fl">0.368</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb3-10"><a href="#cb3-10" aria-hidden="true" tabindex="-1"></a>   Recheck Cond: (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb3-11"><a href="#cb3-11" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Heap</span> Blocks: exact<span class="op">=</span><span class="dv">134</span></span>
<span id="cb3-12"><a href="#cb3-12" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_zamowienia_idkompozycji  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">30.35</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">049</span><span class="op">..</span><span class="fl">0.050</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb3-13"><a href="#cb3-13" aria-hidden="true" tabindex="-1"></a>         <span class="kw">Index</span> Cond: (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb3-14"><a href="#cb3-14" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.279</span> ms</span>
<span id="cb3-15"><a href="#cb3-15" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">0.419</span> ms</span>
<span id="cb3-16"><a href="#cb3-16" aria-hidden="true" tabindex="-1"></a>(<span class="dv">7</span> <span class="kw">rows</span>)</span></code></pre></div>
<p>Przed rozpoczęciem kolejnego ćwiczenia usuń indeks.</p>
<div class="sourceCode" id="cb4"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb4-1"><a href="#cb4-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">DROP</span> <span class="kw">INDEX</span> idx_zamowienia_idkompozycji;</span>
<span id="cb4-2"><a href="#cb4-2" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> <span class="kw">INDEX</span></span></code></pre></div>
<p><strong>Wnioski: dodanie indeksu trzykrotnie wydłużyło ilość
estymowanych jednostek czasu, i wydłużyło samo wykonanie zapytania ponad
20 krotnie</strong></p>
<hr />
<h2 id="indeksy-oparte-o-b-drzewa">Indeksy oparte o b-drzewa</h2>
<p>Uwaga: w ćwiczeniu korzystaj z porównywania wartości, nie używaj
dopasowywania wzorców.</p>
<p>Usuń indeks utworzony w poprzednim kroku i utwórz analogiczny indeks
oparty o b-drzewa. Powtórz poprzednie zapytanie i zapisz wyniki (plan i
czas).</p>
<div class="sourceCode" id="cb5"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb5-1"><a href="#cb5-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">CREATE</span> <span class="kw">INDEX</span> idx_btree_zamowienia_idkompozycji <span class="kw">ON</span> zamowienia (idkompozycji);</span>
<span id="cb5-2"><a href="#cb5-2" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">INDEX</span></span></code></pre></div>
<div class="sourceCode" id="cb6"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb6-1"><a href="#cb6-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> </span>
<span id="cb6-2"><a href="#cb6-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span> </span>
<span id="cb6-3"><a href="#cb6-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia </span>
<span id="cb6-4"><a href="#cb6-4" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>;</span>
<span id="cb6-5"><a href="#cb6-5" aria-hidden="true" tabindex="-1"></a>                                                                  <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                                   </span>
<span id="cb6-6"><a href="#cb6-6" aria-hidden="true" tabindex="-1"></a><span class="co">-----------------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb6-7"><a href="#cb6-7" aria-hidden="true" tabindex="-1"></a> <span class="kw">Bitmap</span> <span class="kw">Heap</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">10</span>.<span class="dv">85</span><span class="op">..</span><span class="fl">155.44</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">039</span><span class="op">..</span><span class="fl">0.258</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb6-8"><a href="#cb6-8" aria-hidden="true" tabindex="-1"></a>   Recheck Cond: (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb6-9"><a href="#cb6-9" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Heap</span> Blocks: exact<span class="op">=</span><span class="dv">134</span></span>
<span id="cb6-10"><a href="#cb6-10" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_btree_zamowienia_idkompozycji  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">10.64</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">027</span><span class="op">..</span><span class="fl">0.027</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb6-11"><a href="#cb6-11" aria-hidden="true" tabindex="-1"></a>         <span class="kw">Index</span> Cond: (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb6-12"><a href="#cb6-12" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.170</span> ms</span>
<span id="cb6-13"><a href="#cb6-13" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">0.294</span> ms</span>
<span id="cb6-14"><a href="#cb6-14" aria-hidden="true" tabindex="-1"></a>(<span class="dv">7</span> <span class="kw">rows</span>)</span></code></pre></div>
<p>Wykonaj zapytanie, które wyświetla zamówienia na wszystkie
kompozycje, których ID zaczyna się na litery stojące w alfabecie przed
„c”. Czy indeks jest wykorzystywany?</p>
<div class="sourceCode" id="cb7"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb7-1"><a href="#cb7-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">SELECT</span> <span class="op">*</span> </span>
<span id="cb7-2"><a href="#cb7-2" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia </span>
<span id="cb7-3"><a href="#cb7-3" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idkompozycji <span class="op">&lt;</span> <span class="st">&#39;c&#39;</span>;</span>
<span id="cb7-4"><a href="#cb7-4" aria-hidden="true" tabindex="-1"></a> idzamowienia | idklienta | idodbiorcy | idkompozycji |   termin   | cena  | zaplacone |            uwagi             </span>
<span id="cb7-5"><a href="#cb7-5" aria-hidden="true" tabindex="-1"></a><span class="co">--------------+-----------+------------+--------------+------------+-------+-----------+------------------------------</span></span>
<span id="cb7-6"><a href="#cb7-6" aria-hidden="true" tabindex="-1"></a>      <span class="dv">2703001</span> | msowins   |          <span class="dv">1</span> | buk1         | <span class="dv">2006</span><span class="op">-</span><span class="dv">04</span><span class="op">-</span><span class="dv">27</span> | <span class="fl">95.00</span> | f         | dolaczyc zyczenia urodzinowe</span>
<span id="cb7-7"><a href="#cb7-7" aria-hidden="true" tabindex="-1"></a>      <span class="dv">2703002</span> | mbabik    |          <span class="dv">1</span> | buk2         | <span class="dv">2006</span><span class="op">-</span><span class="dv">04</span><span class="op">-</span><span class="dv">27</span> | <span class="fl">75.00</span> | f         | dolaczyc zyczenia urodzinowe</span>
<span id="cb7-8"><a href="#cb7-8" aria-hidden="true" tabindex="-1"></a>      <span class="dv">2703003</span> | mfibak    |          <span class="dv">2</span> | buk3         | <span class="dv">2006</span><span class="op">-</span><span class="dv">04</span><span class="op">-</span><span class="dv">27</span> | <span class="fl">90.00</span> | t         | dostarczyc po godzinie <span class="fl">17.00</span></span>
<span id="cb7-9"><a href="#cb7-9" aria-hidden="true" tabindex="-1"></a>      <span class="dv">2903003</span> | acygan    |         <span class="dv">12</span> | buk2         | <span class="dv">2006</span><span class="op">-</span><span class="dv">04</span><span class="op">-</span><span class="dv">30</span> | <span class="fl">75.00</span> | t         | </span>
<span id="cb7-10"><a href="#cb7-10" aria-hidden="true" tabindex="-1"></a>      <span class="dv">2903004</span> | ztylutek  |          <span class="dv">9</span> | buk3         | <span class="dv">2006</span><span class="op">-</span><span class="dv">04</span><span class="op">-</span><span class="dv">30</span> | <span class="fl">90.00</span> | f         | dostarczyc na godzine <span class="fl">10.30</span></span>
<span id="cb7-11"><a href="#cb7-11" aria-hidden="true" tabindex="-1"></a>      <span class="dv">2903008</span> | msowins   |          <span class="dv">3</span> | buk5         | <span class="dv">2007</span><span class="op">-</span><span class="dv">06</span><span class="op">-</span><span class="dv">09</span> | <span class="fl">75.00</span> | t         | </span>
<span id="cb7-12"><a href="#cb7-12" aria-hidden="true" tabindex="-1"></a>(<span class="op">..</span>.)</span></code></pre></div>
<div class="sourceCode" id="cb8"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb8-1"><a href="#cb8-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> </span>
<span id="cb8-2"><a href="#cb8-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span> </span>
<span id="cb8-3"><a href="#cb8-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia </span>
<span id="cb8-4"><a href="#cb8-4" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idkompozycji <span class="op">&lt;</span> <span class="st">&#39;c&#39;</span>;</span>
<span id="cb8-5"><a href="#cb8-5" aria-hidden="true" tabindex="-1"></a>                                                                   <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                                    </span>
<span id="cb8-6"><a href="#cb8-6" aria-hidden="true" tabindex="-1"></a><span class="co">-------------------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb8-7"><a href="#cb8-7" aria-hidden="true" tabindex="-1"></a> <span class="kw">Bitmap</span> <span class="kw">Heap</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">50</span>.<span class="dv">47</span><span class="op">..</span><span class="fl">233.16</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">3895</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">079</span><span class="op">..</span><span class="fl">0.555</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">3895</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb8-8"><a href="#cb8-8" aria-hidden="true" tabindex="-1"></a>   Recheck Cond: (idkompozycji <span class="op">&lt;</span> <span class="st">&#39;c&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb8-9"><a href="#cb8-9" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Heap</span> Blocks: exact<span class="op">=</span><span class="dv">134</span></span>
<span id="cb8-10"><a href="#cb8-10" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_btree_zamowienia_idkompozycji  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">49.50</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">3895</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">065</span><span class="op">..</span><span class="fl">0.065</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">3895</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb8-11"><a href="#cb8-11" aria-hidden="true" tabindex="-1"></a>         <span class="kw">Index</span> Cond: (idkompozycji <span class="op">&lt;</span> <span class="st">&#39;c&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb8-12"><a href="#cb8-12" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.056</span> ms</span>
<span id="cb8-13"><a href="#cb8-13" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">0.675</span> ms</span>
<span id="cb8-14"><a href="#cb8-14" aria-hidden="true" tabindex="-1"></a>(<span class="dv">7</span> <span class="kw">rows</span>)</span></code></pre></div>
<p><strong>Odp: Tak, indeks jest wykorzystywany</strong></p>
<p>Wykonaj zapytanie o pozostałe kompozycje, czyli „c” i kolejne litery.
Czy teraz indeks został użyty?</p>
<div class="sourceCode" id="cb9"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb9-1"><a href="#cb9-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> </span>
<span id="cb9-2"><a href="#cb9-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span> </span>
<span id="cb9-3"><a href="#cb9-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia </span>
<span id="cb9-4"><a href="#cb9-4" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idkompozycji <span class="op">&gt;=</span> <span class="st">&#39;c&#39;</span>;</span>
<span id="cb9-5"><a href="#cb9-5" aria-hidden="true" tabindex="-1"></a>                                                  <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                   </span>
<span id="cb9-6"><a href="#cb9-6" aria-hidden="true" tabindex="-1"></a><span class="co">---------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb9-7"><a href="#cb9-7" aria-hidden="true" tabindex="-1"></a> Seq <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">334.19</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">12120</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">006</span><span class="op">..</span><span class="fl">1.511</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">12120</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb9-8"><a href="#cb9-8" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Filter</span>: (idkompozycji <span class="op">&gt;=</span> <span class="st">&#39;c&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb9-9"><a href="#cb9-9" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Rows</span> Removed <span class="kw">by</span> <span class="kw">Filter</span>: <span class="dv">3895</span></span>
<span id="cb9-10"><a href="#cb9-10" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.061</span> ms</span>
<span id="cb9-11"><a href="#cb9-11" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">1.857</span> ms</span>
<span id="cb9-12"><a href="#cb9-12" aria-hidden="true" tabindex="-1"></a>(<span class="dv">5</span> <span class="kw">rows</span>)</span></code></pre></div>
<p><strong>Odp: Nie, tutaj indeks nie jest wykorzystywany, wymuszamy
wykorzystanie indeksu</strong></p>
<div class="sourceCode" id="cb10"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb10-1"><a href="#cb10-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">SET</span> enable_seqscan <span class="kw">TO</span> <span class="kw">off</span>;</span>
<span id="cb10-2"><a href="#cb10-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SET</span></span></code></pre></div>
<div class="sourceCode" id="cb11"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb11-1"><a href="#cb11-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> </span>
<span id="cb11-2"><a href="#cb11-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span> </span>
<span id="cb11-3"><a href="#cb11-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia </span>
<span id="cb11-4"><a href="#cb11-4" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idkompozycji <span class="op">&gt;=</span> <span class="st">&#39;c&#39;</span>;</span>
<span id="cb11-5"><a href="#cb11-5" aria-hidden="true" tabindex="-1"></a>                                                                     <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                                     </span>
<span id="cb11-6"><a href="#cb11-6" aria-hidden="true" tabindex="-1"></a><span class="co">----------------------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb11-7"><a href="#cb11-7" aria-hidden="true" tabindex="-1"></a> <span class="kw">Bitmap</span> <span class="kw">Heap</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">146</span>.<span class="dv">21</span><span class="op">..</span><span class="fl">431.71</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">12120</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">191</span><span class="op">..</span><span class="fl">0.942</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">12120</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb11-8"><a href="#cb11-8" aria-hidden="true" tabindex="-1"></a>   Recheck Cond: (idkompozycji <span class="op">&gt;=</span> <span class="st">&#39;c&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb11-9"><a href="#cb11-9" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Heap</span> Blocks: exact<span class="op">=</span><span class="dv">134</span></span>
<span id="cb11-10"><a href="#cb11-10" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_btree_zamowienia_idkompozycji  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">143.18</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">12120</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">177</span><span class="op">..</span><span class="fl">0.177</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">12120</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb11-11"><a href="#cb11-11" aria-hidden="true" tabindex="-1"></a>         <span class="kw">Index</span> Cond: (idkompozycji <span class="op">&gt;=</span> <span class="st">&#39;c&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb11-12"><a href="#cb11-12" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.125</span> ms</span>
<span id="cb11-13"><a href="#cb11-13" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">1.278</span> ms</span>
<span id="cb11-14"><a href="#cb11-14" aria-hidden="true" tabindex="-1"></a>(<span class="dv">7</span> <span class="kw">rows</span>)</span></code></pre></div>
<p>Przed rozpoczęciem kolejnego ćwiczenia usuń indeks.</p>
<div class="sourceCode" id="cb12"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb12-1"><a href="#cb12-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">DROP</span> <span class="kw">INDEX</span> <span class="cf">IF</span> <span class="kw">EXISTS</span> idx_btree_zamowienia_idkompozycji;</span>
<span id="cb12-2"><a href="#cb12-2" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> <span class="kw">INDEX</span></span></code></pre></div>
<h2 id="indeksy-a-wzorce">Indeksy a wzorce</h2>
<p>Załóż indeks dla kolumny uwagi i wykonaj zapytanie, które zwraca
wszystkie zamówienia, których uwagi zaczynają się od znaków „do”. Czy
indeks jest wykorzystywany?</p>
<div class="sourceCode" id="cb13"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb13-1"><a href="#cb13-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">CREATE</span> <span class="kw">INDEX</span> idx_zamowienia_uwagi <span class="kw">ON</span> zamowienia (uwagi);</span>
<span id="cb13-2"><a href="#cb13-2" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">INDEX</span></span></code></pre></div>
<div class="sourceCode" id="cb14"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb14-1"><a href="#cb14-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> </span>
<span id="cb14-2"><a href="#cb14-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span> </span>
<span id="cb14-3"><a href="#cb14-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia </span>
<span id="cb14-4"><a href="#cb14-4" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> uwagi <span class="kw">LIKE</span> <span class="st">&#39;do%&#39;</span>;</span>
<span id="cb14-5"><a href="#cb14-5" aria-hidden="true" tabindex="-1"></a>                                                         <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                          </span>
<span id="cb14-6"><a href="#cb14-6" aria-hidden="true" tabindex="-1"></a><span class="co">-----------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb14-7"><a href="#cb14-7" aria-hidden="true" tabindex="-1"></a> Seq <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">10000000000</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">10000000334.19</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">11</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">50</span>.<span class="dv">923</span><span class="op">..</span><span class="fl">51.436</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">11</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb14-8"><a href="#cb14-8" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Filter</span>: ((uwagi):<span class="ch">:text</span> ~~ <span class="st">&#39;do%&#39;</span>:<span class="ch">:text</span>)</span>
<span id="cb14-9"><a href="#cb14-9" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Rows</span> Removed <span class="kw">by</span> <span class="kw">Filter</span>: <span class="dv">16004</span></span>
<span id="cb14-10"><a href="#cb14-10" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.078</span> ms</span>
<span id="cb14-11"><a href="#cb14-11" aria-hidden="true" tabindex="-1"></a> JIT:</span>
<span id="cb14-12"><a href="#cb14-12" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Functions</span>: <span class="dv">2</span></span>
<span id="cb14-13"><a href="#cb14-13" aria-hidden="true" tabindex="-1"></a>   Options: Inlining <span class="kw">true</span>, Optimization <span class="kw">true</span>, Expressions <span class="kw">true</span>, Deforming <span class="kw">true</span></span>
<span id="cb14-14"><a href="#cb14-14" aria-hidden="true" tabindex="-1"></a>   Timing: Generation <span class="fl">0.192</span> ms, Inlining <span class="fl">30.583</span> ms, Optimization <span class="fl">12.102</span> ms, Emission <span class="fl">8.116</span> ms, Total <span class="fl">50.994</span> ms</span>
<span id="cb14-15"><a href="#cb14-15" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">72.068</span> ms</span>
<span id="cb14-16"><a href="#cb14-16" aria-hidden="true" tabindex="-1"></a>(<span class="dv">9</span> <span class="kw">rows</span>)</span></code></pre></div>
<p>Porównaj plany wykonania. Czy indeks jest wykorzystywany?<br />
<strong>Odp: Nie, indeks nie jest wykorzystywany, chyba dlatego że jest
wzorzec“</strong></p>
<p>Usuń indeks i utwórz nowy, tym razem określając dla niego jawnie
klasę operatorów varchar_pattern_ops (więcej informacji):<br />
Powtórz ćwiczenie i porównaj plan oraz czas wykonania.</p>
<div class="sourceCode" id="cb15"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb15-1"><a href="#cb15-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">DROP</span> <span class="kw">INDEX</span> <span class="cf">IF</span> <span class="kw">EXISTS</span> idx_zamowienia_uwagi;</span>
<span id="cb15-2"><a href="#cb15-2" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> <span class="kw">INDEX</span></span>
<span id="cb15-3"><a href="#cb15-3" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">CREATE</span> <span class="kw">INDEX</span> idx_zamowienia_uwagi_pattern_ops <span class="kw">ON</span> zamowienia (uwagi varchar_pattern_ops);</span>
<span id="cb15-4"><a href="#cb15-4" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">INDEX</span></span></code></pre></div>
<div class="sourceCode" id="cb16"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb16-1"><a href="#cb16-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> </span>
<span id="cb16-2"><a href="#cb16-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span>                                  </span>
<span id="cb16-3"><a href="#cb16-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia </span>
<span id="cb16-4"><a href="#cb16-4" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> uwagi <span class="kw">LIKE</span> <span class="st">&#39;do%&#39;</span>;</span>
<span id="cb16-5"><a href="#cb16-5" aria-hidden="true" tabindex="-1"></a>                                                                <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                                 </span>
<span id="cb16-6"><a href="#cb16-6" aria-hidden="true" tabindex="-1"></a><span class="co">-------------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb16-7"><a href="#cb16-7" aria-hidden="true" tabindex="-1"></a> <span class="kw">Bitmap</span> <span class="kw">Heap</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">4</span>.<span class="dv">40</span><span class="op">..</span><span class="fl">39.08</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">11</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">015</span><span class="op">..</span><span class="fl">0.016</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">11</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb16-8"><a href="#cb16-8" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Filter</span>: ((uwagi):<span class="ch">:text</span> ~~ <span class="st">&#39;do%&#39;</span>:<span class="ch">:text</span>)</span>
<span id="cb16-9"><a href="#cb16-9" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Heap</span> Blocks: exact<span class="op">=</span><span class="dv">1</span></span>
<span id="cb16-10"><a href="#cb16-10" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_zamowienia_uwagi_pattern_ops  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">4.40</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">11</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">009</span><span class="op">..</span><span class="fl">0.009</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">11</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb16-11"><a href="#cb16-11" aria-hidden="true" tabindex="-1"></a>         <span class="kw">Index</span> Cond: (((uwagi):<span class="ch">:text</span> ~<span class="op">&gt;=</span>~ <span class="st">&#39;do&#39;</span>:<span class="ch">:text</span>) <span class="kw">AND</span> ((uwagi):<span class="ch">:text</span> ~<span class="op">&lt;</span>~ <span class="st">&#39;dp&#39;</span>:<span class="ch">:text</span>))</span>
<span id="cb16-12"><a href="#cb16-12" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.198</span> ms</span>
<span id="cb16-13"><a href="#cb16-13" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">0.030</span> ms</span>
<span id="cb16-14"><a href="#cb16-14" aria-hidden="true" tabindex="-1"></a>(<span class="dv">7</span> <span class="kw">rows</span>)</span></code></pre></div>
<p>Przed rozpoczęciem kolejnego ćwiczenia usuń indeks.</p>
<div class="sourceCode" id="cb17"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb17-1"><a href="#cb17-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">DROP</span> <span class="kw">INDEX</span> <span class="cf">IF</span> <span class="kw">EXISTS</span> idx_zamowienia_uwagi_pattern_ops;</span>
<span id="cb17-2"><a href="#cb17-2" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> <span class="kw">INDEX</span></span></code></pre></div>
<h2 id="indeksy-wielokolumnowe">Indeksy wielokolumnowe</h2>
<p>Załóż indeks wielokolumnowy obejmujący kolumny idklienta, idodbiorcy
oraz idkompozycji.</p>
<div class="sourceCode" id="cb18"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb18-1"><a href="#cb18-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">CREATE</span> <span class="kw">INDEX</span> idx_zamowienia_wielokolumnowy <span class="kw">ON</span> zamowienia (idklienta, idodbiorcy, idkompozycji);</span>
<span id="cb18-2"><a href="#cb18-2" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">INDEX</span></span></code></pre></div>
<p>Wybierz po jednej wartości występującej w tych kolumnach (np. z
jednego przykładowego rekordu) i wykonaj:</p>
<ul>
<li>zapytanie, które łączy ograniczenia dla poszczególnych kolumn
operatorem AND,</li>
</ul>
<div class="sourceCode" id="cb19"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb19-1"><a href="#cb19-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> </span>
<span id="cb19-2"><a href="#cb19-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span> </span>
<span id="cb19-3"><a href="#cb19-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia </span>
<span id="cb19-4"><a href="#cb19-4" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idklienta <span class="op">=</span> <span class="st">&#39;msowins&#39;</span> </span>
<span id="cb19-5"><a href="#cb19-5" aria-hidden="true" tabindex="-1"></a>  <span class="kw">AND</span> idodbiorcy <span class="op">=</span> <span class="dv">1</span> </span>
<span id="cb19-6"><a href="#cb19-6" aria-hidden="true" tabindex="-1"></a>  <span class="kw">AND</span> idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>;</span>
<span id="cb19-7"><a href="#cb19-7" aria-hidden="true" tabindex="-1"></a>                                                                 <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                                 </span>
<span id="cb19-8"><a href="#cb19-8" aria-hidden="true" tabindex="-1"></a><span class="co">--------------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb19-9"><a href="#cb19-9" aria-hidden="true" tabindex="-1"></a> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">using</span> idx_zamowienia_wielokolumnowy <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">14</span><span class="op">..</span><span class="fl">8.16</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> width<span class="op">=</span><span class="dv">507</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">015</span><span class="op">..</span><span class="fl">0.016</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb19-10"><a href="#cb19-10" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Index</span> Cond: (((idklienta):<span class="ch">:text</span> <span class="op">=</span> <span class="st">&#39;msowins&#39;</span>:<span class="ch">:text</span>) <span class="kw">AND</span> (idodbiorcy <span class="op">=</span> <span class="dv">1</span>) <span class="kw">AND</span> (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>))</span>
<span id="cb19-11"><a href="#cb19-11" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.218</span> ms</span>
<span id="cb19-12"><a href="#cb19-12" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">0.035</span> ms</span>
<span id="cb19-13"><a href="#cb19-13" aria-hidden="true" tabindex="-1"></a>(<span class="dv">4</span> <span class="kw">rows</span>)</span></code></pre></div>
<ul>
<li>zapytanie analogiczne, ale z operatorem OR.</li>
</ul>
<div class="sourceCode" id="cb20"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb20-1"><a href="#cb20-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span>                                                                                </span>
<span id="cb20-2"><a href="#cb20-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span> </span>
<span id="cb20-3"><a href="#cb20-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia </span>
<span id="cb20-4"><a href="#cb20-4" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idklienta <span class="op">=</span> <span class="st">&#39;msowins&#39;</span> </span>
<span id="cb20-5"><a href="#cb20-5" aria-hidden="true" tabindex="-1"></a>  <span class="kw">OR</span> idodbiorcy <span class="op">=</span> <span class="dv">1</span> </span>
<span id="cb20-6"><a href="#cb20-6" aria-hidden="true" tabindex="-1"></a>  <span class="kw">OR</span> idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>;</span>
<span id="cb20-7"><a href="#cb20-7" aria-hidden="true" tabindex="-1"></a>                                                                 <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                                 </span>
<span id="cb20-8"><a href="#cb20-8" aria-hidden="true" tabindex="-1"></a><span class="co">--------------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb20-9"><a href="#cb20-9" aria-hidden="true" tabindex="-1"></a> <span class="kw">Bitmap</span> <span class="kw">Heap</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">20</span>.<span class="dv">64</span><span class="op">..</span><span class="fl">24.69</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">3</span> width<span class="op">=</span><span class="dv">507</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">019</span><span class="op">..</span><span class="fl">0.020</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">2</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb20-10"><a href="#cb20-10" aria-hidden="true" tabindex="-1"></a>   Recheck Cond: (((idklienta):<span class="ch">:text</span> <span class="op">=</span> <span class="st">&#39;msowins&#39;</span>:<span class="ch">:text</span>) <span class="kw">OR</span> (idodbiorcy <span class="op">=</span> <span class="dv">1</span>) <span class="kw">OR</span> (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>))</span>
<span id="cb20-11"><a href="#cb20-11" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Heap</span> Blocks: exact<span class="op">=</span><span class="dv">1</span></span>
<span id="cb20-12"><a href="#cb20-12" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  BitmapOr  (<span class="kw">cost</span><span class="op">=</span><span class="dv">20</span>.<span class="dv">64</span><span class="op">..</span><span class="fl">20.64</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">3</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">015</span><span class="op">..</span><span class="fl">0.015</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">0</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb20-13"><a href="#cb20-13" aria-hidden="true" tabindex="-1"></a>         <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_zamowienia_wielokolumnowy  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">4.14</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">012</span><span class="op">..</span><span class="fl">0.012</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb20-14"><a href="#cb20-14" aria-hidden="true" tabindex="-1"></a>               <span class="kw">Index</span> Cond: ((idklienta):<span class="ch">:text</span> <span class="op">=</span> <span class="st">&#39;msowins&#39;</span>:<span class="ch">:text</span>)</span>
<span id="cb20-15"><a href="#cb20-15" aria-hidden="true" tabindex="-1"></a>         <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_zamowienia_wielokolumnowy  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">8.25</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">002</span><span class="op">..</span><span class="fl">0.002</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">2</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb20-16"><a href="#cb20-16" aria-hidden="true" tabindex="-1"></a>               <span class="kw">Index</span> Cond: (idodbiorcy <span class="op">=</span> <span class="dv">1</span>)</span>
<span id="cb20-17"><a href="#cb20-17" aria-hidden="true" tabindex="-1"></a>         <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_zamowienia_wielokolumnowy  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">8.25</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">001</span><span class="op">..</span><span class="fl">0.001</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb20-18"><a href="#cb20-18" aria-hidden="true" tabindex="-1"></a>               <span class="kw">Index</span> Cond: (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb20-19"><a href="#cb20-19" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.058</span> ms</span>
<span id="cb20-20"><a href="#cb20-20" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">0.038</span> ms</span>
<span id="cb20-21"><a href="#cb20-21" aria-hidden="true" tabindex="-1"></a>(<span class="dv">12</span> <span class="kw">rows</span>)</span></code></pre></div>
<p>Porównaj plany wykonania. Czy indeks jest wykorzystywany?<br />
<strong>Odp: Oczywiście że indeks jest wykorzystywany. W labce było
napisane żeby ustawić flagę <code>enable_seqscan</code> na
<code>off</code>, ale nigdzie nie było napisane żeby to cofnąć.</strong>
<strong>Przez to ten krok labki (i być może przyszłe) nie ma sensu, but
who am I to judge</strong></p>
<p>Wykonaj teraz zapytanie, które zwraca wszystkie zamówienia na
kompozycję <code>buk1</code>, nie precyzując wymagań dotyczących innych
kolumn.</p>
<div class="sourceCode" id="cb21"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb21-1"><a href="#cb21-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> <span class="kw">SELECT</span> <span class="op">*</span></span>
<span id="cb21-2"><a href="#cb21-2" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia</span>
<span id="cb21-3"><a href="#cb21-3" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>;</span>
<span id="cb21-4"><a href="#cb21-4" aria-hidden="true" tabindex="-1"></a>                                                                 <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                                 </span>
<span id="cb21-5"><a href="#cb21-5" aria-hidden="true" tabindex="-1"></a><span class="co">--------------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb21-6"><a href="#cb21-6" aria-hidden="true" tabindex="-1"></a> <span class="kw">Bitmap</span> <span class="kw">Heap</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">332</span>.<span class="dv">61</span><span class="op">..</span><span class="fl">477.20</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">293</span><span class="op">..</span><span class="fl">0.478</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb21-7"><a href="#cb21-7" aria-hidden="true" tabindex="-1"></a>   Recheck Cond: (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb21-8"><a href="#cb21-8" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Heap</span> Blocks: exact<span class="op">=</span><span class="dv">134</span></span>
<span id="cb21-9"><a href="#cb21-9" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_zamowienia_wielokolumnowy  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">332.40</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">280</span><span class="op">..</span><span class="fl">0.281</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb21-10"><a href="#cb21-10" aria-hidden="true" tabindex="-1"></a>         <span class="kw">Index</span> Cond: (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb21-11"><a href="#cb21-11" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.054</span> ms</span>
<span id="cb21-12"><a href="#cb21-12" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">0.517</span> ms</span>
<span id="cb21-13"><a href="#cb21-13" aria-hidden="true" tabindex="-1"></a>(<span class="dv">7</span> <span class="kw">rows</span>)</span></code></pre></div>
<p>Usuń indeks wielokolumnowy i załóż osobne indeksy na każdą z
wymienionych kolumn. Wykonaj trzy powyższe zapytania i porównaj
wyniki.</p>
<div class="sourceCode" id="cb22"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb22-1"><a href="#cb22-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">DROP</span> <span class="kw">INDEX</span> <span class="cf">IF</span> <span class="kw">EXISTS</span> idx_zamowienia_wielokolumnowy;</span>
<span id="cb22-2"><a href="#cb22-2" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> <span class="kw">INDEX</span></span>
<span id="cb22-3"><a href="#cb22-3" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb22-4"><a href="#cb22-4" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">CREATE</span> <span class="kw">INDEX</span> idx_zamowienia_idklienta <span class="kw">ON</span> zamowienia (idklienta);</span>
<span id="cb22-5"><a href="#cb22-5" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">INDEX</span> idx_zamowienia_idodbiorcy <span class="kw">ON</span> zamowienia (idodbiorcy);</span>
<span id="cb22-6"><a href="#cb22-6" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">INDEX</span> idx_zamowienia_idkompozycji <span class="kw">ON</span> zamowienia (idkompozycji);</span>
<span id="cb22-7"><a href="#cb22-7" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">INDEX</span></span>
<span id="cb22-8"><a href="#cb22-8" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">INDEX</span></span>
<span id="cb22-9"><a href="#cb22-9" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">INDEX</span></span>
<span id="cb22-10"><a href="#cb22-10" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb22-11"><a href="#cb22-11" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> </span>
<span id="cb22-12"><a href="#cb22-12" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span> </span>
<span id="cb22-13"><a href="#cb22-13" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia </span>
<span id="cb22-14"><a href="#cb22-14" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idklienta <span class="op">=</span> <span class="st">&#39;msowins&#39;</span> </span>
<span id="cb22-15"><a href="#cb22-15" aria-hidden="true" tabindex="-1"></a>  <span class="kw">AND</span> idodbiorcy <span class="op">=</span> <span class="dv">1</span> </span>
<span id="cb22-16"><a href="#cb22-16" aria-hidden="true" tabindex="-1"></a>  <span class="kw">AND</span> idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>;</span>
<span id="cb22-17"><a href="#cb22-17" aria-hidden="true" tabindex="-1"></a>                                                                  <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                                   </span>
<span id="cb22-18"><a href="#cb22-18" aria-hidden="true" tabindex="-1"></a><span class="co">-----------------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb22-19"><a href="#cb22-19" aria-hidden="true" tabindex="-1"></a> <span class="kw">Bitmap</span> <span class="kw">Heap</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">26</span>.<span class="dv">41</span><span class="op">..</span><span class="fl">30.42</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">058</span><span class="op">..</span><span class="fl">0.059</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb22-20"><a href="#cb22-20" aria-hidden="true" tabindex="-1"></a>   Recheck Cond: (((idklienta):<span class="ch">:text</span> <span class="op">=</span> <span class="st">&#39;msowins&#39;</span>:<span class="ch">:text</span>) <span class="kw">AND</span> (idodbiorcy <span class="op">=</span> <span class="dv">1</span>) <span class="kw">AND</span> (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>))</span>
<span id="cb22-21"><a href="#cb22-21" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Heap</span> Blocks: exact<span class="op">=</span><span class="dv">1</span></span>
<span id="cb22-22"><a href="#cb22-22" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  BitmapAnd  (<span class="kw">cost</span><span class="op">=</span><span class="dv">26</span>.<span class="dv">41</span><span class="op">..</span><span class="fl">26.41</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">055</span><span class="op">..</span><span class="fl">0.055</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">0</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb22-23"><a href="#cb22-23" aria-hidden="true" tabindex="-1"></a>         <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_zamowienia_idklienta  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">6.69</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">321</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">018</span><span class="op">..</span><span class="fl">0.018</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">321</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb22-24"><a href="#cb22-24" aria-hidden="true" tabindex="-1"></a>               <span class="kw">Index</span> Cond: ((idklienta):<span class="ch">:text</span> <span class="op">=</span> <span class="st">&#39;msowins&#39;</span>:<span class="ch">:text</span>)</span>
<span id="cb22-25"><a href="#cb22-25" aria-hidden="true" tabindex="-1"></a>         <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_zamowienia_idodbiorcy  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">8.57</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">572</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">015</span><span class="op">..</span><span class="fl">0.015</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">572</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb22-26"><a href="#cb22-26" aria-hidden="true" tabindex="-1"></a>               <span class="kw">Index</span> Cond: (idodbiorcy <span class="op">=</span> <span class="dv">1</span>)</span>
<span id="cb22-27"><a href="#cb22-27" aria-hidden="true" tabindex="-1"></a>         <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_zamowienia_idkompozycji  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">10.64</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">018</span><span class="op">..</span><span class="fl">0.018</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb22-28"><a href="#cb22-28" aria-hidden="true" tabindex="-1"></a>               <span class="kw">Index</span> Cond: (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb22-29"><a href="#cb22-29" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.211</span> ms</span>
<span id="cb22-30"><a href="#cb22-30" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">0.074</span> ms</span>
<span id="cb22-31"><a href="#cb22-31" aria-hidden="true" tabindex="-1"></a>(<span class="dv">12</span> <span class="kw">rows</span>)</span>
<span id="cb22-32"><a href="#cb22-32" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb22-33"><a href="#cb22-33" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> </span>
<span id="cb22-34"><a href="#cb22-34" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span> </span>
<span id="cb22-35"><a href="#cb22-35" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia </span>
<span id="cb22-36"><a href="#cb22-36" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idklienta <span class="op">=</span> <span class="st">&#39;msowins&#39;</span> </span>
<span id="cb22-37"><a href="#cb22-37" aria-hidden="true" tabindex="-1"></a>  <span class="kw">OR</span> idodbiorcy <span class="op">=</span> <span class="dv">1</span> </span>
<span id="cb22-38"><a href="#cb22-38" aria-hidden="true" tabindex="-1"></a>  <span class="kw">OR</span> idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>;</span>
<span id="cb22-39"><a href="#cb22-39" aria-hidden="true" tabindex="-1"></a>                                                                  <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                                   </span>
<span id="cb22-40"><a href="#cb22-40" aria-hidden="true" tabindex="-1"></a><span class="co">-----------------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb22-41"><a href="#cb22-41" aria-hidden="true" tabindex="-1"></a> <span class="kw">Bitmap</span> <span class="kw">Heap</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">27</span>.<span class="dv">17</span><span class="op">..</span><span class="fl">191.62</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1682</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">061</span><span class="op">..</span><span class="fl">0.357</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1680</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb22-42"><a href="#cb22-42" aria-hidden="true" tabindex="-1"></a>   Recheck Cond: (((idklienta):<span class="ch">:text</span> <span class="op">=</span> <span class="st">&#39;msowins&#39;</span>:<span class="ch">:text</span>) <span class="kw">OR</span> (idodbiorcy <span class="op">=</span> <span class="dv">1</span>) <span class="kw">OR</span> (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>))</span>
<span id="cb22-43"><a href="#cb22-43" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Heap</span> Blocks: exact<span class="op">=</span><span class="dv">134</span></span>
<span id="cb22-44"><a href="#cb22-44" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  BitmapOr  (<span class="kw">cost</span><span class="op">=</span><span class="dv">27</span>.<span class="dv">17</span><span class="op">..</span><span class="fl">27.17</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1740</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">048</span><span class="op">..</span><span class="fl">0.048</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">0</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb22-45"><a href="#cb22-45" aria-hidden="true" tabindex="-1"></a>         <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_zamowienia_idklienta  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">6.69</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">321</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">021</span><span class="op">..</span><span class="fl">0.021</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">321</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb22-46"><a href="#cb22-46" aria-hidden="true" tabindex="-1"></a>               <span class="kw">Index</span> Cond: ((idklienta):<span class="ch">:text</span> <span class="op">=</span> <span class="st">&#39;msowins&#39;</span>:<span class="ch">:text</span>)</span>
<span id="cb22-47"><a href="#cb22-47" aria-hidden="true" tabindex="-1"></a>         <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_zamowienia_idodbiorcy  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">8.57</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">572</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">011</span><span class="op">..</span><span class="fl">0.011</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">572</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb22-48"><a href="#cb22-48" aria-hidden="true" tabindex="-1"></a>               <span class="kw">Index</span> Cond: (idodbiorcy <span class="op">=</span> <span class="dv">1</span>)</span>
<span id="cb22-49"><a href="#cb22-49" aria-hidden="true" tabindex="-1"></a>         <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_zamowienia_idkompozycji  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">10.64</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">015</span><span class="op">..</span><span class="fl">0.015</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb22-50"><a href="#cb22-50" aria-hidden="true" tabindex="-1"></a>               <span class="kw">Index</span> Cond: (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb22-51"><a href="#cb22-51" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.068</span> ms</span>
<span id="cb22-52"><a href="#cb22-52" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">0.420</span> ms</span>
<span id="cb22-53"><a href="#cb22-53" aria-hidden="true" tabindex="-1"></a>(<span class="dv">12</span> <span class="kw">rows</span>)</span>
<span id="cb22-54"><a href="#cb22-54" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb22-55"><a href="#cb22-55" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> </span>
<span id="cb22-56"><a href="#cb22-56" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span> </span>
<span id="cb22-57"><a href="#cb22-57" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia </span>
<span id="cb22-58"><a href="#cb22-58" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>;</span>
<span id="cb22-59"><a href="#cb22-59" aria-hidden="true" tabindex="-1"></a>                                                               <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                                </span>
<span id="cb22-60"><a href="#cb22-60" aria-hidden="true" tabindex="-1"></a><span class="co">-----------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb22-61"><a href="#cb22-61" aria-hidden="true" tabindex="-1"></a> <span class="kw">Bitmap</span> <span class="kw">Heap</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">10</span>.<span class="dv">85</span><span class="op">..</span><span class="fl">155.44</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">040</span><span class="op">..</span><span class="fl">0.287</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb22-62"><a href="#cb22-62" aria-hidden="true" tabindex="-1"></a>   Recheck Cond: (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb22-63"><a href="#cb22-63" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Heap</span> Blocks: exact<span class="op">=</span><span class="dv">134</span></span>
<span id="cb22-64"><a href="#cb22-64" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_zamowienia_idkompozycji  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">10.64</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">028</span><span class="op">..</span><span class="fl">0.028</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">847</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb22-65"><a href="#cb22-65" aria-hidden="true" tabindex="-1"></a>         <span class="kw">Index</span> Cond: (idkompozycji <span class="op">=</span> <span class="st">&#39;buk1&#39;</span>:<span class="ch">:bpchar</span>)</span>
<span id="cb22-66"><a href="#cb22-66" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.077</span> ms</span>
<span id="cb22-67"><a href="#cb22-67" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">0.327</span> ms</span>
<span id="cb22-68"><a href="#cb22-68" aria-hidden="true" tabindex="-1"></a>(<span class="dv">7</span> <span class="kw">rows</span>)</span></code></pre></div>
<p><strong>Odp: Wyniki poprawiły się dla Select’a z samym
<code>buk1</code>, natomiast jest gorzej w przypadku pozostałych
sytuacji</strong></p>
<p>Nie usuwaj indeksów przed następnym ćwiczeniem.</p>
<h2 id="indeksy-a-sortowanie">Indeksy a sortowanie</h2>
<p>Wykonaj zapytanie, które zwraca wszystkie zamówienia posortowane
według ID kompozycji. Czy indeks został wykorzystany do wyszukiwania?
Czy było konieczne osobne uruchomienie algorytmu sortowania?</p>
<div class="sourceCode" id="cb23"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb23-1"><a href="#cb23-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span></span>
<span id="cb23-2"><a href="#cb23-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span></span>
<span id="cb23-3"><a href="#cb23-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia</span>
<span id="cb23-4"><a href="#cb23-4" aria-hidden="true" tabindex="-1"></a><span class="kw">ORDER</span> <span class="kw">BY</span> idkompozycji;</span>
<span id="cb23-5"><a href="#cb23-5" aria-hidden="true" tabindex="-1"></a>                                                                    <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                                     </span>
<span id="cb23-6"><a href="#cb23-6" aria-hidden="true" tabindex="-1"></a><span class="co">---------------------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb23-7"><a href="#cb23-7" aria-hidden="true" tabindex="-1"></a> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">using</span> idx_zamowienia_idkompozycji <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">29</span><span class="op">..</span><span class="fl">843.52</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">16015</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">008</span><span class="op">..</span><span class="fl">2.638</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">16015</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb23-8"><a href="#cb23-8" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.075</span> ms</span>
<span id="cb23-9"><a href="#cb23-9" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">3.075</span> ms</span>
<span id="cb23-10"><a href="#cb23-10" aria-hidden="true" tabindex="-1"></a>(<span class="dv">3</span> <span class="kw">rows</span>)</span></code></pre></div>
<p><strong>Odp. Tak, indeks został wykorzystany, ponieważ w wyniku
widzimy <code>Index Scan</code>. Algorytm sortowania nie był potrzebny,
ponieważ w planie nie ma sekcji <code>sort</code></strong></p>
<div class="sourceCode" id="cb24"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb24-1"><a href="#cb24-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">DROP</span> <span class="kw">INDEX</span> <span class="cf">IF</span> <span class="kw">EXISTS</span> idx_zamowienia_idkompozycji;</span>
<span id="cb24-2"><a href="#cb24-2" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> <span class="kw">INDEX</span></span>
<span id="cb24-3"><a href="#cb24-3" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span>                                  </span>
<span id="cb24-4"><a href="#cb24-4" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span></span>
<span id="cb24-5"><a href="#cb24-5" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia</span>
<span id="cb24-6"><a href="#cb24-6" aria-hidden="true" tabindex="-1"></a><span class="kw">ORDER</span> <span class="kw">BY</span> idkompozycji;</span>
<span id="cb24-7"><a href="#cb24-7" aria-hidden="true" tabindex="-1"></a>                                                              <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                               </span>
<span id="cb24-8"><a href="#cb24-8" aria-hidden="true" tabindex="-1"></a><span class="co">---------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb24-9"><a href="#cb24-9" aria-hidden="true" tabindex="-1"></a> <span class="kw">Sort</span>  (<span class="kw">cost</span><span class="op">=</span><span class="dv">10000001412</span>.<span class="dv">57</span><span class="op">..</span><span class="fl">10000001452.61</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">16015</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">4</span>.<span class="dv">019</span><span class="op">..</span><span class="fl">4.567</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">16015</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb24-10"><a href="#cb24-10" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Sort</span> <span class="kw">Key</span>: idkompozycji</span>
<span id="cb24-11"><a href="#cb24-11" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Sort</span> <span class="kw">Method</span>: quicksort  Memory: 1386kB</span>
<span id="cb24-12"><a href="#cb24-12" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  Seq <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">10000000000</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">10000000294.15</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">16015</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">003</span><span class="op">..</span><span class="fl">0.713</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">16015</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb24-13"><a href="#cb24-13" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.079</span> ms</span>
<span id="cb24-14"><a href="#cb24-14" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">5.011</span> ms</span>
<span id="cb24-15"><a href="#cb24-15" aria-hidden="true" tabindex="-1"></a>(<span class="dv">6</span> <span class="kw">rows</span>)</span></code></pre></div>
<p><strong>Tak, tym razem było sortowanie</strong></p>
<p>Usuń wszystkie indeksy.</p>
<pre><code>kjlazarz=&gt; SELECT indexname, tablename
FROM pg_indexes
WHERE schemaname = &#39;lab-1&#39;;
         indexname         | tablename  
---------------------------+------------
 idx_zamowienia_idklienta  | zamowienia
 idx_zamowienia_idodbiorcy | zamowienia
(2 rows)</code></pre>
<div class="sourceCode" id="cb26"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb26-1"><a href="#cb26-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> DO $$ </span>
<span id="cb26-2"><a href="#cb26-2" aria-hidden="true" tabindex="-1"></a><span class="kw">DECLARE</span> </span>
<span id="cb26-3"><a href="#cb26-3" aria-hidden="true" tabindex="-1"></a>    r <span class="dt">RECORD</span>; </span>
<span id="cb26-4"><a href="#cb26-4" aria-hidden="true" tabindex="-1"></a><span class="cf">BEGIN</span> </span>
<span id="cb26-5"><a href="#cb26-5" aria-hidden="true" tabindex="-1"></a>    <span class="cf">FOR</span> r <span class="kw">IN</span> <span class="kw">SELECT</span> indexname <span class="kw">FROM</span> pg_indexes <span class="kw">WHERE</span> schemaname <span class="op">=</span> <span class="st">&#39;lab-1&#39;</span> <span class="cf">LOOP</span> </span>
<span id="cb26-6"><a href="#cb26-6" aria-hidden="true" tabindex="-1"></a>        <span class="kw">EXECUTE</span> format(<span class="st">&#39;DROP INDEX IF EXISTS %I;&#39;</span>, r.indexname); </span>
<span id="cb26-7"><a href="#cb26-7" aria-hidden="true" tabindex="-1"></a>    <span class="cf">END</span> <span class="cf">LOOP</span>; </span>
<span id="cb26-8"><a href="#cb26-8" aria-hidden="true" tabindex="-1"></a><span class="cf">END</span> $$;</span>
<span id="cb26-9"><a href="#cb26-9" aria-hidden="true" tabindex="-1"></a>DO</span></code></pre></div>
<div class="sourceCode" id="cb27"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb27-1"><a href="#cb27-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">SELECT</span> indexname, tablename</span>
<span id="cb27-2"><a href="#cb27-2" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> pg_indexes</span>
<span id="cb27-3"><a href="#cb27-3" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> schemaname <span class="op">=</span> <span class="st">&#39;lab-1&#39;</span>;</span>
<span id="cb27-4"><a href="#cb27-4" aria-hidden="true" tabindex="-1"></a> indexname | tablename </span>
<span id="cb27-5"><a href="#cb27-5" aria-hidden="true" tabindex="-1"></a><span class="co">-----------+-----------</span></span>
<span id="cb27-6"><a href="#cb27-6" aria-hidden="true" tabindex="-1"></a>(<span class="dv">0</span> <span class="kw">rows</span>)</span></code></pre></div>
<h2 id="indeksy-częściowe">Indeksy częściowe</h2>
<p>Utwórz indeks na kolumnie idklienta, ale tylko dla zamówień
zapłaconych. Sprawdź jego działanie, pobierając wszystkie zapłacone
zamówienia wybranego klienta. Powtórz zapytanie dla zamówień
niezapłaconych.</p>
<div class="sourceCode" id="cb28"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb28-1"><a href="#cb28-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">CREATE</span> <span class="kw">INDEX</span> idx_zamowienia_idklienta_zaplacone <span class="kw">ON</span> zamowienia (idklienta)</span>
<span id="cb28-2"><a href="#cb28-2" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> zaplacone <span class="op">=</span> <span class="kw">true</span>;</span>
<span id="cb28-3"><a href="#cb28-3" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">INDEX</span></span></code></pre></div>
<div class="sourceCode" id="cb29"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb29-1"><a href="#cb29-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span></span>
<span id="cb29-2"><a href="#cb29-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span></span>
<span id="cb29-3"><a href="#cb29-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia</span>
<span id="cb29-4"><a href="#cb29-4" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idklienta <span class="op">=</span> <span class="st">&#39;msowins&#39;</span> <span class="kw">AND</span> zaplacone <span class="op">=</span> <span class="kw">true</span>;</span>
<span id="cb29-5"><a href="#cb29-5" aria-hidden="true" tabindex="-1"></a>                                                                  <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                                   </span>
<span id="cb29-6"><a href="#cb29-6" aria-hidden="true" tabindex="-1"></a><span class="co">-----------------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb29-7"><a href="#cb29-7" aria-hidden="true" tabindex="-1"></a> <span class="kw">Bitmap</span> <span class="kw">Heap</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">6</span>.<span class="dv">77</span><span class="op">..</span><span class="fl">144.79</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">321</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">028</span><span class="op">..</span><span class="fl">0.153</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">320</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb29-8"><a href="#cb29-8" aria-hidden="true" tabindex="-1"></a>   Recheck Cond: (((idklienta):<span class="ch">:text</span> <span class="op">=</span> <span class="st">&#39;msowins&#39;</span>:<span class="ch">:text</span>) <span class="kw">AND</span> zaplacone)</span>
<span id="cb29-9"><a href="#cb29-9" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Heap</span> Blocks: exact<span class="op">=</span><span class="dv">123</span></span>
<span id="cb29-10"><a href="#cb29-10" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> idx_zamowienia_idklienta_zaplacone  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">6.69</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">321</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">018</span><span class="op">..</span><span class="fl">0.018</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">320</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb29-11"><a href="#cb29-11" aria-hidden="true" tabindex="-1"></a>         <span class="kw">Index</span> Cond: ((idklienta):<span class="ch">:text</span> <span class="op">=</span> <span class="st">&#39;msowins&#39;</span>:<span class="ch">:text</span>)</span>
<span id="cb29-12"><a href="#cb29-12" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.166</span> ms</span>
<span id="cb29-13"><a href="#cb29-13" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">0.176</span> ms</span>
<span id="cb29-14"><a href="#cb29-14" aria-hidden="true" tabindex="-1"></a>(<span class="dv">7</span> <span class="kw">rows</span>)</span></code></pre></div>
<div class="sourceCode" id="cb30"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb30-1"><a href="#cb30-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span></span>
<span id="cb30-2"><a href="#cb30-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span></span>
<span id="cb30-3"><a href="#cb30-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia</span>
<span id="cb30-4"><a href="#cb30-4" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> idklienta <span class="op">=</span> <span class="st">&#39;msowins&#39;</span> <span class="kw">AND</span> zaplacone <span class="op">=</span> <span class="kw">false</span>;</span>
<span id="cb30-5"><a href="#cb30-5" aria-hidden="true" tabindex="-1"></a>                                                        <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                         </span>
<span id="cb30-6"><a href="#cb30-6" aria-hidden="true" tabindex="-1"></a><span class="co">---------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb30-7"><a href="#cb30-7" aria-hidden="true" tabindex="-1"></a> Seq <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">10000000000</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">10000000334.19</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> width<span class="op">=</span><span class="dv">52</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">24</span>.<span class="dv">845</span><span class="op">..</span><span class="fl">25.344</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb30-8"><a href="#cb30-8" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Filter</span>: ((<span class="kw">NOT</span> zaplacone) <span class="kw">AND</span> ((idklienta):<span class="ch">:text</span> <span class="op">=</span> <span class="st">&#39;msowins&#39;</span>:<span class="ch">:text</span>))</span>
<span id="cb30-9"><a href="#cb30-9" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Rows</span> Removed <span class="kw">by</span> <span class="kw">Filter</span>: <span class="dv">16014</span></span>
<span id="cb30-10"><a href="#cb30-10" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.055</span> ms</span>
<span id="cb30-11"><a href="#cb30-11" aria-hidden="true" tabindex="-1"></a> JIT:</span>
<span id="cb30-12"><a href="#cb30-12" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Functions</span>: <span class="dv">2</span></span>
<span id="cb30-13"><a href="#cb30-13" aria-hidden="true" tabindex="-1"></a>   Options: Inlining <span class="kw">true</span>, Optimization <span class="kw">true</span>, Expressions <span class="kw">true</span>, Deforming <span class="kw">true</span></span>
<span id="cb30-14"><a href="#cb30-14" aria-hidden="true" tabindex="-1"></a>   Timing: Generation <span class="fl">0.589</span> ms, Inlining <span class="fl">5.464</span> ms, Optimization <span class="fl">12.868</span> ms, Emission <span class="fl">6.405</span> ms, Total <span class="fl">25.325</span> ms</span>
<span id="cb30-15"><a href="#cb30-15" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">25.969</span> ms</span>
<span id="cb30-16"><a href="#cb30-16" aria-hidden="true" tabindex="-1"></a>(<span class="dv">9</span> <span class="kw">rows</span>)</span></code></pre></div>
<p>Następnie oblicz sumę wszystkich zamówień niezapłaconych. Czy
zapytanie korzysta z indeksu?</p>
<div class="sourceCode" id="cb31"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb31-1"><a href="#cb31-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span></span>
<span id="cb31-2"><a href="#cb31-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="fu">SUM</span>(cena)</span>
<span id="cb31-3"><a href="#cb31-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> zamowienia</span>
<span id="cb31-4"><a href="#cb31-4" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> zaplacone <span class="op">=</span> <span class="kw">false</span>;</span>
<span id="cb31-5"><a href="#cb31-5" aria-hidden="true" tabindex="-1"></a>                                                           <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                           </span>
<span id="cb31-6"><a href="#cb31-6" aria-hidden="true" tabindex="-1"></a><span class="co">--------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb31-7"><a href="#cb31-7" aria-hidden="true" tabindex="-1"></a> Aggregate  (<span class="kw">cost</span><span class="op">=</span><span class="dv">10000000294</span>.<span class="dv">17</span><span class="op">..</span><span class="fl">10000000294.18</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> width<span class="op">=</span><span class="dv">32</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">37</span>.<span class="dv">776</span><span class="op">..</span><span class="fl">37.777</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb31-8"><a href="#cb31-8" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  Seq <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">10000000000</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">10000000294.15</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">7</span> width<span class="op">=</span><span class="dv">5</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">37</span>.<span class="dv">263</span><span class="op">..</span><span class="fl">37.766</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">7</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb31-9"><a href="#cb31-9" aria-hidden="true" tabindex="-1"></a>         <span class="kw">Filter</span>: (<span class="kw">NOT</span> zaplacone)</span>
<span id="cb31-10"><a href="#cb31-10" aria-hidden="true" tabindex="-1"></a>         <span class="kw">Rows</span> Removed <span class="kw">by</span> <span class="kw">Filter</span>: <span class="dv">16008</span></span>
<span id="cb31-11"><a href="#cb31-11" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.064</span> ms</span>
<span id="cb31-12"><a href="#cb31-12" aria-hidden="true" tabindex="-1"></a> JIT:</span>
<span id="cb31-13"><a href="#cb31-13" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Functions</span>: <span class="dv">5</span></span>
<span id="cb31-14"><a href="#cb31-14" aria-hidden="true" tabindex="-1"></a>   Options: Inlining <span class="kw">true</span>, Optimization <span class="kw">true</span>, Expressions <span class="kw">true</span>, Deforming <span class="kw">true</span></span>
<span id="cb31-15"><a href="#cb31-15" aria-hidden="true" tabindex="-1"></a>   Timing: Generation <span class="fl">0.433</span> ms, Inlining <span class="fl">4.486</span> ms, Optimization <span class="fl">19.677</span> ms, Emission <span class="fl">12.986</span> ms, Total <span class="fl">37.582</span> ms</span>
<span id="cb31-16"><a href="#cb31-16" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">38.252</span> ms</span>
<span id="cb31-17"><a href="#cb31-17" aria-hidden="true" tabindex="-1"></a>(<span class="dv">10</span> <span class="kw">rows</span>)</span></code></pre></div>
<p><strong>Odp. Nie, zapytanie nie kożysta z indeksu</strong></p>
<p>Przed rozpoczęciem kolejnego ćwiczenia usuń indeks.</p>
<div class="sourceCode" id="cb32"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb32-1"><a href="#cb32-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">DROP</span> <span class="kw">INDEX</span> <span class="cf">IF</span> <span class="kw">EXISTS</span> idx_zamowienia_idklienta_zaplacone;</span>
<span id="cb32-2"><a href="#cb32-2" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> <span class="kw">INDEX</span></span></code></pre></div>
<h2 id="indeksy-na-wyrażeniach">Indeksy na wyrażeniach</h2>
<p>Utwórz indeks oraz zapytanie, które pozwolą na wyszukiwanie klientów
z miasta zaczynającego się od określonego ciągu znaków (np. „krak”),
niezależnie od wielkości liter (czyli „krak” = „Krak” = „KRAK”,
itd.).</p>
<div class="sourceCode" id="cb33"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb33-1"><a href="#cb33-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">CREATE</span> <span class="kw">INDEX</span> idx_klienci_miasto_lower <span class="kw">ON</span> klienci (<span class="fu">LOWER</span>(miasto));</span>
<span id="cb33-2"><a href="#cb33-2" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">INDEX</span></span></code></pre></div>
<div class="sourceCode" id="cb34"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb34-1"><a href="#cb34-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span></span>
<span id="cb34-2"><a href="#cb34-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span></span>
<span id="cb34-3"><a href="#cb34-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> klienci</span>
<span id="cb34-4"><a href="#cb34-4" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> <span class="fu">LOWER</span>(miasto) ILIKE <span class="st">&#39;krak%&#39;</span>;</span>
<span id="cb34-5"><a href="#cb34-5" aria-hidden="true" tabindex="-1"></a>                                                        <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                        </span>
<span id="cb34-6"><a href="#cb34-6" aria-hidden="true" tabindex="-1"></a><span class="co">--------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb34-7"><a href="#cb34-7" aria-hidden="true" tabindex="-1"></a> Seq <span class="kw">Scan</span> <span class="kw">on</span> klienci  (<span class="kw">cost</span><span class="op">=</span><span class="dv">10000000000</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">10000000001.75</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> width<span class="op">=</span><span class="dv">692</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">47</span>.<span class="dv">342</span><span class="op">..</span><span class="fl">47.363</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">23</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb34-8"><a href="#cb34-8" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Filter</span>: (<span class="fu">lower</span>((miasto):<span class="ch">:text</span>) ~~<span class="op">*</span> <span class="st">&#39;krak%&#39;</span>:<span class="ch">:text</span>)</span>
<span id="cb34-9"><a href="#cb34-9" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Rows</span> Removed <span class="kw">by</span> <span class="kw">Filter</span>: <span class="dv">27</span></span>
<span id="cb34-10"><a href="#cb34-10" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.270</span> ms</span>
<span id="cb34-11"><a href="#cb34-11" aria-hidden="true" tabindex="-1"></a> JIT:</span>
<span id="cb34-12"><a href="#cb34-12" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Functions</span>: <span class="dv">2</span></span>
<span id="cb34-13"><a href="#cb34-13" aria-hidden="true" tabindex="-1"></a>   Options: Inlining <span class="kw">true</span>, Optimization <span class="kw">true</span>, Expressions <span class="kw">true</span>, Deforming <span class="kw">true</span></span>
<span id="cb34-14"><a href="#cb34-14" aria-hidden="true" tabindex="-1"></a>   Timing: Generation <span class="fl">0.194</span> ms, Inlining <span class="fl">32.493</span> ms, Optimization <span class="fl">8.807</span> ms, Emission <span class="fl">5.917</span> ms, Total <span class="fl">47.412</span> ms</span>
<span id="cb34-15"><a href="#cb34-15" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">68.020</span> ms</span>
<span id="cb34-16"><a href="#cb34-16" aria-hidden="true" tabindex="-1"></a>(<span class="dv">9</span> <span class="kw">rows</span>)</span></code></pre></div>
<p><strong>Nie działa, ale zastosujemy tę samą sztuczkę co
wyżej</strong></p>
<div class="sourceCode" id="cb35"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb35-1"><a href="#cb35-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">CREATE</span> <span class="kw">INDEX</span> idx_klienci_miasto_pattern <span class="kw">ON</span> klienci (miasto varchar_pattern_ops);</span>
<span id="cb35-2"><a href="#cb35-2" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">INDEX</span></span></code></pre></div>
<div class="sourceCode" id="cb36"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb36-1"><a href="#cb36-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span></span>
<span id="cb36-2"><a href="#cb36-2" aria-hidden="true" tabindex="-1"></a><span class="kw">SELECT</span> <span class="op">*</span></span>
<span id="cb36-3"><a href="#cb36-3" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> klienci</span>
<span id="cb36-4"><a href="#cb36-4" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> <span class="fu">LOWER</span>(miasto) ILIKE <span class="st">&#39;krak%&#39;</span>;</span>
<span id="cb36-5"><a href="#cb36-5" aria-hidden="true" tabindex="-1"></a>                                                        <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                        </span>
<span id="cb36-6"><a href="#cb36-6" aria-hidden="true" tabindex="-1"></a><span class="co">--------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb36-7"><a href="#cb36-7" aria-hidden="true" tabindex="-1"></a> Seq <span class="kw">Scan</span> <span class="kw">on</span> klienci  (<span class="kw">cost</span><span class="op">=</span><span class="dv">10000000000</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">10000000001.75</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">1</span> width<span class="op">=</span><span class="dv">692</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">18</span>.<span class="dv">358</span><span class="op">..</span><span class="fl">18.379</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">23</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb36-8"><a href="#cb36-8" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Filter</span>: (<span class="fu">lower</span>((miasto):<span class="ch">:text</span>) ~~<span class="op">*</span> <span class="st">&#39;krak%&#39;</span>:<span class="ch">:text</span>)</span>
<span id="cb36-9"><a href="#cb36-9" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Rows</span> Removed <span class="kw">by</span> <span class="kw">Filter</span>: <span class="dv">27</span></span>
<span id="cb36-10"><a href="#cb36-10" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.179</span> ms</span>
<span id="cb36-11"><a href="#cb36-11" aria-hidden="true" tabindex="-1"></a> JIT:</span>
<span id="cb36-12"><a href="#cb36-12" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Functions</span>: <span class="dv">2</span></span>
<span id="cb36-13"><a href="#cb36-13" aria-hidden="true" tabindex="-1"></a>   Options: Inlining <span class="kw">true</span>, Optimization <span class="kw">true</span>, Expressions <span class="kw">true</span>, Deforming <span class="kw">true</span></span>
<span id="cb36-14"><a href="#cb36-14" aria-hidden="true" tabindex="-1"></a>   Timing: Generation <span class="fl">0.249</span> ms, Inlining <span class="fl">4.647</span> ms, Optimization <span class="fl">8.572</span> ms, Emission <span class="fl">5.042</span> ms, Total <span class="fl">18.509</span> ms</span>
<span id="cb36-15"><a href="#cb36-15" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">18.659</span> ms</span>
<span id="cb36-16"><a href="#cb36-16" aria-hidden="true" tabindex="-1"></a>(<span class="dv">9</span> <span class="kw">rows</span>)</span></code></pre></div>
<p><strong>Nadal nie działa :/</strong></p>
<p>Przed rozpoczęciem kolejnego ćwiczenia usuń indeks.</p>
<div class="sourceCode" id="cb37"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb37-1"><a href="#cb37-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">SELECT</span> indexname, tablename</span>
<span id="cb37-2"><a href="#cb37-2" aria-hidden="true" tabindex="-1"></a><span class="kw">FROM</span> pg_indexes</span>
<span id="cb37-3"><a href="#cb37-3" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> schemaname <span class="op">=</span> <span class="st">&#39;lab-1&#39;</span>;</span>
<span id="cb37-4"><a href="#cb37-4" aria-hidden="true" tabindex="-1"></a>         indexname          | tablename </span>
<span id="cb37-5"><a href="#cb37-5" aria-hidden="true" tabindex="-1"></a><span class="co">----------------------------+-----------</span></span>
<span id="cb37-6"><a href="#cb37-6" aria-hidden="true" tabindex="-1"></a> idx_klienci_miasto_lower   | klienci</span>
<span id="cb37-7"><a href="#cb37-7" aria-hidden="true" tabindex="-1"></a> idx_klienci_miasto_pattern | klienci</span>
<span id="cb37-8"><a href="#cb37-8" aria-hidden="true" tabindex="-1"></a>(<span class="dv">2</span> <span class="kw">rows</span>)</span>
<span id="cb37-9"><a href="#cb37-9" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb37-10"><a href="#cb37-10" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">DROP</span> <span class="kw">INDEX</span> <span class="cf">IF</span> <span class="kw">EXISTS</span> idx_klienci_miasto_lower;</span>
<span id="cb37-11"><a href="#cb37-11" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> <span class="kw">INDEX</span> <span class="cf">IF</span> <span class="kw">EXISTS</span> idx_klienci_miasto_pattern;</span>
<span id="cb37-12"><a href="#cb37-12" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> <span class="kw">INDEX</span></span>
<span id="cb37-13"><a href="#cb37-13" aria-hidden="true" tabindex="-1"></a><span class="kw">DROP</span> <span class="kw">INDEX</span></span></code></pre></div>
<h2 id="indeksy-gist">Indeksy GiST</h2>
<p>Dodaj do tabeli zamowienia kolumnę lokalizacja typu point (będzie
zawierać współrzędne miejsca dostawy) i wypełnij ją punktami o losowych
współrzędnych na płaszczyźnie (0,0)–(100,100):</p>
<div class="sourceCode" id="cb38"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb38-1"><a href="#cb38-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">ALTER</span> <span class="kw">TABLE</span> zamowienia <span class="kw">ADD</span> <span class="kw">COLUMN</span> lokalizacja point;</span>
<span id="cb38-2"><a href="#cb38-2" aria-hidden="true" tabindex="-1"></a><span class="kw">UPDATE</span> zamowienia <span class="kw">SET</span> lokalizacja<span class="op">=</span>point(<span class="kw">random</span>()<span class="op">*</span><span class="dv">100</span>, <span class="kw">random</span>()<span class="op">*</span><span class="dv">100</span>);</span>
<span id="cb38-3"><a href="#cb38-3" aria-hidden="true" tabindex="-1"></a><span class="kw">ALTER</span> <span class="kw">TABLE</span></span>
<span id="cb38-4"><a href="#cb38-4" aria-hidden="true" tabindex="-1"></a><span class="kw">UPDATE</span> <span class="dv">16015</span></span></code></pre></div>
<p>Napisz zapytania, wykorzystując wbudowane funkcje/operatory
analizujące geometrie PostgreSQL, które:</p>
<ul>
<li>zwrócą zamówienia, których miejsca dostaw są nie dalej niż 10
jednostek od centrum miasta (punkt (50, 50))</li>
</ul>
<div class="sourceCode" id="cb39"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb39-1"><a href="#cb39-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> <span class="kw">SELECT</span> <span class="op">*</span> <span class="kw">FROM</span> zamowienia</span>
<span id="cb39-2"><a href="#cb39-2" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> lokalizacja <span class="op">&lt;</span>@ circle <span class="st">&#39;((50, 50), 10)&#39;</span>;</span>
<span id="cb39-3"><a href="#cb39-3" aria-hidden="true" tabindex="-1"></a>                                                          <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                          </span>
<span id="cb39-4"><a href="#cb39-4" aria-hidden="true" tabindex="-1"></a><span class="co">------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb39-5"><a href="#cb39-5" aria-hidden="true" tabindex="-1"></a> Seq <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">10000000000</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">10000000516.19</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">16</span> width<span class="op">=</span><span class="dv">68</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">24</span>.<span class="dv">219</span><span class="op">..</span><span class="fl">24.987</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">512</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb39-6"><a href="#cb39-6" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Filter</span>: (lokalizacja <span class="op">&lt;</span>@ <span class="st">&#39;&lt;(50,50),10&gt;&#39;</span>:<span class="ch">:circle</span>)</span>
<span id="cb39-7"><a href="#cb39-7" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Rows</span> Removed <span class="kw">by</span> <span class="kw">Filter</span>: <span class="dv">15503</span></span>
<span id="cb39-8"><a href="#cb39-8" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.036</span> ms</span>
<span id="cb39-9"><a href="#cb39-9" aria-hidden="true" tabindex="-1"></a> JIT:</span>
<span id="cb39-10"><a href="#cb39-10" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Functions</span>: <span class="dv">2</span></span>
<span id="cb39-11"><a href="#cb39-11" aria-hidden="true" tabindex="-1"></a>   Options: Inlining <span class="kw">true</span>, Optimization <span class="kw">true</span>, Expressions <span class="kw">true</span>, Deforming <span class="kw">true</span></span>
<span id="cb39-12"><a href="#cb39-12" aria-hidden="true" tabindex="-1"></a>   Timing: Generation <span class="fl">0.287</span> ms, Inlining <span class="fl">1.782</span> ms, Optimization <span class="fl">13.344</span> ms, Emission <span class="fl">8.950</span> ms, Total <span class="fl">24.362</span> ms</span>
<span id="cb39-13"><a href="#cb39-13" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">25.320</span> ms</span>
<span id="cb39-14"><a href="#cb39-14" aria-hidden="true" tabindex="-1"></a>(<span class="dv">9</span> <span class="kw">rows</span>)</span></code></pre></div>
<ul>
<li>zwrócą wszystkie zamówienia dla północno-zachodniej ćwiartki
miasta.</li>
</ul>
<div class="sourceCode" id="cb40"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb40-1"><a href="#cb40-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> <span class="kw">SELECT</span> <span class="op">*</span> <span class="kw">FROM</span> zamowienia</span>
<span id="cb40-2"><a href="#cb40-2" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> lokalizacja <span class="op">&lt;</span>@ box <span class="st">&#39;((0, 100), (50, 50))&#39;</span>;</span>
<span id="cb40-3"><a href="#cb40-3" aria-hidden="true" tabindex="-1"></a>                                                          <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                           </span>
<span id="cb40-4"><a href="#cb40-4" aria-hidden="true" tabindex="-1"></a><span class="co">-------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb40-5"><a href="#cb40-5" aria-hidden="true" tabindex="-1"></a> Seq <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">10000000000</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">10000000516.19</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">16</span> width<span class="op">=</span><span class="dv">68</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">21</span>.<span class="dv">992</span><span class="op">..</span><span class="fl">22.825</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">3999</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb40-6"><a href="#cb40-6" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Filter</span>: (lokalizacja <span class="op">&lt;</span>@ <span class="st">&#39;(50,100),(0,50)&#39;</span>:<span class="ch">:box</span>)</span>
<span id="cb40-7"><a href="#cb40-7" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Rows</span> Removed <span class="kw">by</span> <span class="kw">Filter</span>: <span class="dv">12016</span></span>
<span id="cb40-8"><a href="#cb40-8" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.064</span> ms</span>
<span id="cb40-9"><a href="#cb40-9" aria-hidden="true" tabindex="-1"></a> JIT:</span>
<span id="cb40-10"><a href="#cb40-10" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Functions</span>: <span class="dv">2</span></span>
<span id="cb40-11"><a href="#cb40-11" aria-hidden="true" tabindex="-1"></a>   Options: Inlining <span class="kw">true</span>, Optimization <span class="kw">true</span>, Expressions <span class="kw">true</span>, Deforming <span class="kw">true</span></span>
<span id="cb40-12"><a href="#cb40-12" aria-hidden="true" tabindex="-1"></a>   Timing: Generation <span class="fl">0.299</span> ms, Inlining <span class="fl">1.687</span> ms, Optimization <span class="fl">12.649</span> ms, Emission <span class="fl">7.524</span> ms, Total <span class="fl">22.159</span> ms</span>
<span id="cb40-13"><a href="#cb40-13" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">23.284</span> ms</span>
<span id="cb40-14"><a href="#cb40-14" aria-hidden="true" tabindex="-1"></a>(<span class="dv">9</span> <span class="kw">rows</span>)</span></code></pre></div>
<p>Utwórz indeks typu GiST wspierający powyższe zapytania i powtórz
analizę planu oraz pomiar czasu.</p>
<div class="sourceCode" id="cb41"><pre
class="sourceCode sql"><code class="sourceCode sql"><span id="cb41-1"><a href="#cb41-1" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">CREATE</span> <span class="kw">INDEX</span> <span class="kw">ON</span> zamowienia <span class="kw">USING</span> GIST (lokalizacja);</span>
<span id="cb41-2"><a href="#cb41-2" aria-hidden="true" tabindex="-1"></a><span class="kw">CREATE</span> <span class="kw">INDEX</span></span>
<span id="cb41-3"><a href="#cb41-3" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb41-4"><a href="#cb41-4" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> <span class="kw">SELECT</span> <span class="op">*</span> <span class="kw">FROM</span> zamowienia</span>
<span id="cb41-5"><a href="#cb41-5" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> lokalizacja <span class="op">&lt;</span>@ circle <span class="st">&#39;((50, 50), 10)&#39;</span>;</span>
<span id="cb41-6"><a href="#cb41-6" aria-hidden="true" tabindex="-1"></a>                                                              <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                              </span>
<span id="cb41-7"><a href="#cb41-7" aria-hidden="true" tabindex="-1"></a><span class="co">--------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb41-8"><a href="#cb41-8" aria-hidden="true" tabindex="-1"></a> <span class="kw">Bitmap</span> <span class="kw">Heap</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">4</span>.<span class="dv">40</span><span class="op">..</span><span class="fl">57.80</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">16</span> width<span class="op">=</span><span class="dv">68</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">092</span><span class="op">..</span><span class="fl">0.268</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">512</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb41-9"><a href="#cb41-9" aria-hidden="true" tabindex="-1"></a>   Recheck Cond: (lokalizacja <span class="op">&lt;</span>@ <span class="st">&#39;&lt;(50,50),10&gt;&#39;</span>:<span class="ch">:circle</span>)</span>
<span id="cb41-10"><a href="#cb41-10" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Heap</span> Blocks: exact<span class="op">=</span><span class="dv">172</span></span>
<span id="cb41-11"><a href="#cb41-11" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia_lokalizacja_idx  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">4.40</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">16</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">078</span><span class="op">..</span><span class="fl">0.078</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">512</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb41-12"><a href="#cb41-12" aria-hidden="true" tabindex="-1"></a>         <span class="kw">Index</span> Cond: (lokalizacja <span class="op">&lt;</span>@ <span class="st">&#39;&lt;(50,50),10&gt;&#39;</span>:<span class="ch">:circle</span>)</span>
<span id="cb41-13"><a href="#cb41-13" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.048</span> ms</span>
<span id="cb41-14"><a href="#cb41-14" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">0.301</span> ms</span>
<span id="cb41-15"><a href="#cb41-15" aria-hidden="true" tabindex="-1"></a>(<span class="dv">7</span> <span class="kw">rows</span>)</span>
<span id="cb41-16"><a href="#cb41-16" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb41-17"><a href="#cb41-17" aria-hidden="true" tabindex="-1"></a>kjlazarz<span class="op">=&gt;</span> <span class="kw">EXPLAIN</span> <span class="kw">ANALYZE</span> <span class="kw">SELECT</span> <span class="op">*</span> <span class="kw">FROM</span> zamowienia</span>
<span id="cb41-18"><a href="#cb41-18" aria-hidden="true" tabindex="-1"></a><span class="kw">WHERE</span> lokalizacja <span class="op">&lt;</span>@ box <span class="st">&#39;((0, 100), (50, 50))&#39;</span>;</span>
<span id="cb41-19"><a href="#cb41-19" aria-hidden="true" tabindex="-1"></a>                                                              <span class="kw">QUERY</span> <span class="kw">PLAN</span>                                                               </span>
<span id="cb41-20"><a href="#cb41-20" aria-hidden="true" tabindex="-1"></a><span class="co">---------------------------------------------------------------------------------------------------------------------------------------</span></span>
<span id="cb41-21"><a href="#cb41-21" aria-hidden="true" tabindex="-1"></a> <span class="kw">Bitmap</span> <span class="kw">Heap</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia  (<span class="kw">cost</span><span class="op">=</span><span class="dv">4</span>.<span class="dv">40</span><span class="op">..</span><span class="fl">57.80</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">16</span> width<span class="op">=</span><span class="dv">68</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">187</span><span class="op">..</span><span class="fl">0.684</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">3999</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb41-22"><a href="#cb41-22" aria-hidden="true" tabindex="-1"></a>   Recheck Cond: (lokalizacja <span class="op">&lt;</span>@ <span class="st">&#39;(50,100),(0,50)&#39;</span>:<span class="ch">:box</span>)</span>
<span id="cb41-23"><a href="#cb41-23" aria-hidden="true" tabindex="-1"></a>   <span class="kw">Heap</span> Blocks: exact<span class="op">=</span><span class="dv">183</span></span>
<span id="cb41-24"><a href="#cb41-24" aria-hidden="true" tabindex="-1"></a>   <span class="op">-&gt;</span>  <span class="kw">Bitmap</span> <span class="kw">Index</span> <span class="kw">Scan</span> <span class="kw">on</span> zamowienia_lokalizacja_idx  (<span class="kw">cost</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">00</span><span class="op">..</span><span class="fl">4.40</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">16</span> width<span class="op">=</span><span class="dv">0</span>) (actual <span class="dt">time</span><span class="op">=</span><span class="dv">0</span>.<span class="dv">171</span><span class="op">..</span><span class="fl">0.171</span> <span class="kw">rows</span><span class="op">=</span><span class="dv">3999</span> loops<span class="op">=</span><span class="dv">1</span>)</span>
<span id="cb41-25"><a href="#cb41-25" aria-hidden="true" tabindex="-1"></a>         <span class="kw">Index</span> Cond: (lokalizacja <span class="op">&lt;</span>@ <span class="st">&#39;(50,100),(0,50)&#39;</span>:<span class="ch">:box</span>)</span>
<span id="cb41-26"><a href="#cb41-26" aria-hidden="true" tabindex="-1"></a> Planning <span class="dt">Time</span>: <span class="fl">0.063</span> ms</span>
<span id="cb41-27"><a href="#cb41-27" aria-hidden="true" tabindex="-1"></a> Execution <span class="dt">Time</span>: <span class="fl">0.816</span> ms</span>
<span id="cb41-28"><a href="#cb41-28" aria-hidden="true" tabindex="-1"></a>(<span class="dv">7</span> <span class="kw">rows</span>)</span></code></pre></div>
<p><strong>Ciekawe. W tym przypadku indeksy znacząco poprawiły czas
wykonywania się kwerend, co jest całkowitą odwrotnością dotychczasowego
trendu</strong></p>
<h2 id="raport">Raport</h2>
<p>“ Raport powinien obejmować dla każdego ćwiczenia:</p>
<ul>
<li>zapytania użyte do jego realizacji,</li>
<li>efekty polecenia EXPLAIN:
<ul>
<li>plan wykonania zapytania, wskazujący, że został wykorzystany
indeks,</li>
<li>prognozowany i rzeczywisty czas wykonania zapytania.</li>
</ul></li>
</ul>
<p><ins>Poprawki polegały na doprecyzowaniu terminologii oraz
zwiększeniu czytelności tekstu.</ins> “</p>
<h1 id="czas-poświęcony-na-wykonanie-laboratorium">Czas poświęcony na
wykonanie laboratorium:</h1>
<ul>
<li>1.5h na zajęciach
<ul>
<li>próba skorzystania z serwera lab.kis.agh.edu.pl zgodnie z
instrukcją, podczas gdy dns nie działał przez połowę czasu</li>
<li>dodanie <code>SET search_path = "lab-1";</code> do plików
dołączonych do laboratorium i postawienie tabel w bazie</li>
</ul></li>
<li>50 minut na setup środowiska
<ul>
<li>skopiowanie klucza ssh na serwer
<ul>
<li>odkrycie że lepiej jest zrobić to przez <code>nano</code> niż jakieś
<code>copy-ssh-key</code></li>
</ul></li>
<li>znalezienie dobrej metody do pracy na laboratorium w kwestji
<ul>
<li>kopiowania plików na serwer i edycji</li>
<li>wstawiania pliku sql do bazy</li>
<li>robienia nowej schemy</li>
<li>sprawdzone narzędzia:
<ul>
<li>pgadmin</li>
<li>psql</li>
<li>VS Code jakiśtam extension do baz danych</li>
</ul></li>
</ul></li>
<li>po wybraniu VsCode do zdalnego terminala i zarządzania plikami oraz
psql’a do całej reszy i po wyklarowaniu się pisania sprawka w
markdownie:
<ul>
<li>znalezienie obejścia do zapamiętywania długiej komendy do aktywacji
psql’a oraz obejście prompta o hasło bazy przy każdym uruchomieniu
<ul>
<li>przetestowanie i odrzucenie psql w wersji z connection
stringiem</li>
<li>odrzucenie ustawiania zmiennych środowiskowych psql’a</li>
<li>przetestowanie i przyjęcie pliku konfiguracyjnego
<code>.pgpass</code></li>
</ul></li>
</ul></li>
</ul></li>
<li>2h15min na samo wykonanie zadań w pełnym skupieniu i za jednym
podejściem, z równoległym pisaniem sprawozdania (weryfikowane przez
WakaTime, pisania tej sekcji nie uwzględniam)</li>
</ul>
</body>
</html> Fix this code so that every header have a line break before