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

Reducing space consumption

Indexing is nice and its main purpose is to speed up things as much as possible. As with all good stuff, indexing comes with a price tag: space consumption. To do its magic, an index has to store values in an organized fashion. If your table contains 10 million integer values, the index belonging to the table will logically contain those 10 million integer values.

A B-tree will contain a pointer to each row in the table, and so it is certainly not free of charge. To figure out how much space an index will need, you can ask psql using the \di+ command:

test=# \di+ 
List of relations
Schema | Name | Type | Owner | Table | Size
--------+------------+-------+-------+----------+-------
public | idx_cos | index | hs | t_random | 86 MB
public | idx_id | index | hs | t_test | 86 MB
public | idx_name | index | hs | t_test | 86 MB
public | idx_random | index | hs | t_random | 86 MB
(4 rows)

In my database, the staggering amount of 344 MB has been burned to store those indexes. Now, compare this to the amount of storage burned by the underlying tables:

test=# \d+ 
List of relations
Schema | Name | Type | Owner | Size
--------+---------------+----------+-------+------------
public | t_random | table | hs | 169 MB
public | t_test | table | hs | 169 MB
public | t_test_id_seq | sequence | hs | 8192 bytes
(3 rows)

The size of both tables combined is just 338 MB. In other words, our indexing needs more space than the actual data. In the real world, this is common and actually pretty likely. Recently I visited a Cybertec customer in Germany and I saw a database in which 64% of the database size was made up of indexes that were never used (not a single time over the period of months). So, over-indexing can be an issue just like under-indexing. Remember, those indexes don't just consume space. Every INSERT or UPDATE must maintain the values in the indexes as well. In extreme cases like our example, this vastly decreases write throughput.

If there are just a handful of different values in the table, partial indexes are a solution:

test=# DROP INDEX idx_name; 
DROP INDEX
test=# CREATE INDEX idx_name ON t_test (name) WHERE name NOT IN ('hans', 'paul');
CREATE INDEX

In this case, the majority has been excluded from the index and a small, efficient index can be enjoyed:

test=# \di+ idx_name 
List of relations
Schema | Name | Type | Owner | Table | Size
--------+----------+-------+-------+--------+-----------
public | idx_name | index | hs | t_test | 8192 bytes
(1 row)

Note that it only makes sense to exclude very frequent values that make up a large part of the table (at least 25% or so). Ideal candidates for partial indexes are gender (we assume that most people are male or female), nationality (assuming that most people in your country have the same nationality), and so on. Of course, applying this kind of trickery requires some deep knowledge of your data, but it certainly pays off.

主站蜘蛛池模板: 阿克苏市| 革吉县| 宝清县| 神池县| 蓬安县| 郑州市| 衡水市| 阜平县| 武安市| 房产| 江源县| 阜新市| 苍梧县| 长顺县| 莫力| 临洮县| 公安县| 定远县| 阿勒泰市| 信丰县| 龙江县| 子洲县| 宜兴市| 军事| 洛川县| 墨江| 政和县| 海南省| 仪陇县| 牟定县| 涟源市| 陵水| 吉安市| 托里县| 浦县| 彭山县| 上林县| 和静县| 湾仔区| 晋中市| 禹城市|