官术网_书友最值得收藏!

  • Mastering PostgreSQL 9.6
  • Hans Jurgen Schonig
  • 210字
  • 2021-07-09 19:57:15

Deploying simple indexes

Firing up more worker processes to scan ever larger tables is sometimes not the solution. Reading entire tables to find just a single row is usually not a good idea. Therefore, it makes sense to create indexes:

test=# CREATE INDEX idx_id ON t_test (id); 
CREATE INDEX
test=# SELECT * FROM t_test WHERE id = 43242;
id | name
-------+------
43242 | hans
(1 row)
Time: 0.259 ms

PostgreSQL uses Lehman-Yao's high concurrency B-tree for standard indexes. Along with some PostgreSQL specific optimizations, those trees provide end users with excellent performance. The most important thing is that Lehman-Yao allows you to run many operations (reading and writing) on the very same index at the same time, which helps to improve throughput dramatically.

However, indexes are not for free:

test=# \di+ 
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+--------+-------+-------+--------+-------+-------------
public | idx_id | index | hs | t_test | 86 MB |
(1 row)

As you can see, our index containing 4 million rows will eat up 86 MB of disk space. In addition to that, writes to the table will be slower because the index has to be kept in sync all the time.

主站蜘蛛池模板: 阿拉善盟| 三门县| 运城市| 杭锦后旗| 肥乡县| 河津市| 武陟县| 青田县| 盐边县| 宿松县| 法库县| 綦江县| 德惠市| 安陆市| 东乡| 韶关市| 邯郸县| 东至县| 上林县| 五大连池市| 张家川| 广灵县| 信宜市| 民权县| 夏邑县| 策勒县| 阿克苏市| 府谷县| 昆明市| 京山县| 海城市| 洪雅县| 桓台县| 镶黄旗| 名山县| 建湖县| 合川市| 寿阳县| 紫金县| 上虞市| 阳泉市|