Join Performance with PostgreSQL "array containment"

Comparing the performance of joining via "array containment" @> with the performance of using an additional table instead.

In PostgreSQL, it is possible to define table columns with an array type. While this approach is more concise than explicitly modelling a 1:n relation with an additional table, be aware that this might have a negative impact on join performance.

For instance, the following simple table holds a text array, together with an ID:

CREATE TABLE array_holder (
  id uuid NOT NULL,
  some_array text[] NOT NULL
);

INSERT INTO array_holder VALUES (gen_random_uuid(), '{foo, bar}');

|   id   | some_array |
| ------ | ---------- |
| <id_1> | {foo, bar} |

By using the PostgreSQL array containment operator (written as @>), we can determine those rows whose array contains a given string:

SELECT * FROM array_holder WHERE some_array @> '{foo}';

We can use the same syntax for joining another table:

CREATE TABLE text_holder (
  id uuid NOT NULL,
  some_text text NOT NULL
);

INSERT INTO text_holder VALUES(gen_random_uuid(), 'foo');

|   id   | some_text |
| ------ | --------- |
| <id_2> | foo       |

SELECT * FROM text_holder th
JOIN array_holder ah
ON ah.some_array @> ARRAY[th.some_text];

|   id   | some_text | id       | some_array |
| ------ | --------- | -------- | ---------- |
| <id_1> | foo       | <uuid_2> | {foo, bar} |

Now let’s populate the tables with a larger number of entries:

DO $FN$
BEGIN
  FOR counter IN 1..2000000 LOOP
    INSERT INTO text_holder VALUES (gen_random_uuid(), counter);
    INSERT INTO array_holder VALUES (gen_random_uuid(), ARRAY[counter]);
  END LOOP;
END;
$FN$;

Depending on your machine, executing the following join with this larger dataset might take a while:

SELECT count(*) FROM text_holder th
JOIN array_holder ah
ON ah.some_array @> ARRAY[th.some_text];

In my case, I cancelled the query after 3 minutes.

Creating the following index improves the query performance to about 6 seconds:

CREATE INDEX idx_array_holder_some_array
ON array_holder USING gin (some_array);

Compare this with the following setup that uses a separate table instead of the array type (after dropping the previously created index):

DROP INDEX idx_array_holder_some_array;

CREATE TABLE array_holder_values (
  fk_array_holder uuid NOT NULL,
  some_array_value text NOT NULL
);

INSERT INTO array_holder_values
SELECT id, unnest(some_array) FROM array_holder;

| fk_array_holder | some_array_value |
| --------------- | ---------------- |
| <id_1>          | 1                |
| <id_2>          | 2                |
| <id_3>          | 3                |

SELECT count(*) FROM text_holder th
JOIN array_holder_values ahv
ON th.some_text = ahv.some_array_value
JOIN array_holder ah
ON ahv.fk_array_holder = ah.id;

This last query now takes just 1.5 seconds, instead of the previous 6 seconds.