2012/6/26 Maxim Boguk <maxim.boguk@xxxxxxxxx>: > > > On Tue, Jun 26, 2012 at 6:04 PM, Pavel Stehule <pavel.stehule@xxxxxxxxx> > wrote: >> >> 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 >> > > Hi, > > I understand what you mean, but in my test for all values of N test > performed access only to first 10000 elements of the array independent of > the array size.... > So i still can't see why access time degrade soo fast for N>10000...: > > WITH > --GENERATE single entry table with single ARRAY field of size N > t AS (SELECT ARRAY(SELECT * FROM generate_series(1,N)) AS _array) > --iterate over first 10000 elements of that ARRAY > SELECT count((SELECT _array[i] FROM t)) FROM generate_series(1,10000) as > g(i); > > ... if access time depend only on position then after 10k there should not > be any serious degradation, in fact a perfromance degradation is almost > linear. > 10k 34ms > 50k 177ms > 100k 321ms > 500k 4100ms > 1M 8100ms > 2M 22000ms > 5M 61000ms > 10M 220000ms = 22ms to sinlge array element access. in this use case TOAST/DETOAST is not used, but probably there are problem with array copy. Taking n element of array means calling some function, and postgresql uses only passing parameters by value - so copy of large array needs higher time. this issue is solved partially in 9.2, where you can use FOR EACH cycle http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY Regards Pavel > > And I think no toasting/detoasting happen in my test case. > > Kind Regards, > Maksym -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
