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?
