2014-02-15 21:52 GMT+01:00 AlexK <alkuzo@xxxxxxxxx>:
postgres=# do
$$
declare a int := 0;
begin
for i in 1..100000
loop
a := a + 1;
end loop;
end;
$$;
DO
Time: 88.481 ms
Hi Pavel,1. I believe we have lots of memory. How much is needed to read one array of 30K float number?
it is not too much - about 120KB
2. What do we need to avoid possible repeated detost, and what it is?
any access to array emits detoast - so repeated access to any field in array is wrong. You can read a complete array as one block, or you can evaluate a array as table - and then detost is processed only once.
3. We are not going to update individual elements of the arrays. We might occasionally replace the whole thing. When we benchmarked, we did not notice slowness. Can you explain how to reproduce slowness?
you can see this example
postgres=# do
$$
declare a int[] := '{}';
begin
for i in 1..1000
loop
a := a || i;
end loop;
end;
$$;
postgres=# do
$$
declare a int[] := '{}';
begin
for i in 1..1000
loop
a := a || i;
end loop;
end;
$$;
This code is fast only for small arrays
10K ~ 100ms
100K ~ 10000ms
postgres=# do
$$
declare a int := 0;
begin
for i in 1..100000
loop
a := a + 1;
end loop;
end;
$$;
DO
Time: 88.481 ms
overhead of plpgsql cycle is about 100ms
but you can generate a array by fast way (but should not be by update)
postgres=# select array_upper(array(select generate_series(1,100000)),1);
array_upper
─────────────
100000
(1 row)
Time: 19.441 ms
postgres=# select array_upper(array(select generate_series(1,100000)),1);
array_upper
─────────────
100000
(1 row)
Time: 19.441 ms
Pg can manipulate with large arrays relatively fast
postgres=# select max(unnest) from (select unnest(array(select generate_series(1,100000)))) x;
max
────────
100000
(1 row)
Time: 96.644 ms
postgres=# select max(unnest) from (select unnest(array(select generate_series(1,100000)))) x;
max
────────
100000
(1 row)
Time: 96.644 ms
but it should be a block operations
Regards
Pavel
TIA!On Fri, Feb 14, 2014 at 11:03 PM, Pavel Stehule [via PostgreSQL] <[hidden email]> wrote:
HelloI worked with 80K float fields without any problem.There are possible issues:* needs lot of memory for detoast - it can be problem with more parallel queries
* there is a risk of possible repeated detost - some unhappy usage in plpgsql can be slow - it is solvable, but you have to identify this issue* any update of large array is slow - so these arrays are good for write once data
RegardsPavel2014-02-14 23:07 GMT+01:00 lup <[hidden email]>:
Would 10K elements of float[3] make any difference in terms of read/write--
performance?
Or 240K byte array?
Or are these all functionally the same issue for the server? If so,
intriguing possibilities abound. :)
View this message in context: http://postgresql.1045698.n5.nabble.com/Is-it-reasonable-to-store-double-arrays-of-30K-elements-tp5790562p5792099.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list ([hidden email])
http://postgresql.1045698.n5.nabble.com/Is-it-reasonable-to-store-double-arrays-of-30K-elements-tp5790562p5792144.htmlIf you reply to this email, your message will be added to the discussion below:
View this message in context: Re: Is it reasonable to store double[] arrays of 30K elements
