postgresqlindexingpostgresql-performancepartial-index

Is it possible to index the position of an array column in PostgreSQL?

Let's say I want to find rows in the table my_table that have the value 5 at the first position of the array column my_array_column. To prepare the table, I executed the following statements:

CREATE TABLE my_table (
    id serial primary key,
    my_array_column  integer[]
);

CREATE INDEX my_table_my_array_column_index on "my_table" USING GIN ("my_array_column");

SET enable_seqscan TO off;

INSERT INTO my_table (my_array_column) VALUES ('{5,7,10}');

Now, the query can look like this:

select * from my_table where my_array_column[1] = 5;

This works, but it doesn't use the created GIN index. Is it possible to search for the value 5 at a specific position with an index?

Solution

  • I want to find rows in the table my_table that have the value 5 at the first position of the array column

    A partial index would be most efficient for that definition:

    CREATE INDEX my_table_my_array_special_idx ON my_table ((true))
WHERE my_array_column[1] = 5;

    If only a small fraction of rows qualifies, a partial index is accordingly smaller. Plus, the actual index column only occupies minimum space (typically 8 bytes). And, on top of that, Postgres 13 or later can apply index deduplication to make the index much smaller, yet.

    Once the index is fully cached, its small size does not make it much faster, but still.
    And most writes do not have to manipulate the index, which may be the most important benefit, depending on the workload.

    Oh, and Postgres collects statistics for a partial index. So you can expect the query planner to make a fully educated choice when that index is involved.

    Related:

    It's applicable when the query repeats the same condition.

    Typically, you have something useful as index field on top of your declared purpose. But if you don't, just use any small constant - true in my example, but anything < 8 bytes is equally good.

    Minor disclaimer: The "first position" in a Postgres array does not necessarily have index 1. If non-standard array indexes are possible, consider:

    ...
WHERE (my_array_column[:])[1] = 5;

    In index and queries.

    See: