does a btree index on an array field actually do anything?

In PG you can create a table like

CREATE TABLE foo (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid (),
  name text NOT NULL,
  things text[] NOT NULL DEFAULT ARRAY[] ::text[]
)

and a related index via

CREATE INDEX foo_text ON foo USING btree (name, things);

but it's very hard to find information on what this does. I've inherited a table like this (hundreds of millions of rows, though this array almost always has 0 or 1 entries in it), and the index DOES get an occasional hit in pg_stat_all_indexes, so there is at least some scenario when this can happen, but I've also noticed that this index takes up a LOT of space relative to the other indexes and is much slower to vacuum. Does a btree index on an array field in PG make any sense?

If the goal is to be able to find rows where things contains a provided value or values is there a better scheme? (Assume we cannot normalize this properly to its own table at this time.)

The queries we would expect would hit this index looks like

SELECT id FROM foo
WHERE name = $1
AND $2::text = ANY(things)

SELECT id FROM FOO
WHERE name = $1
AND things @> $2::text[]

A B-tree index on an array cannot speed up the queries in your question. Somebody must have created them by mistake.

The B-tree index should only index name. If that condition already narrows down the result set a lot, that would be good enough. If you need an index to speed up the condition on things as well, create a separate GIN index on that column.

Note that only the condition using the @> operator can use the index, the condition on things in your first query cannot be indexed.

StackExchange Database Administrators Q#308760, answer score: 5