Skip site navigation (1) Skip section navigation (2)

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

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Marc Mamin <M(dot)Mamin(at)intershop(dot)de>
Cc: Jesper Krogh <jesper(at)krogh(dot)cc>, Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance of a large array access by position (tested version 9.1.3)
Date: 2012-06-26 08:04:22
Message-ID: CAFj8pRD4jxJxr5i6WOCtRLGAwecXg-i6=taMpseuDxZh=NVx7g@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

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

In response to

Responses

pgsql-performance by date

Next:From: Marc MaminDate: 2012-06-26 08:19:45
Subject: Re: Performance of a large array access by position (tested version 9.1.3)
Previous:From: Marc MaminDate: 2012-06-26 07:53:07
Subject: Re: Performance of a large array access by position (tested version 9.1.3)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group