Is there a performance difference between using ARRAY[] and '{}'::int[] to create an array?

Question

What's the difference between using ARRAY[] and '{}'::int[] when it comes to performance? Is one of them better than the other?

I'm using functions which contains queries that use conditions like:

  AND ('{13,57,30,59}'::int[] && _user_profiles)

where is necessary to compare an array (_user_profiles in the example) against a static array ('{13,57,30,59}'::int[]).

This way to compare replies in almost every one of these queries. So what would be better to use?

AND ('{13,57,30,59}'::int[] && _user_profiles)

(OR)

AND (ARRAY[13,57,30,59] && _user_profiles)

Just try it explain (analyze) select '{13,57,30,59}'::int[] from generate_series(1,10e6) and explain (analyze) select ARRAY[13,57,30,59] from generate_series(1,10e6) — user330315
– user330315, Commented Jan 27, 2020 at 6:50

Laurenz Albe · Accepted Answer · 2020-01-27 06:45:15Z

1

Both forms are evaluated at parse time and the result is the same, so there shouldn't be any difference.

To see that for yourself, run both queries with EXPLAIN (VERBOSE) and check for differences in the execution plan.

edited Jan 27, 2020 at 6:45

answered Jan 23, 2020 at 17:36

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

3 Comments

juan_carlos_yl Over a year ago

Thanks for taking the time to answer. Is your answer based on any documentation or is it based on your expertise?

Laurenz Albe Over a year ago

Sorry for the lack of explanation. I have added a sentence recommending EXPLAIN to check the execution plan.

juan_carlos_yl Over a year ago

Also used the EXPLAIN (ANALYZE) suggested by @a_horse_with_no_name and got this result: imgur.com/a/ee3tYPu So I'll take them as the same in terms of performance.Thank you.

Collectives™ on Stack Overflow

Is there a performance difference between using ARRAY[] and '{}'::int[] to create an array?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related