8

This is what I am currently using:

CREATE FUNCTION array_intersect(a1 anyarray, a2 anyarray) RETURNS anyarray AS $$ SELECT ARRAY( SELECT unnest($1) INTERSECT SELECT unnest($2) ORDER BY 1 ); $$ LANGUAGE sql IMMUTABLE STRICT; --get the length: select array_length ( array_intersect(array[...], array[...]), 1);

Is there a faster way?

Improve this question
4
  • 1
    Doesn't look like it for general purpose arrays. For integer arrays the intarray extension provides the & operator, but there doesn't seem to be a generalized one for all arrays despite the existence of the && boolean test for overlapping arrays. It'd be nice to add, but ... well, take a look at the C source code for the PostgreSQL general purpose array functions and operators to see why there aren't as many as you might expect. The array API is horrid. Commented Feb 6, 2014 at 5:06
  • Thanks Craig. Ideally, I'm trying to get the Jaccard Similarity between two arrays, quickly. Any tips on getting Teodor's smlar extension to compile on Windows? Commented Feb 6, 2014 at 18:32
  • I am struggling with the same issue now. I looked at converting my text elements to integers to use the integer indexing extensions. Did you ever find a way to do this quickly? Commented Sep 23, 2014 at 13:26
  • What is the datatype you're using? How big are the arrays? Commented Jan 29, 2018 at 22:49

1 Answer 1

Reset to default
2

  1. You say you're looking for "Jaccard Similarity between two arrays"

    You may consider MadLib which provides this as dist_jaccard()

    Jaccard distance between two varchar vectors treated as sets.

  2. You may want to try it without STRICT. That may be slowing it down.

  3. You may want to use INTERSECT ALL Especially if you know you don't have dupes in $1.

  4. As Craig Ringer said in the comments you may want out intarray and consider using &.

To give a more accurate answer here,

  • What are the datatypes?
  • How many elements are we talking about on each array?
  • How much exclusion does the INTERSECT provide?
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.