[SOLVED] PostgreSQL multi-column index including text and text array columns

PostgreSQL multi-column index including text and text array columns

I have a table containing email data.

The from column is text (character varying).

The to column and the cc column are both arrays of text (also character varying).

These will be searched using entire email address values (no 'like' type searches). I need to return all rows where any of the addresses match one supplied.

Can I add an index that includes all 3 columns? What should the operator class be?

Currently I've tried using GIN and GiST index methods but can't find operator classes that work with those!

Solution

You can do it like this:

CREATE TABLE email (
   "from" text NOT NULL,
   "to" text[] NOT NULL,
   cc text[]
);

CREATE EXTENSION IF NOT EXISTS btree_gin;

CREATE INDEX ON email USING gin ("from", "to", cc);

SET enable_seqscan = off;

EXPLAIN (COSTS OFF) SELECT * FROM email
WHERE "from" = 'mail'
   OR "to" @> ARRAY['mail']
   OR cc @> ARRAY['mail'];

                                             QUERY PLAN                                              
═════════════════════════════════════════════════════════════════════════════════════════════════════
 Bitmap Heap Scan on email
   Recheck Cond: (("from" = 'mail'::text) OR ("to" @> '{mail}'::text[]) OR (cc @> '{mail}'::text[]))
   ->  BitmapOr
         ->  Bitmap Index Scan on email_from_to_cc_idx
               Index Cond: ("from" = 'mail'::text)
         ->  Bitmap Index Scan on email_from_to_cc_idx
               Index Cond: ("to" @> '{mail}'::text[])
         ->  Bitmap Index Scan on email_from_to_cc_idx
               Index Cond: (cc @> '{mail}'::text[])