From: | "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
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:19:45 |
Message-ID: | C4DAC901169B624F933534A26ED7DF310861B5E9@JENMAIL01.ad.intershop.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> -----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 ?
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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2012-06-26 08:22:41 | Re: Performance of a large array access by position (tested version 9.1.3) |
Previous Message | Pavel Stehule | 2012-06-26 08:04:22 | Re: Performance of a large array access by position (tested version 9.1.3) |