Which index type is best suited for exact-match against a list of values? I have this table:

CREATE TABLE public.foos (
    id integer NOT NULL,
    bar_string character varying
);


I want to do this query:

SELECT id from foos where bar_string in ('one', 'two', 'three')


The size of the list will be something like 5-100.

If I know I'll only ever be doing this query, what's the best index to put on bar_string? I've read that Hash indexes are better than btree for exact matches, is this the way to go?
TL;DR: BTREE looks like it is a bit more efficient than HASH. You should read and adapt this test to your situation

Benchmark

I tried the two solutions for a geohash id column. Here is the script I used:

require 'set'

def random_geohash(precision)
  letters = %w(0 1 2 3 4 5 6 7 8 9 b c d e f g h j k m n p q r s t u v w x y z)

  letters.sample(precision).join('')
end

geohashes = Set.new

loop do
  geohashes &lt;&lt; random_geohash(9)
  break if geohashes.length == 50_000
end


sample = geohashes.to_a.sample(1_000).map { |h| "'%s'" % h }.join(", ")

IO.write("perfs.init.sql", &lt;&lt;~SQL)
  DROP TABLE IF EXISTS "tests";

  CREATE TABLE "tests" (
  "id_btree" text NOT NULL,
  "id_hash" text NOT NULL
  );

  CREATE INDEX "index_id_btree" ON tests USING btree (id_btree);
  CREATE INDEX "index_id_hash" ON tests USING hash (id_hash);

  INSERT INTO tests (id_btree, id_hash) VALUES #{geohashes.map { |h| "('%s', '%s')" % [h,h] }.join(", ")};
  VACUUM ANALYZE;
SQL

IO.write("perfs.btree.sql", &lt;&lt;~SQL)
  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
  SELECT * FROM tests WHERE id_btree IN (#{sample});
SQL

IO.write("perfs.hash.sql", &lt;&lt;~SQL)
  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
  SELECT * FROM tests WHERE id_hash IN (#{sample});
SQL

IO.write("perfs.end.sql", &lt;&lt;~SQL)
  DROP TABLE IF EXISTS "tests";
SQL


`psql --user=john_doe -qAt -f perfs.init.sql my_db`
`psql --user=john_doe -qAt -f perfs.btree.sql my_db &gt; perfs.btree.json`
`psql --user=john_doe -qAt -f perfs.hash.sql my_db &gt; perfs.hash.json`
`psql --user=john_doe -qAt -f perfs.end.sql my_db`


This will give you two explain analyze (perfs.&lt;index&gt;.json) that you can now read. I suggest using Pev for a comfortable lecture. Here are the text results for PG11 on my mac:

BTREE

Index Scan using index_id_btree on public.tests  (cost=0.29..2611.50 rows=1000 width=20) (actual time=0.988..4.347 rows=1000 loops=1)
  Output: id_btree, id_hash
  Index Cond: (tests.id_btree = ANY ('{e6g8nf3kv,...,3r9n5udye}'::text[]))
  Buffers: shared hit=3014
Planning Time: 1.475 ms
Execution Time: 4.424 ms


HASH

Bitmap Heap Scan on public.tests  (cost=1179.75..2758.75 rows=1000 width=20) (actual time=1.784..15.275 rows=1000 loops=1)
  Output: id_btree, id_hash
  Recheck Cond: (tests.id_hash = ANY ('{e6g8nf3kv,...,3r9n5udye}'::text[]))
  Heap Blocks: exact=304
  Buffers: shared hit=1463
  -&gt;  Bitmap Index Scan on index_id_hash  (cost=0.00..1179.50 rows=1000 width=0) (actual time=1.714..1.714 rows=1000 loops=1)
        Index Cond: (tests.id_hash = ANY ('{e6g8nf3kv,...,3r9n5udye}'::text[]))
        Buffers: shared hit=1159
Planning Time: 1.495 ms
Execution Time: 15.446 ms