| 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? |
