pgsql 10 hash index

testdb=# CREATE TABLE table1 (
testdb(#   "id"     SERIAL NOT NULL,
testdb(#   "value"  TEXT NOT NULL
testdb(# );
CREATE TABLE

testdb=# ALTER TABLE ONLY "table1"
testdb-#   ADD CONSTRAINT "table1_PK_id" PRIMARY KEY ("id"),
testdb-#   ADD CONSTRAINT "table1_UN_value" UNIQUE ("value");
ALTER TABLE

testdb=# CREATE TABLE table2 (
testdb(#   "id"     SERIAL UNIQUE NOT NULL,
testdb(#   "value"  TEXT UNIQUE NOT NULL
testdb(# );
CREATE TABLE

testdb=# ALTER TABLE ONLY "table2"
testdb-#   ADD CONSTRAINT "table2_PK_id" PRIMARY KEY ("id"),
testdb-#   ADD CONSTRAINT "table2_UN_value" UNIQUE ("value");
ALTER TABLE

testdb=# CREATE INDEX table2_hash_value ON "table2" USING hash ("value");
CREATE INDEX

testdb=# CREATE FUNCTION fill_data()
testdb-# RETURNS VOID
testdb-# LANGUAGE plpgsql
testdb-#   AS $$
testdb$#   DECLARE
testdb$#     i INTEGER;
testdb$#     p TEXT;
testdb$#   BEGIN
testdb$#     FOR i IN 1..50000 LOOP
testdb$#       p := 'zzz'||log(i)||'xxx';
testdb$#       INSERT INTO "table1" ("value") VALUES (p);
testdb$#       INSERT INTO "table2" ("value") VALUES (p);
testdb$#     END LOOP;
testdb$#   END;
testdb$#   $$;
CREATE FUNCTION

testdb=# select fill_data();
 fill_data
-----------

(1 row)

testdb=# REINDEX DATABASE testdb;
REINDEX

testdb=# \d+
                            List of relations
 Schema |     Name      |   Type   |  Owner   |    Size    | Description
--------+---------------+----------+----------+------------+-------------
 public | table1        | table    | postgres | 2976 kB    |
 public | table1_id_seq | sequence | postgres | 8192 bytes |
 public | table2        | table    | postgres | 2976 kB    |
 public | table2_id_seq | sequence | postgres | 8192 bytes |
(4 rows)

testdb=# \di+
                               List of relations
 Schema |       Name        | Type  |  Owner   | Table  |  Size   | Description
--------+-------------------+-------+----------+--------+---------+-------------
 public | table1_PK_id      | index | postgres | table1 | 1112 kB |
 public | table1_UN_value   | index | postgres | table1 | 2008 kB |
 public | table2_PK_id      | index | postgres | table2 | 1112 kB |
 public | table2_UN_value   | index | postgres | table2 | 2008 kB |
 public | table2_hash_value | index | postgres | table2 | 2064 kB |
 public | table2_id_key     | index | postgres | table2 | 1112 kB |
 public | table2_value_key  | index | postgres | table2 | 2008 kB |
(7 rows)

testdb=# explain analyze verbose select id from table1 where value = 'zzz3xxx';
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Index Scan using "table1_UN_value" on public.table1  (cost=0.41..8.43 rows=1 width=4) (actual time=0.036..0.037 rows=1 loops=1)
   Output: id
   Index Cond: (table1.value = 'zzz3xxx'::text)
 Planning time: 0.101 ms
 Execution time: 0.063 ms
(5 rows)

testdb=# explain analyze verbose select id from table2 where value = 'zzz3xxx';
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Index Scan using table2_hash_value on public.table2  (cost=0.00..8.02 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=1)
   Output: id
   Index Cond: (table2.value = 'zzz3xxx'::text)
 Planning time: 0.100 ms
 Execution time: 0.040 ms
(5 rows)

testdb=# explain analyze verbose select id from table1 where value = 'wtfomg';
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Index Scan using "table1_UN_value" on public.table1  (cost=0.41..8.43 rows=1 width=4) (actual time=0.038..0.038 rows=0 loops=1)
   Output: id
   Index Cond: (table1.value = 'wtfomg'::text)
 Planning time: 0.124 ms
 Execution time: 0.078 ms
(5 rows)

testdb=# explain analyze verbose select id from table2 where value = 'wtfomg';
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Index Scan using table2_hash_value on public.table2  (cost=0.00..8.02 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=1)
   Output: id
   Index Cond: (table2.value = 'wtfomg'::text)
 Planning time: 0.140 ms
 Execution time: 0.039 ms
(5 rows)