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

Question

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[]

Answer 1

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.

Answer 2

That index can be used on both queries you show, although it is only the first column in the index ("name") that can get used for them. But unless you also have an index on "name" alone, then I would think foo_text would get quite a lot of use, just for the sake of its first column.

It can be used on the "things" column as well, but only when that column is tested for equality or inequality. Not when it is tested with ANY or @>. So for example it is used to good effect here:

where name='AA' and things='{6rL}';

(Assuming there are many ties for name='AA' which need to be broken)

I've also noticed that this index takes up a LOT of space relative to the other indexes and is much slower to vacuum.

Maybe yours is bloated. I don't find my copy of it to be any slower to VACUUM, nor find it to be larger than I would expect.