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

2012/6/26 Marc Mamin <M(dot)Mamin(at)intershop(dot)de>:
>
>
>> -----Original Message-----
>> From: Pavel Stehule [mailto:pavel(dot)stehule(at)gmail(dot)com]
>>
>> 2012/6/26 Marc Mamin <M(dot)Mamin(at)intershop(dot)de>:
>> >
>> >>> 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(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
>> >
>> >
>> >
>> > --
>> > Sent via pgsql-performance mailing list (pgsql-
>> performance(at)postgresql(dot)org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-performance