In PG you can create a table like

  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

WHERE name = $1
AND $2::text = ANY(things)
WHERE name = $1
AND things @> $2::text[]
  • you need a GIN index: postgresql.org/docs/current/gin-builtin-opclasses.html
    – user1822
    Mar 15, 2022 at 19:54
  • I've seen that. What I'm curious about is WHY this index (the btree one) still gets hit occasionally. Under what scenarios does a btree index on an array "work"?
    – Joe
    Mar 15, 2022 at 20:07
  • 1
    The btree index is most likely only used for equality conditions: where things = array['foo', 'bar']
    – user1822
    Mar 15, 2022 at 20:39

2 Answers 2


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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.