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

  • 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.

主站蜘蛛池模板: SHOW| 正定县| 南川市| 广灵县| 竹北市| 谢通门县| 双峰县| 疏附县| 远安县| 闽侯县| 虹口区| 安国市| 普定县| 松阳县| 涪陵区| 永年县| 安新县| 凯里市| 拉孜县| 奉化市| 昭觉县| 永福县| 北辰区| 凌源市| 分宜县| 康乐县| 泰和县| 永定县| 耒阳市| 宣威市| 宾川县| 军事| 丰城市| 缙云县| 五寨县| 拜城县| 泾源县| 二连浩特市| 万山特区| 三台县| 灌云县|