当前位置：Gxlcms > 数据库问题 > PostgreSQL work_mem理解

PostgreSQL work_mem理解

时间：2021-07-01 10:21:17 帮助过：101人阅读

~]$ perl -e ‘@c=("a".."z","A".."Z",0..9); print join("",map{$c[rand@c]}10..20+rand(40))."\n" for 1..1000000‘ > /tmp/random_strings [postgres@sht-sgmhadoopdn-04 ~]$ ls -lh /tmp/random_strings -rw-r--r-- 1 postgres dba 31M Nov 21 22:44 /tmp/random_strings

创建对应表结构并导入数据

  1. edbstore<span style="color: #808080">=</span># <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span> test (id serial <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span>, random_text <span style="color: #0000ff">text</span><span style="color: #000000"> );
  2. </span><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000">
  3. edbstore</span><span style="color: #808080">=</span><span style="color: #000000"># \d test
  4.                             </span><span style="color: #0000ff">Table</span> "<span style="color: #0000ff">public</span><span style="color: #000000">.test"
  5.    </span><span style="color: #0000ff">Column</span>    <span style="color: #808080">|</span>  Type   <span style="color: #808080">|</span><span style="color: #000000">                     Modifiers                     
  6. </span><span style="color: #008080">--</span><span style="color: #008080">-----------+---------+---------------------------------------------------</span>
  7.  id          <span style="color: #808080">|</span> <span style="color: #0000ff">integer</span> <span style="color: #808080">|</span> <span style="color: #808080">not</span> <span style="color: #0000ff">null</span> <span style="color: #0000ff">default</span> nextval(<span style="color: #ff0000">‘</span><span style="color: #ff0000">test_id_seq</span><span style="color: #ff0000">‘</span><span style="color: #000000">::regclass)
  8.  random_text </span><span style="color: #808080">|</span> <span style="color: #0000ff">text</span>    <span style="color: #808080">|</span><span style="color: #000000"> 
  9. Indexes:
  10.     "test_pkey" </span><span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">, btree (id)
  11. edbstore</span><span style="color: #808080">=</span><span style="color: #000000"># \d
  12.              List </span><span style="color: #0000ff">of</span><span style="color: #000000"> relations
  13.  </span><span style="color: #0000ff">Schema</span> <span style="color: #808080">|</span>    Name     <span style="color: #808080">|</span>   Type   <span style="color: #808080">|</span><span style="color: #000000">  Owner   
  14. </span><span style="color: #008080">--</span><span style="color: #008080">------+-------------+----------+----------</span>
  15.  <span style="color: #0000ff">public</span> <span style="color: #808080">|</span> tb1         <span style="color: #808080">|</span> <span style="color: #0000ff">table</span>    <span style="color: #808080">|</span><span style="color: #000000"> postgres
  16.  </span><span style="color: #0000ff">public</span> <span style="color: #808080">|</span> test        <span style="color: #808080">|</span> <span style="color: #0000ff">table</span>    <span style="color: #808080">|</span><span style="color: #000000"> postgres
  17.  </span><span style="color: #0000ff">public</span> <span style="color: #808080">|</span> test_id_seq <span style="color: #808080">|</span> sequence <span style="color: #808080">|</span><span style="color: #000000"> postgres
  18. (</span><span style="color: #800000; font-weight: bold">3</span><span style="color: #000000"> rows)
  19. edbstore</span><span style="color: #808080">=</span># copy test (random_text) <span style="color: #0000ff">FROM</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">/tmp/random_strings</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
  20. COPY </span><span style="color: #800000; font-weight: bold">1000000</span><span style="color: #000000">
  21. edbstore</span><span style="color: #808080">=</span># <span style="color: #0000ff">select</span> <span style="color: #808080">*</span> <span style="color: #0000ff">from</span> test limit <span style="color: #800000; font-weight: bold">10</span><span style="color: #000000">;
  22.  id </span><span style="color: #808080">|</span><span style="color: #000000">                   random_text                   
  23. </span><span style="color: #008080">--</span><span style="color: #008080">--+-------------------------------------------------</span>
  24.   <span style="color: #800000; font-weight: bold">1</span> <span style="color: #808080">|</span><span style="color: #000000"> CKQyHTYH5VjeHRUC6YYLF8H5S
  25.   </span><span style="color: #800000; font-weight: bold">2</span> <span style="color: #808080">|</span><span style="color: #000000"> G22uBhFmrlA17wTUzf
  26.   </span><span style="color: #800000; font-weight: bold">3</span> <span style="color: #808080">|</span><span style="color: #000000"> ey6kX7I6etknzhEFCL
  27.   </span><span style="color: #800000; font-weight: bold">4</span> <span style="color: #808080">|</span><span style="color: #000000"> 8LB6navSS8VyoIeqbJBx9RqB3O4AI8GIFExnM7s
  28.   </span><span style="color: #800000; font-weight: bold">5</span> <span style="color: #808080">|</span><span style="color: #000000"> bvYt4dKGSiAun6yA5Q7owlKWJGEgD0nlxoBRZm8B
  29.   </span><span style="color: #800000; font-weight: bold">6</span> <span style="color: #808080">|</span><span style="color: #000000"> qk1RfhXHwo2PNpbI4
  30.   </span><span style="color: #800000; font-weight: bold">7</span> <span style="color: #808080">|</span><span style="color: #000000"> rnPterTw1a3Z3DoL8rhzlltUKb5
  31.   </span><span style="color: #800000; font-weight: bold">8</span> <span style="color: #808080">|</span><span style="color: #000000"> l2TrrbDsBkAa5V5ZBKFE59k4T7sDKA58yrS0mJNssl7CJnF
  32.   </span><span style="color: #800000; font-weight: bold">9</span> <span style="color: #808080">|</span><span style="color: #000000"> xM9HPgq6QMRsx1aOTqM0LPRQRYkQy50uV
  33.  </span><span style="color: #800000; font-weight: bold">10</span> <span style="color: #808080">|</span><span style="color: #000000"> viSJ4p1i3O0dY8tKei3x
  34. (</span><span style="color: #800000; font-weight: bold">10</span> rows)

 通过每次获取不通的数据量来观察每次explain的执行方式

  1. edbstore<span style="color: #808080">=</span><span style="color: #000000"># show work_mem;
  2.  work_mem 
  3. </span><span style="color: #008080">--</span><span style="color: #008080">--------</span>
  4. <span style="color: #000000"> 1MB
  5. (</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000"> row)
  6. edbstore</span><span style="color: #808080">=</span># EXPLAIN analyze <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> test <span style="color: #0000ff">WHERE</span> id <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">10</span> <span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> random_text <span style="color: #0000ff">ASC</span><span style="color: #000000">;
  7.                                                        QUERY </span><span style="color: #0000ff">PLAN</span>                                                       
  8. <span style="color: #008080">--</span><span style="color: #008080">----------------------------------------------------------------------------------------------------------------------</span>
  9.  Sort  (cost<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">8.73</span>..<span style="color: #800000; font-weight: bold">8.75</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">9</span> width<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">35</span>) (actual time<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.188</span>..<span style="color: #800000; font-weight: bold">0.202</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">10</span> loops<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  10.    Sort </span><span style="color: #0000ff">Key</span><span style="color: #000000">: random_text
  11.    Sort Method: quicksort  Memory: 25kB
  12.    </span><span style="color: #808080">-></span>  <span style="color: #0000ff">Index</span> Scan using test_pkey <span style="color: #0000ff">on</span> test  (cost<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.42</span>..<span style="color: #800000; font-weight: bold">8.58</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">9</span> width<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">35</span>) (actual time<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.018</span>..<span style="color: #800000; font-weight: bold">0.037</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">10</span> loops<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  13.          </span><span style="color: #0000ff">Index</span> Cond: (id <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">10</span><span style="color: #000000">)
  14.  Planning time: </span><span style="color: #800000; font-weight: bold">1.435</span><span style="color: #000000"> ms
  15.  Execution time: </span><span style="color: #800000; font-weight: bold">0.294</span><span style="color: #000000"> ms
  16. (</span><span style="color: #800000; font-weight: bold">7</span><span style="color: #000000"> rows)
  17. edbstore</span><span style="color: #808080">=</span># EXPLAIN analyze <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> test <span style="color: #0000ff">WHERE</span> id <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">100</span> <span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> random_text <span style="color: #0000ff">ASC</span><span style="color: #000000">;
  18.                                                          QUERY </span><span style="color: #0000ff">PLAN</span>                                                         
  19. <span style="color: #008080">--</span><span style="color: #008080">--------------------------------------------------------------------------------------------------------------------------</span>
  20.  Sort  (cost<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">13.50</span>..<span style="color: #800000; font-weight: bold">13.75</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">100</span> width<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">35</span>) (actual time<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.870</span>..<span style="color: #800000; font-weight: bold">1.027</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">100</span> loops<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  21.    Sort </span><span style="color: #0000ff">Key</span><span style="color: #000000">: random_text
  22.    Sort Method: quicksort  Memory: 34kB
  23.    </span><span style="color: #808080">-></span>  <span style="color: #0000ff">Index</span> Scan using test_pkey <span style="color: #0000ff">on</span> test  (cost<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.42</span>..<span style="color: #800000; font-weight: bold">10.18</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">100</span> width<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">35</span>) (actual time<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.022</span>..<span style="color: #800000; font-weight: bold">0.218</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">100</span> loops<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  24.          </span><span style="color: #0000ff">Index</span> Cond: (id <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">100</span><span style="color: #000000">)
  25.  Planning time: </span><span style="color: #800000; font-weight: bold">0.286</span><span style="color: #000000"> ms
  26.  Execution time: </span><span style="color: #800000; font-weight: bold">1.248</span><span style="color: #000000"> ms
  27. (</span><span style="color: #800000; font-weight: bold">7</span><span style="color: #000000"> rows)
  28. edbstore</span><span style="color: #808080">=</span># EXPLAIN analyze <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> test <span style="color: #0000ff">WHERE</span> id <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">1000</span> <span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> random_text <span style="color: #0000ff">ASC</span><span style="color: #000000">;
  29.                                                           QUERY </span><span style="color: #0000ff">PLAN</span>                                                          
  30. <span style="color: #008080">--</span><span style="color: #008080">----------------------------------------------------------------------------------------------------------------------------</span>
  31.  Sort  (cost<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">92.57</span>..<span style="color: #800000; font-weight: bold">95.10</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1011</span> width<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">35</span>) (actual time<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">8.846</span>..<span style="color: #800000; font-weight: bold">10.251</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1000</span> loops<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  32.    Sort </span><span style="color: #0000ff">Key</span><span style="color: #000000">: random_text
  33.    Sort Method: quicksort  Memory: 112kB
  34.    </span><span style="color: #808080">-></span>  <span style="color: #0000ff">Index</span> Scan using test_pkey <span style="color: #0000ff">on</span> test  (cost<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.42</span>..<span style="color: #800000; font-weight: bold">42.12</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1011</span> width<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">35</span>) (actual time<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.027</span>..<span style="color: #800000; font-weight: bold">2.474</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1000</span> loops<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  35.          </span><span style="color: #0000ff">Index</span> Cond: (id <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">1000</span><span style="color: #000000">)
  36.  Planning time: </span><span style="color: #800000; font-weight: bold">0.286</span><span style="color: #000000"> ms
  37.  Execution time: </span><span style="color: #800000; font-weight: bold">11.584</span><span style="color: #000000"> ms
  38. (</span><span style="color: #800000; font-weight: bold">7</span><span style="color: #000000"> rows)
  39. edbstore</span><span style="color: #808080">=</span># EXPLAIN analyze <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> test <span style="color: #0000ff">WHERE</span> id <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">10000</span> <span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> random_text <span style="color: #0000ff">ASC</span><span style="color: #000000">;
  40.                                                             QUERY </span><span style="color: #0000ff">PLAN</span>                                                            
  41. <span style="color: #008080">--</span><span style="color: #008080">--------------------------------------------------------------------------------------------------------------------------------</span>
  42.  Sort  (cost<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1049.39</span>..<span style="color: #800000; font-weight: bold">1074.68</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">10116</span> width<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">35</span>) (actual time<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">144.963</span>..<span style="color: #800000; font-weight: bold">160.943</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">10000</span> loops<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  43.    Sort </span><span style="color: #0000ff">Key</span><span style="color: #000000">: random_text
  44.    Sort Method: external merge  </span><span style="color: #0000ff">Disk</span><span style="color: #000000">: 448kB
  45.    </span><span style="color: #808080">-></span>  <span style="color: #0000ff">Index</span> Scan using test_pkey <span style="color: #0000ff">on</span> test  (cost<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.42</span>..<span style="color: #800000; font-weight: bold">376.45</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">10116</span> width<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">35</span>) (actual time<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.063</span>..<span style="color: #800000; font-weight: bold">22.225</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">10000</span> loops<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  46.          </span><span style="color: #0000ff">Index</span> Cond: (id <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">10000</span><span style="color: #000000">)
  47.  Planning time: </span><span style="color: #800000; font-weight: bold">0.149</span><span style="color: #000000"> ms
  48.  Execution time: </span><span style="color: #800000; font-weight: bold">173.841</span><span style="color: #000000"> ms
  49. (</span><span style="color: #800000; font-weight: bold">7</span><span style="color: #000000"> rows)
  50. edbstore</span><span style="color: #808080">=</span># EXPLAIN analyze <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> test <span style="color: #0000ff">WHERE</span> id <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">100000</span> <span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> random_text <span style="color: #0000ff">ASC</span><span style="color: #000000">;
  51.                                                               QUERY </span><span style="color: #0000ff">PLAN</span>                                                              
  52. <span style="color: #008080">--</span><span style="color: #008080">------------------------------------------------------------------------------------------------------------------------------------</span>
  53.  Sort  (cost<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">17477.39</span>..<span style="color: #800000; font-weight: bold">17727.70</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">100122</span> width<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">35</span>) (actual time<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1325.789</span>..<span style="color: #800000; font-weight: bold">1706.516</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">100000</span> loops<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  54.    Sort </span><span style="color: #0000ff">Key</span><span style="color: #000000">: random_text
  55.    Sort Method: external merge  </span><span style="color: #0000ff">Disk</span><span style="color: #000000">: 4440kB
  56.    </span><span style="color: #808080">-></span>  <span style="color: #0000ff">Index</span> Scan using test_pkey <span style="color: #0000ff">on</span> test  (cost<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.42</span>..<span style="color: #800000; font-weight: bold">3680.56</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">100122</span> width<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">35</span>) (actual time<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.088</span>..<span style="color: #800000; font-weight: bold">214.490</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">100000</span> loops<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  57.          </span><span style="color: #0000ff">Index</span> Cond: (id <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">100000</span><span style="color: #000000">)
  58.  Planning time: </span><span style="color: #800000; font-weight: bold">0.147</span><span style="color: #000000"> ms
  59.  Execution time: </span><span style="color: #800000; font-weight: bold">1822.008</span><span style="color: #000000"> ms
  60. (</span><span style="color: #800000; font-weight: bold">7</span><span style="color: #000000"> rows)
  61. edbstore</span><span style="color: #808080">=</span># EXPLAIN analyze <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> test <span style="color: #0000ff">WHERE</span> id <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">1000000</span> <span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> random_text <span style="color: #0000ff">ASC</span><span style="color: #000000">;
  62.                                                        QUERY </span><span style="color: #0000ff">PLAN</span>                                                       
  63. <span style="color: #008080">--</span><span style="color: #008080">----------------------------------------------------------------------------------------------------------------------</span>
  64.  Sort  (cost<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">202426.34</span>..<span style="color: #800000; font-weight: bold">204926.34</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1000000</span> width<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">35</span>) (actual time<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">8703.143</span>..<span style="color: #800000; font-weight: bold">10160.421</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1000000</span> loops<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  65.    Sort </span><span style="color: #0000ff">Key</span><span style="color: #000000">: random_text
  66.    Sort Method: external merge  </span><span style="color: #0000ff">Disk</span><span style="color: #000000">: 44504kB
  67.    </span><span style="color: #808080">-></span>  Seq Scan <span style="color: #0000ff">on</span> test  (cost<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.00</span>..<span style="color: #800000; font-weight: bold">20732.00</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1000000</span> width<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">35</span>) (actual time<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.024</span>..<span style="color: #800000; font-weight: bold">1021.491</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1000000</span> loops<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  68.          Filter: (id </span><span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">1000000</span><span style="color: #000000">)
  69.  Planning time: </span><span style="color: #800000; font-weight: bold">0.316</span><span style="color: #000000"> ms
  70.  Execution time: </span><span style="color: #800000; font-weight: bold">10577.464</span><span style="color: #000000"> ms
  71. (</span><span style="color: #800000; font-weight: bold">7</span> rows)
row Sort Method Execution time
10 quicksort  Memory: 25kB 0.294 ms
100 Sort Method: quicksort  Memory: 34kB 1.248 ms
1000 Sort Method: quicksort  Memory: 112kB 11.584 ms
10000 Sort Method: external merge  Disk: 448kB 173.841 ms
100000 Sort Method: external merge  Disk: 4440kB 1822.008 ms
1000000 Sort Method: external merge  Disk: 44504kB 10577.464 ms

通过上图我们可以看到，当sort的数据大于一万条时，explain显示排序方法从 quicksort in memory, 到external merge disk method，说明此时的work_mem的大小不能满足我们在内存的sort和hash表的需求。此时我们将work_mem参数的值调大

  1. edbstore<span style="color: #808080">=</span># <span style="color: #0000ff">set</span> work_mem<span style="color: #808080">=</span><span style="color: #000000">"500MB";
  2. </span><span style="color: #0000ff">SET</span><span style="color: #000000">
  3. edbstore</span><span style="color: #808080">=</span># EXPLAIN analyze <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> test <span style="color: #0000ff">WHERE</span> id <span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">1000000</span> <span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> random_text <span style="color: #0000ff">ASC</span><span style="color: #000000">;
  4.                                                       QUERY </span><span style="color: #0000ff">PLAN</span>                                                       
  5. <span style="color: #008080">--</span><span style="color: #008080">---------------------------------------------------------------------------------------------------------------------</span>
  6.  Sort  (cost<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">120389.84</span>..<span style="color: #800000; font-weight: bold">122889.84</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1000000</span> width<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">35</span>) (actual time<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">6232.270</span>..<span style="color: #800000; font-weight: bold">6884.121</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1000000</span> loops<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  7.    Sort </span><span style="color: #0000ff">Key</span><span style="color: #000000">: random_text
  8.    Sort Method: quicksort  Memory: 112847kB
  9.    </span><span style="color: #808080">-></span>  Seq Scan <span style="color: #0000ff">on</span> test  (cost<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.00</span>..<span style="color: #800000; font-weight: bold">20732.00</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1000000</span> width<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">35</span>) (actual time<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0.015</span>..<span style="color: #800000; font-weight: bold">659.035</span> rows<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1000000</span> loops<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)
  10.          Filter: (id </span><span style="color: #808080"><=</span> <span style="color: #800000; font-weight: bold">1000000</span><span style="color: #000000">)
  11.  Planning time: </span><span style="color: #800000; font-weight: bold">0.125</span><span style="color: #000000"> ms
  12.  Execution time: </span><span style="color: #800000; font-weight: bold">7302.621</span><span style="color: #000000"> ms
  13. (</span><span style="color: #800000; font-weight: bold">7</span> rows)
row Sort Method Execution time
1000000 quicksort  Memory: 112847kB 6887.851 ms

可以发现sort method从merg disk变成quicksort in memory。

 

 

https://www.depesz.com/2011/07/03/understanding-postgresql-conf-work_mem/

 

PostgreSQL work_mem理解

标签：方式   tar   表操作   tin   ase   integer   div   spec   nbsp   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！