Performance of IN (Array) in PostgreSQL

Question

For example I have this table

Person
id: integer (primary key)
name: varchar
workplace_id: integer

and I want to find the name of people which work in some places, so I used this query

SELECT name FROM Person WHERE workplace_id IN(/*values*/)

Is it better to sort the values first? Or does sorting the values not make any difference in performance? How about the time complexity of the IN operator? If sorting makes better performance, is it considered a premature optimization and should be avoided?

Laurenz Albe · Accepted Answer · 2016-10-10 11:22:02Z

2

Sorting the list of values will probably be a net loss, since sorting costs time and the ordering of the list won't influence the performance.

The best optimization would be an index on workplace_id.

If the number of values is really large, it might be better to create a temporary table with the list values and join that table with your table.

To test all these options, use EXPLAIN and EXPLAIN (ANALYZE) that way the database will tell you how it solves the problem and how long each step takes.

answered Oct 10, 2016 at 11:22

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Comments

Durante · Accepted Answer · 2016-10-10 11:20:30Z

0

The in operator check all element in the array , for performance you maybe change the statement to use exist with another table.

SELECT name FROM Person a WHERE exist (select 1 from table b where b.workplace_id = a.workplace_id);

This going to check first match only.

answered Oct 10, 2016 at 11:20

Durante

3052 silver badges13 bronze badges

5 Comments

Capi Etheriel Over a year ago

do you have any source documenting this behavior?

Durante Over a year ago

@CapiEtheriel yes, you can see it here: "The subquery will generally only be executed long enough to determine whether at least one row is returned, not all the way to completion." References: - postgresql.org/docs/9.0/functions-subquery.html#AEN16782 - postgresql.org/docs/9.0/functions-comparisons.html#AEN16964

Capi Etheriel Over a year ago

and what about the IN behavior?

Durante Over a year ago

postgresql.org/docs/9.0/functions-comparisons.html#AEN16964

Capi Etheriel Over a year ago

the IN documentation says it is the same as COLUMN = VALUE1 OR COLUMN = VALUE2 OR .... now, the OR evaluation order is unpredictable but it seems to short circuit indeed (but not to the left, as devs would expect). see postgresql.org/docs/current/…

Collectives™ on Stack Overflow

Performance of IN (Array) in PostgreSQL

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related