| From: | Олег Самойлов <splarv(at)ya(dot)ru> | 
|---|---|
| To: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> | 
| Subject: | WTF with hash index? | 
| Date: | 2018-11-13 16:42:07 | 
| Message-ID: | A841C4BC-A878-497E-AD9B-4DE0830DCC68@ya.ru | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
CentOS 7
$ rpm -q postgresql10
postgresql10-10.6-1PGDG.rhel7.x86_64
SQL script for psql:
\set table_size 1000000
begin;
create table gender (gender varchar);
insert into gender (gender) select case when random<0.50 then 'female' when random<0.99 then 'male' else 'other' end from (select random() as random, generate_series(1,:table_size)) as subselect;
create index gender_btree on gender using btree (gender);
create index gender_hash on gender using hash (gender);
commit;
vacuum full analyze;
Vacuum full is not necessary here, just a little vodoo programming. I expected that the hash index will be much smaller and quicker than the btree index, because it doesn’t keep values inside itself, only hashes. But:
=> \d+
                   List of relations
 Schema |  Name  | Type  | Owner | Size  | Description
--------+--------+-------+-------+-------+-------------
 public | gender | table | olleg | 35 MB |
(1 row)
=> \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 |
(2 rows)
The hash index not only is more than the btree index, but also is bigger than the table itself. What is wrong with the hash index?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Laurenz Albe | 2018-11-13 17:55:49 | Re: WTF with hash index? | 
| Previous Message | Ravi Krishna | 2018-11-13 15:50:36 | Re: Plpgsql search_path issue going from 9.3 to 9.6 |