2012/6/26 Marc Mamin <M.Mamin@xxxxxxxxxxxx>: > > >> -----Original Message----- >> From: Pavel Stehule [mailto:pavel.stehule@xxxxxxxxx] >> >> 2012/6/26 Marc Mamin <M.Mamin@xxxxxxxxxxxx>: >> > >> >>> On 22/06/12 09:02, Maxim Boguk wrote: >> > >> >>> May be I completely wrong but I always assumed that the access >> speed to the array element in PostgreSQL should be close to constant >> time. >> >>> But in tests I found that access speed degrade as O(N) of array >> size. >> > >> >>> Is that behaviour is correct? >> >> yes - access to n position means in postgresql - skip n-1 elements > > > Hmmm... > > how many elements to be skipped here ? there are two independent stages: a) detoast - loading and decompression (complete array is detoasted) b) access if you has very large arrays, then @a is significant Regards Pavel > > SELECT _array[1] FROM t2; > > I wonder if the time rather get spent in first retrieving the array itself before accessing its elements. > > regards, > > Marc Mamin > >> >> Regards >> >> Pavel >> >> > >> > >> >> From: pgsql-performance-owner@xxxxxxxxxxxxxx On Behalf Of Jesper >> Krogh >> > >> >> Default column storage is to "compress it, and store in TOAST" with >> large values. >> >> This it what is causing the shift. Try to change the column storage >> of the column >> >> to EXTERNAL instead and rerun the test. >> > >> > >> > Hello, >> > >> > I've repeated your test in a simplified form: >> > you are right :-( >> > >> > create table t1 ( _array int[]); >> > alter table t1 alter _array set storage external; >> > insert into t1 SELECT ARRAY(SELECT * FROM generate_series(1,50000)); >> > >> > create table t2 ( _array int[]); >> > alter table t2 alter _array set storage external; >> > insert into t2 SELECT ARRAY(SELECT * FROM >> generate_series(1,5000000)); >> > >> > explain analyze SELECT _array[1] FROM t1; >> > Total runtime: 0.125 ms >> > >> > explain analyze SELECT _array[1] FROM t2; >> > Total runtime: 8.649 ms >> > >> > >> > best regards, >> > >> > Marc Mamin >> > >> > >> > >> > -- >> > Sent via pgsql-performance mailing list (pgsql- >> performance@xxxxxxxxxxxxxx) >> > To make changes to your subscription: >> > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
