4

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)[]    |
 Indexes:
    "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

Edit:

Here's the query I run currently:

http://sqlfiddle.com/#!15/7c907/2/0

http://explain.depesz.com/s/Dot

And this one is using Erwin Brandstetter 's method:

http://sqlfiddle.com/#!15/7c907/1/0

http://explain.depesz.com/s/pr2

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. – dezso Apr 17 '15 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. – Erwin Brandstetter Apr 18 '15 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. – Erwin Brandstetter Apr 21 '15 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. – Zhaohan Weng Apr 22 '15 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. – Zhaohan Weng Apr 27 '15 at 19:05
2

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
FROM  (
   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
, LATERAL (
   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 – Zhaohan Weng Apr 20 '15 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 – Zhaohan Weng Apr 20 '15 at 15:57
  • I omitted the filters on the messages in the sql fiddle. – Zhaohan Weng Apr 20 '15 at 16:02
  • the actual explain: explain.depesz.com/s/qXJo is there anyway to use any index instead of the seq scan? – Zhaohan Weng Apr 20 '15 at 16:56
  • @ZhaohanWeng: There may be a type mismatch. Are search terms and column exactly the same data type? – Erwin Brandstetter Apr 20 '15 at 17:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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