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

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

主站蜘蛛池模板: 西宁市| 阳高县| 汤阴县| 子长县| 泾阳县| 遂宁市| 章丘市| 临漳县| 武威市| 大新县| 盖州市| 略阳县| 禹州市| 敦化市| 突泉县| 德江县| 老河口市| 六枝特区| 乡城县| 瓦房店市| 张掖市| 巴彦淖尔市| 岳池县| 柏乡县| 和龙市| 准格尔旗| 金寨县| 内江市| 龙山县| 奉新县| 金堂县| 腾冲县| 平南县| 巨鹿县| 阜城县| 四平市| 赣州市| 新郑市| 通城县| 枣庄市| 张北县|