Re: Performance of a large array access by position (tested version 9.1.3)

>> 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?

> From: pgsql-performance-owner(at)postgresql(dot)org 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