pgsql 10 hash index (large test)

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..800000 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=# explain analyze verbose select fill_data();
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=4) (actual time=72509.791..72509.792 rows=1 loops=1)
   Output: fill_data()
 Planning time: 0.045 ms
 Execution time: 72509.869 ms
(4 rows)

testdb=# REINDEX DATABASE testdb;
REINDEX

testdb=# \d+
                            List of relations
 Schema |     Name      |   Type   |  Owner   |    Size    | Description
--------+---------------+----------+----------+------------+-------------
 public | table1        | table    | postgres | 46 MB      |
 public | table1_id_seq | sequence | postgres | 8192 bytes |
 public | table2        | table    | postgres | 46 MB      |
 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 | 17 MB |
 public | table1_UN_value   | index | postgres | table1 | 31 MB |
 public | table2_PK_id      | index | postgres | table2 | 17 MB |
 public | table2_UN_value   | index | postgres | table2 | 31 MB |
 public | table2_hash_value | index | postgres | table2 | 26 MB |
 public | table2_id_key     | index | postgres | table2 | 17 MB |
 public | table2_value_key  | index | postgres | table2 | 31 MB |
(7 rows)

testdb=# explain analyze verbose select id from table1 where value = 'zzz5xxx';
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Index Scan using "table1_UN_value" on public.table1  (cost=0.42..8.44 rows=1 width=4) (actual time=0.055..0.057 rows=1 loops=1)
   Output: id
   Index Cond: (table1.value = 'zzz5xxx'::text)
 Planning time: 0.433 ms
 Execution time: 0.089 ms
(5 rows)

testdb=# explain analyze verbose select id from table2 where value = 'zzz5xxx';
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Index Scan using table2_hash_value on public.table2  (cost=0.00..8.02 rows=1 width=4) (actual time=0.028..0.029 rows=1 loops=1)
   Output: id
   Index Cond: (table2.value = 'zzz5xxx'::text)
 Planning time: 0.255 ms
 Execution time: 0.051 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.42..8.44 rows=1 width=4) (actual time=0.034..0.034 rows=0 loops=1)
   Output: id
   Index Cond: (table1.value = 'wtfomg'::text)
 Planning time: 0.061 ms
 Execution time: 0.055 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.006..0.006 rows=0 loops=1)
   Output: id
   Index Cond: (table2.value = 'wtfomg'::text)
 Planning time: 0.061 ms
 Execution time: 0.019 ms
(5 rows)