PostgreSQL work_mem理解

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

edbstore=# CREATE TABLE test (id serial PRIMARY KEY, random_text text );
CREATE TABLE
edbstore=# \d test
                            Table "public.test"
   Column    |  Type   |                     Modifiers                     
-------------+---------+---------------------------------------------------
 id          | integer | not null default nextval(‘test_id_seq‘::regclass)
 random_text | text    | 
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)

edbstore=# \d
             List of relations
 Schema |    Name     |   Type   |  Owner   
--------+-------------+----------+----------
 public | tb1         | table    | postgres
 public | test        | table    | postgres
 public | test_id_seq | sequence | postgres
(3 rows)

edbstore=# copy test (random_text) FROM ‘/tmp/random_strings‘;
COPY 1000000
edbstore=# select * from test limit 10;
 id |                   random_text                   
----+-------------------------------------------------
  1 | CKQyHTYH5VjeHRUC6YYLF8H5S
  2 | G22uBhFmrlA17wTUzf
  3 | ey6kX7I6etknzhEFCL
  4 | 8LB6navSS8VyoIeqbJBx9RqB3O4AI8GIFExnM7s
  5 | bvYt4dKGSiAun6yA5Q7owlKWJGEgD0nlxoBRZm8B
  6 | qk1RfhXHwo2PNpbI4
  7 | rnPterTw1a3Z3DoL8rhzlltUKb5
  8 | l2TrrbDsBkAa5V5ZBKFE59k4T7sDKA58yrS0mJNssl7CJnF
  9 | xM9HPgq6QMRsx1aOTqM0LPRQRYkQy50uV
 10 | viSJ4p1i3O0dY8tKei3x
(10 rows)

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

edbstore=# show work_mem;
 work_mem 
----------
 1MB
(1 row)

edbstore=# EXPLAIN analyze SELECT * FROM test WHERE id <= 10 ORDER BY random_text ASC;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=8.73..8.75 rows=9 width=35) (actual time=0.188..0.202 rows=10 loops=1)
   Sort Key: random_text
   Sort Method: quicksort  Memory: 25kB
   ->  Index Scan using test_pkey on test  (cost=0.42..8.58 rows=9 width=35) (actual time=0.018..0.037 rows=10 loops=1)
         Index Cond: (id <= 10)
 Planning time: 1.435 ms
 Execution time: 0.294 ms
(7 rows)

edbstore=# EXPLAIN analyze SELECT * FROM test WHERE id <= 100 ORDER BY random_text ASC;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=13.50..13.75 rows=100 width=35) (actual time=0.870..1.027 rows=100 loops=1)
   Sort Key: random_text
   Sort Method: quicksort  Memory: 34kB
   ->  Index Scan using test_pkey on test  (cost=0.42..10.18 rows=100 width=35) (actual time=0.022..0.218 rows=100 loops=1)
         Index Cond: (id <= 100)
 Planning time: 0.286 ms
 Execution time: 1.248 ms
(7 rows)

edbstore=# EXPLAIN analyze SELECT * FROM test WHERE id <= 1000 ORDER BY random_text ASC;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=92.57..95.10 rows=1011 width=35) (actual time=8.846..10.251 rows=1000 loops=1)
   Sort Key: random_text
   Sort Method: quicksort  Memory: 112kB
   ->  Index Scan using test_pkey on test  (cost=0.42..42.12 rows=1011 width=35) (actual time=0.027..2.474 rows=1000 loops=1)
         Index Cond: (id <= 1000)
 Planning time: 0.286 ms
 Execution time: 11.584 ms
(7 rows)

edbstore=# EXPLAIN analyze SELECT * FROM test WHERE id <= 10000 ORDER BY random_text ASC;
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1049.39..1074.68 rows=10116 width=35) (actual time=144.963..160.943 rows=10000 loops=1)
   Sort Key: random_text
   Sort Method: external merge  Disk: 448kB
   ->  Index Scan using test_pkey on test  (cost=0.42..376.45 rows=10116 width=35) (actual time=0.063..22.225 rows=10000 loops=1)
         Index Cond: (id <= 10000)
 Planning time: 0.149 ms
 Execution time: 173.841 ms
(7 rows)

edbstore=# EXPLAIN analyze SELECT * FROM test WHERE id <= 100000 ORDER BY random_text ASC;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=17477.39..17727.70 rows=100122 width=35) (actual time=1325.789..1706.516 rows=100000 loops=1)
   Sort Key: random_text
   Sort Method: external merge  Disk: 4440kB
   ->  Index Scan using test_pkey on test  (cost=0.42..3680.56 rows=100122 width=35) (actual time=0.088..214.490 rows=100000 loops=1)
         Index Cond: (id <= 100000)
 Planning time: 0.147 ms
 Execution time: 1822.008 ms
(7 rows)

edbstore=# EXPLAIN analyze SELECT * FROM test WHERE id <= 1000000 ORDER BY random_text ASC;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=202426.34..204926.34 rows=1000000 width=35) (actual time=8703.143..10160.421 rows=1000000 loops=1)
   Sort Key: random_text
   Sort Method: external merge  Disk: 44504kB
   ->  Seq Scan on test  (cost=0.00..20732.00 rows=1000000 width=35) (actual time=0.024..1021.491 rows=1000000 loops=1)
         Filter: (id <= 1000000)
 Planning time: 0.316 ms
 Execution time: 10577.464 ms
(7 rows)

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

edbstore=# set work_mem="500MB";
SET
edbstore=# EXPLAIN analyze SELECT * FROM test WHERE id <= 1000000 ORDER BY random_text ASC;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Sort  (cost=120389.84..122889.84 rows=1000000 width=35) (actual time=6232.270..6884.121 rows=1000000 loops=1)
   Sort Key: random_text
   Sort Method: quicksort  Memory: 112847kB
   ->  Seq Scan on test  (cost=0.00..20732.00 rows=1000000 width=35) (actual time=0.015..659.035 rows=1000000 loops=1)
         Filter: (id <= 1000000)
 Planning time: 0.125 ms
 Execution time: 7302.621 ms
(7 rows)

可以发现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