From: | Олег Самойлов <splarv(at)ya(dot)ru> |
---|---|
To: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: WTF with hash index? |
Date: | 2018-11-13 19:29:20 |
Message-ID: | 6714A96C-349B-4096-B929-FB28F215D6BB@ya.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am just doing experiment what a type a most suitable for enumeration in PostgreSQL. And what index. And this effect looked for me very strange. There is in the PostgreSQL one another hash index. This is gin(jsonb_path_ops) for the jsob type. It is also use hash internally, but it is much better.
Example based on the previous example.
create table jender (jdoc jsonb);
insert into jender (jdoc) select ('{"gender": "'||gender||'"}')::jsonb from gender;
create index jender_hash on jender using gin (jdoc jsonb_path_ops);
=> \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------+-------+-------+-------+-------------
public | gender | table | olleg | 35 MB |
public | jender | table | olleg | 54 MB |
(2 rows)
=> \di+
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+--------------+-------+-------+--------+---------+-------------
public | gender_btree | index | olleg | gender | 21 MB |
public | gender_hash | index | olleg | gender | 47 MB |
public | jender_hash | index | olleg | jender | 1104 kB |
(3 rows)
Very much better. What about to copy paste algorithm from gin(jsonb_path_ops) to the hash index?
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2018-11-13 19:37:23 | Re: WTF with hash index? |
Previous Message | Ron | 2018-11-13 18:47:32 | Re: WTF with hash index? |