PostgreSQLhstore Column Performance Improvement case

PostgreSQL supports hstore to store KEY-gt; VALUE data, which is similar to ARRAY or JSON. To use such data efficiently, of course, efficiency is essential.

PostgreSQL supports hstore to store KEY-gt; VALUE data, which is similar to ARRAY or JSON. To use such data efficiently, of course, efficiency is essential.

PostgreSQL supports hstore to store data such as KEY-> VALUE, which is also similar to ARRAY or JSON. To use this type of data efficiently, you must use efficient indexes. Let's take a look at the performance of two different types of indexes for the same retrieval request.

Suppose we have an original table with a BTREE index based on the str1 field.

T_girl = # \ d status_check;
Table "ytt. status_check"
Column | Type | Modifiers
-------- + --------------------- + -----------
Is_yes | boolean | not null
Str1 | character varying (20) | not null
Str2 | character varying (20) | not null
Indexes:
"Index_status_check_str1" btree (str1)

There are 10 million records. The data is roughly as follows,

T_girl = # select * from status_check limit 2;
Is_yes | str1 | str2
-------- + ------ + ----------------------
F | 0 | cfcd208495d565ef66e7
T | 1 | c4ca4238a0b923820dcc
(2 rows)

Time: 0.617 MS
T_girl = #

Stores the status_check_hstore table structure of the hstore type. There is a GIST index based on the str1_str2 field.

Table "ytt. status_check_hstore"
Column | Type | Modifiers
----------- + --------- + -----------
Is_yes | boolean |
Str1_str2 | hstore |
Indexes:
"Idx_str_str2_gist" gist (strw.str2)

T_girl = # select * from status_check_hstore limit 2;
Is_yes | str1_str2
-------- + -----------------------------
F | "0" => "cfcd208495d565ef66e7"
T | "1" => "c4ca4238a0b923820dcc"
(2 rows)

Time: 39.874 MS

For more details, please continue to read the highlights on the next page:

------------------------------------ Lili split line ------------------------------------

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

,