BTree Index Compression

Availability

This feature is available since MogDB 3.1.0.

Introduction

This feature supports compression of index data. Compressed index data is loaded to memory, reducing memory usage and helping load more index data at a time.

Benefits

The use of BTree index storage structure and memory compression can provide many advantages to meet system business requirements for efficient data queries.The following are a few of the key benefits of this feature:

• Improved data query speed: BTree indexes are an efficient index structure that provides fast data lookup and access. By compressing the index data, the size of the index data can be reduced, thus speeding up the query. Compressed index data can be loaded into memory faster and reduce the cost of memory access.

• Reduced storage footprint: Indexed data usually takes up a lot of storage space. By compressing index data, you can reduce the storage footprint. A smaller index data size also means fewer disk IO operations and less network transfer overhead.

• Improve overall system performance: By reducing memory footprint and disk IO operations, the BTree index compression feature can significantly improve overall system performance. The increase in data query speed and storage space saving can speed up the response time of the system and improve the user experience. In addition, the reduction of IO operations can also reduce the load of the system and improve the stability and reliability of the system.

In conclusion, the use of BTree index storage structure and memory compression can optimize the storage and query efficiency of indexed data, improve data query speed, reduce storage space occupation, and improve the overall performance of the system. This is very useful for meeting the demand of system business for efficient data query.

Description

The use of BTree index storage structure and compression of index data stored in memory can further improve query efficiency.

When there is a large amount of duplicate data in one or more columns, the creation of the index and the de-compression of the index data are especially important. By de-compressing the index data, you can reduce the size of the index data, save storage space, and load the index data into memory faster.

At the same time, this feature also supports the de-compression of non-unique index data. For non-unique indexes, de-compression can further reduce the storage space occupied and speed up the query. For unique indexes, although there is no compression effect, you can still enjoy other advantages, such as fast data lookup and access.

In summary, this feature can accelerate data query efficiency through the BTree index storage structure and compression of index data stored in memory, and is particularly applicable to the existence of a large number of duplicate data index creation and index data de-compression. Both non-unique and unique indexes can benefit from this feature.

Constraints

• The include index is not supported.
• The system table index is not supported.
• Only the BTree index is supported.

Example

1. Create a table.

   -- Create a table
   CREATE TABLE mogdb_index_compression_table_01
   (
           seq int,
           id int,
           a  text,
           b  text,
           c  timestamp
   );

2. Creates an index, specifying the compression parameter.

   -- Create a btree compressed index, no compression parameter is specified by default, and the index compression attribute is turned off.
   CREATE INDEX idx_single_col_01 ON mogdb_index_compression_table_01(a) WITH (compression =yes);
   CREATE INDEX idx_multi_col_01 ON mogdb_index_compression_table_01(id,a) WITH (compression =yes);
   
   -- The same data table, the same index attribute columns, create a btree index, the compression attribute is in default off state, used to compare the effect of compression space reduction
   CREATE INDEX idx_single_col_02 ON mogdb_index_compression_table_01(a);
   CREATE INDEX idx_multi_col_02 ON mogdb_index_compression_table_01(id,a);
   
   -- catalog system table to view index compression switch information
   MogDB =# SELECT * FROM pg_indexes WHERE tablename = 'mogdb_index_compression_table_01';
    schemaname |            tablename             |     indexname     | tablespace |                                                              indexdef
   ------------+----------------------------------+-------------------+------------+-----------------------------------------------------------------------------------------------------------------------
   -------------
    public     | mogdb_index_compression_table_01 | idx_single_col_01 |            | CREATE INDEX idx_single_col_01 ON mogdb_index_compression_table_01 USING btree (a) WITH (compression =yes) TABLESPACE p
   g_default
    public     | mogdb_index_compression_table_01 | idx_multi_col_01  |            | CREATE INDEX idx_multi_col_01 ON mogdb_index_compression_table_01 USING btree (id, a) WITH (compression =yes) TABLESPAC
   E pg_default
    public     | mogdb_index_compression_table_01 | idx_single_col_02 |            | CREATE INDEX idx_single_col_02 ON mogdb_index_compression_table_01 USING btree (a) TABLESPACE pg_default
    public     | mogdb_index_compression_table_01 | idx_multi_col_02  |            | CREATE INDEX idx_multi_col_02 ON mogdb_index_compression_table_01 USING btree (id, a) TABLESPACE pg_default
   ( 4 rows)
   MogDB =# SELECT relname,reloptions FROM pg_class WHERE relname = 'idx_single_col_01';
         relname      |    reloptions
   -------------------+-------------------
    idx_single_col_01 | {compression =yes}
   ( 1 row)
   MogDB =# SELECT relname,reloptions FROM pg_class WHERE relname = 'idx_multi_col_01';
        relname      |    reloptions
   ------------------+-------------------
    idx_multi_col_01 | {compression =yes}
   ( 1 row)

3. Construct random data to insert into the data table.

   -- Insert data
   INSERT INTO mogdb_index_compression_table_01
   SELECT seqno,
   seqno % 20,
   md5((random() * 20):: INTEGER::text),
   md5(random()::text),
   tt
   FROM generate_series( 1, 1000) AS seqno,
   generate_series(now(),now() + '1 week', '1 day') AS tt;
   
   -- Viewing inserted data in a data table
   MogDB =# SELECT * FROM mogdb_index_compression_table_01 LIMIT 15;
    seq | id |                a                 |                b                 |             c
   -----+----+----------------------------------+----------------------------------+----------------------------
      1 |  1 | c4ca4238a0b923820dcc509a6f75849b | d5e055cf4a50c7f2790bacae0685108c | 2023 -05 -25 16: 12: 46.776094
      1 |  1 | 6512bd43d9caa6e02c990b0a82652dca | 59124424a2bb70cba5cd9495912c6f3f | 2023 -05 -26 16: 12: 46.776094
      1 |  1 | 1679091c5a880faf6fb5e6087eb1b2dc | 8b49e1ccec21c9fc2bfb08dfbea85995 | 2023 -05 -27 16: 12: 46.776094
      1 |  1 | 1679091c5a880faf6fb5e6087eb1b2dc | 0b1660223cd7777ef587b535b913ce5a | 2023 -05 -28 16: 12: 46.776094
      1 |  1 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 1b9abe418c57bdcee01ee66415cf07de | 2023 -05 -29 16: 12: 46.776094
      1 |  1 | c4ca4238a0b923820dcc509a6f75849b | d60b4db2e8390bc96887f944be6af3e8 | 2023 -05 -30 16: 12: 46.776094
      1 |  1 | c20ad4d76fe97759aa27a0c99bff6710 | 7e1d2447ed0f40c4d77f163c925361b1 | 2023 -05 -31 16: 12: 46.776094
      1 |  1 | cfcd208495d565ef66e7dff9f98764da | 1c4c7d26e8e04643051da09f32425ce9 | 2023 -06 -01 16: 12: 46.776094
      2 |  2 | c51ce410c124a10e0db5e4b97fc2af39 | 7c330d7e0940db58c56b6c2a48618d91 | 2023 -05 -25 16: 12: 46.776094
      2 |  2 | 6f4922f45568161a8cdf4ad2299f6d23 | 5cba36cfee55985bef28e77cd95be9f6 | 2023 -05 -26 16: 12: 46.776094
      2 |  2 | d3d9446802a44259755d38e6d163e820 | 6e94dde54130dedd4166adde26a47ff7 | 2023 -05 -27 16: 12: 46.776094
      2 |  2 | a87ff679a2f3e71d9181a67b7542122c | f834dd99d9e24eb2f8fde50560f73679 | 2023 -05 -28 16: 12: 46.776094
      2 |  2 | c4ca4238a0b923820dcc509a6f75849b | d3ebea8e5ee091eba9fbb458bee90d15 | 2023 -05 -29 16: 12: 46.776094
      2 |  2 | 8f14e45fceea167a5a36dedd4bea2543 | 681df81facbf975107287365d7c2b568 | 2023 -05 -30 16: 12: 46.776094
      2 |  2 | aab3238922bcc25a6f606eb525ffdc56 | c5b256a5aa07c723df488a432147b39e | 2023 -05 -31 16: 12: 46.776094
   ( 15 rows)

4. View compressed and uncompressed index file sizes.

   -- View the data file size and index file size, due to random data, file size may be different from the example, mainly focus on non-compressed and compressed scenarios under the index file size changes
   -- 1. View data file size
   MogDB =# SELECT pg_size_pretty(pg_relation_size( 'mogdb_index_compression_table_01'));
    pg_size_pretty
   ----------------
     920 kB
   ( 1 row)
   
   -- 2.View single-column indexed uncompressed scenario file sizes
   MogDB =# SELECT pg_size_pretty(pg_relation_size( 'idx_single_col_02'));
    pg_size_pretty
   ----------------
     504 kB
   ( 1 row)
   
   -- 3.View Single-Column Index Compression Scenario File Size
   MogDB =# SELECT pg_size_pretty(pg_relation_size( 'idx_single_col_01'));
    pg_size_pretty
   ----------------
     88 kB
   ( 1 row)
   
   -- 4.View Uncompressed Scenario File Size for Multi-Column Combined Indexes
   MogDB =# SELECT pg_size_pretty(pg_relation_size( 'idx_multi_col_02'));
    pg_size_pretty
   ----------------
     600 kB
   ( 1 row)
   
   -- 5.View Multi-column Combined Index Compression Scenario file size
   MogDB =# SELECT pg_size_pretty(pg_relation_size( 'idx_multi_col_01'));
    pg_size_pretty
   ----------------
     120 kB
   ( 1 row)
   
   -- Directly view comparisons by other means
   MogDB =# \d +
                                                      List of relations
    Schema |               Name               | Type  |  Owner   |  Size  |             Storage              | Description
   --------+----------------------------------+-------+----------+--------+----------------------------------+-------------
    public | mogdb_index_compression_table_01 | table | xyhmogdb | 952 kB | {orientation = row,compression = no} |
   ( 1 row)
   MogDB =# \di +
                                                         List of relations
    Schema |       Name        | Type  |  Owner   |              Table               |  Size  |      Storage      | Description
   --------+-------------------+-------+----------+----------------------------------+--------+-------------------+-------------
    public | idx_multi_col_01  | index | xyhmogdb | mogdb_index_compression_table_01 | 120 kB | {compression =yes} |
    public | idx_multi_col_02  | index | xyhmogdb | mogdb_index_compression_table_01 | 600 kB |                   |
    public | idx_single_col_01 | index | xyhmogdb | mogdb_index_compression_table_01 | 88 kB  | {compression =yes} |
    public | idx_single_col_02 | index | xyhmogdb | mogdb_index_compression_table_01 | 504 kB |                   |
   ( 4 rows)

Related Pages

CREATE INDEX