I have a message table in Postgres 9.4 that contains a words field of array type, with random words of a message. Currently I have millions of messages:

\d messages
                        Table "public.messages"
            Column            |            Type             | Modifiers
 id_str                       | character varying(255)      | not null
 feed_id                      | integer                     |
 message                      | character varying(255)      |
 posted_at                    | timestamp without time zone |
 words                        | character varying(255)[]    |
    "messages_pkey" PRIMARY KEY, btree (id_str)
    "index_messages_on_feed_id" btree (feed_id)
    "index_messages_on_feed_id_posted_at" btree (feed_id, posted_at DESC NULLS LAST)
    "index_messages_on_words" gin (words)

Now I have an array of words [A, B, C].
I need to find the number of messages that the words contains each of the combinations of the list, i.e. [[A,B], [A,C], [B,C]].

Also I have this combination array created outside of the Postgres query already.

I can do this by creating 3 queries:

select count(*) from messages where words @> ARRAY['A','B']::varchar[];
select count(*) from messages where words @> ARRAY['A','C']::varchar[];
select count(*) from messages where words @> ARRAY['B','C']::varchar[];

Is there a way to do this in one query? And output the result in the following format?

A, B, count or [A, B], count
A, C, count
B, C, count


Here's the query I run currently:



And this one is using Erwin Brandstetter 's method:



Now assume the messages table has more than 1 Million records, the CTE scan or seq scan in each of the query plan will become very slow.

  • To me it sounds like GROUP BY is your friend. Apr 17, 2015 at 22:17
  • Please always provide your version of Postgres. And the actual table definition (\d tbl in psql) is superior to describing the table definition with free text. Apr 18, 2015 at 23:22
  • What is the mean length of elements in words? How many elements on average in words? How many unique elements in your search? You mentioned 1000 combinations? That would be ~ 45 distinct words in one search? You can probably make this (substantially) faster, but it will take some time to find 1000 combinations in a million arrays. the fiddles don't seem to work (the site may have problems), please include the query to work with in your question. Apr 21, 2015 at 19:30
  • Currently the number of elements in messages.words are around 15-20. And I have 50 distinct words, which is 1225 combinations, and this 1225 combinations are pre calculated. so I have this 1225 combination array ready. The problem right now is the performance of the messages.words @> '{A, B}' even with the gin index. it still does a seq scan on it. not sure how to improve this. Apr 22, 2015 at 13:49
  • The sql fiddle should work, can you try copy and paste the code again? or add or remove the semi colon as end? Sometimes I have to do this to make the fiddle to work. Apr 27, 2015 at 19:05

1 Answer 1


To get all unique pairs of elements from an array of arbitrary length:

WITH a(a) AS (SELECT '{A,B,C,D}'::text[])  -- provide array here
,    i(i) AS (SELECT i FROM a, generate_series(1, array_upper(a.a,1)) i)
SELECT ARRAY[a[i1.i], a[i2.i]] AS pair
FROM   i i1
JOIN   i i2 ON i2 > i1
,      a;

You can then join to the message table.

Without knowing any details of your setup, my educated guess is that a LATERAL join will be fastest as it can use the GIN index on messages.words - create it if you don't have one yet.

WITH a(a) AS (SELECT '{A,B,C,D}'::text[])
,    i(i) AS (SELECT i FROM a, generate_series(1, array_upper(a.a,1)) i)
SELECT p.pair, c.ct
   SELECT ARRAY[a[i1.i], a[i2.i]] AS pair
        , i1.i AS i1, i2.i AS i2
   FROM   i i1
   JOIN   i i2 ON i2 > i1
   ,      a
   ) p
   SELECT count(*) AS ct
   FROM   message
   WHERE  words @> p.pair
   ) c
ORDER BY p.i1, p.i2;

SQL Fiddle.

  • Thanks for the response, and sorry for the confusing in my question. I am using postgres 9.4. And I already have the combination array from my code, ie. ({'A', 'B'}, {'A', 'C'}, {'B','C'}). so I don't have to use postgres to construct this. What I did is I used right join (VALUES #{words_values}) AS t(words) ON messages.words @> t.words" then group by t.words and select count. But this is very slow is I have 1 million messages. Here's the explain: explain.depesz.com/s/KlL Apr 20, 2015 at 15:13
  • I also tried your solution, it works, but has the same problem, it's very slow when the combination array have over 1000 items, and the messages table is over a million records. Here's what I did: sqlfiddle.com/#!15/6a277/1/0 Apr 20, 2015 at 15:57
  • I omitted the filters on the messages in the sql fiddle. Apr 20, 2015 at 16:02
  • the actual explain: explain.depesz.com/s/qXJo is there anyway to use any index instead of the seq scan? Apr 20, 2015 at 16:56
  • @ZhaohanWeng: There may be a type mismatch. Are search terms and column exactly the same data type? Apr 20, 2015 at 17:00

Your Answer

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

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