Postgres Fastest way to compare arrays

Question

In a stored procedure, I am generating an array of keys and want to compare it to an array of keys off a table. What is the fastest way to compare two arrays to see if both of them have at least one value that matches?

So far, we've tried the following:

Loop over the generated keys and compare them to the key field (slow)
Use array operation overlap (&&) on the arrays

Here is the definition of table: solicitation

id bigint NOT NULL DEFAULT nextval('id_seq'::regclass),
type_id bigint,
name character varying(2000),
key character varying[],
status bigint

Sample Code from Stored Procedure

-- keys = list of generated keys
FOR _id, _name, _type IN
   SELECT rec.eid, rec.domain, rec.name, rec.type_id
   from   solicitation rec 
   where  rec.key && _keys
   and    rec.status in (1201, 1202, 1241)
LOOP
...
END LOOP;

key is indexed.

Naqeeb · Accepted Answer · 2012-12-16 15:29:05Z

2

Using the array overlap operation (&&) was fast but the slow down was due to NULLs in the generated array.

Stripping out the NULLs increased the performance from 6 seconds to 300 milliseconds for a table size in the millions.

answered Dec 16, 2012 at 15:29

Naqeeb

2454 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Taha Jahangir · Accepted Answer · 2012-12-14 15:43:58Z

1

Array operators in postgresql are pretty fast.

But note that you should use GIN index to gain performance improvement.

Most of times, doing jobs in DB server is faster in performance, but note that if your database server is overloaded, creating replicas of web servers is more easy that creating database replicas, so try to move load from DB-servers to web-servers, to better manage load across web servers.

answered Dec 14, 2012 at 15:43

Taha Jahangir

4,9322 gold badges44 silver badges52 bronze badges

2 Comments

Naqeeb Over a year ago

We're using a GIN index already. The issue turned out that the generated array of keys had NULLs in them which slowed down the performance tremendously.

Taha Jahangir Over a year ago

So, strip down NULLs from arrays when writing to Database

Collectives™ on Stack Overflow

Postgres Fastest way to compare arrays

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related