Most efficient array notation in PostgreSQL

Question

Using arrays as constant, what is more efficient:

array[1,2,3]

or

'{1,2,3}'::int[]`

?

Or, more complex:

array['1812-01-01'::date, '1812-02-02']

or

'{1812-01-01,1812-02-02}'::date[]

?

I really scare to dip to the PostgreSQL sources despite the fact that it is wonderful.

Update

Partial answer, based on the investigation provided by @VaoTsun (thanks a lot!) Surprisingly '{1,2,3}'::int[] is faster then array[1,2,3]. Dixi!

Efficient computation-wise? They don't define one as being more efficient. If there's a significant difference, which btw I doubt, it's subject to change in later versions. — sudo
– sudo, Commented May 14, 2018 at 0:05
@sudo It was "minds on the road"... In particular, what you prefer: '{1,2}::int[]' or array[1,2] and why? — Abelisto
– Abelisto, Commented May 14, 2018 at 0:09
As all those expressions are converted at parse time they have no influence whatsoever on the run time of a query. So I don't think this is of any importance. — user330315
– user330315, Commented May 14, 2018 at 6:15
@Abelisto but you can compare results statistically with big nambers, to measure the measurement error itself. I tried 10 loops to see that for all 10 array[ is slightly slower - you can try 10K times - if rate remains, I think you can expect its not just a measurement error. — Vao Tsun
– Vao Tsun, Commented May 14, 2018 at 6:29

Vao Tsun · Accepted Answer · 2018-05-14 06:19:59Z

1

on my machine the array[] is always slightly slower then '{}' one:

so=# do $$
declare
 t text;
 ts timestamptz;
begin
 for n in 1..10 loop
  select clock_timestamp() into ts;
  for i in 1..99999 loop
   execute format($f$select array['18%s-01-01'::date, '18%s-02-02']$f$,i,i) into t;
  end loop;
  raise info '%', t||' "[" '||clock_timestamp() - ts;

  select clock_timestamp() into ts;
  for i in 1..99999 loop
   execute format($f$select '{18%s-01-01,18%s-02-02}'::date[]$f$,i,i) into t;
  end loop;
  raise info '%', t||' "{" '||clock_timestamp() - ts;
 end loop;
end; $$;
INFO:  {1899999-01-01,1899999-02-02} "[" 00:00:01.99259
INFO:  {1899999-01-01,1899999-02-02} "{" 00:00:01.691473
INFO:  {1899999-01-01,1899999-02-02} "[" 00:00:02.207583
INFO:  {1899999-01-01,1899999-02-02} "{" 00:00:01.762358
INFO:  {1899999-01-01,1899999-02-02} "[" 00:00:01.926091
INFO:  {1899999-01-01,1899999-02-02} "{" 00:00:01.685358
INFO:  {1899999-01-01,1899999-02-02} "[" 00:00:01.98542
INFO:  {1899999-01-01,1899999-02-02} "{" 00:00:01.686831
INFO:  {1899999-01-01,1899999-02-02} "[" 00:00:02.01972
INFO:  {1899999-01-01,1899999-02-02} "{" 00:00:01.698365
INFO:  {1899999-01-01,1899999-02-02} "[" 00:00:02.008609
INFO:  {1899999-01-01,1899999-02-02} "{" 00:00:01.698494
INFO:  {1899999-01-01,1899999-02-02} "[" 00:00:01.987951
INFO:  {1899999-01-01,1899999-02-02} "{" 00:00:01.698711
INFO:  {1899999-01-01,1899999-02-02} "[" 00:00:01.977347
INFO:  {1899999-01-01,1899999-02-02} "{" 00:00:01.707921
INFO:  {1899999-01-01,1899999-02-02} "[" 00:00:01.945438
INFO:  {1899999-01-01,1899999-02-02} "{" 00:00:01.663771
INFO:  {1899999-01-01,1899999-02-02} "[" 00:00:02.079186
INFO:  {1899999-01-01,1899999-02-02} "{" 00:00:01.752366
DO
Time: 37178.056 ms

Implicitly docs suggest the"curly" way to introduce arrays in the first line:

https://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-INPUT

To write an array value as a literal constant, enclose the element values within curly braces and separate them by commas.

And later: https://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS

An array constructor is an expression that builds an array value using values for its member elements.

So you can expect building the curly values from array construct to take additional tick. But surely I'm just speculating here. The answer was meant only to measure linear impact of both ways mixed close in time on no load PC...

Also IN (scalar list) is rewritten to = ANY(array) construct in "curly" format:

so=# explain analyse select * from pg_database where datname in ('t','so');
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Seq Scan on pg_database  (cost=0.00..1.02 rows=2 width=254) (actual time=0.105..0.109 rows=2 loops=1)
   Filter: (datname = ANY ('{t,so}'::name[]))

I'm not saying it's an implication to do same, but it looks internally chosen way over array[ construct, so might be a hint

edited May 14, 2018 at 6:19

answered May 14, 2018 at 6:12

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

5 Comments

Abelisto Over a year ago

I am really surprised. Coming from the compilable language like C/Pascal I was sure that "ready to use" forms like array[...] are faster then forms that needs additional parsing like '{1,2,3}'. But nope. Second one is about 5 times faster then first one for some reasons (pastebin.com/0jTdvPph)[https://pastebin.com/0jTdvPph]. Just interesting: why?

Vao Tsun Over a year ago

I did not look into the code, so I'm speculating in docs here. the array[ construct is not ready to use, but to be first used to build '{value and then parsed. I'm afraid looking into the source is unavoidable here :)

Abelisto Over a year ago

Ok, thats it. Previously I preferred to use array[...] and for now I know why my queries was so slow :)

Vao Tsun Over a year ago

happy you, I wish I'd find my golden little key :)

Abelisto Over a year ago

It will be at the end of everything >:-E Be patient ;) And good luck of course!

Collectives™ on Stack Overflow

Most efficient array notation in PostgreSQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related