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

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

From: Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Performance of a large array access by position (tested version 9.1.3)
Date: 2012-06-22 07:02:32
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Hi all,

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.

Test case (performed on large not busy server with 1GB work_mem to ensure I
working with memory only):

t AS (SELECT ARRAY(SELECT * FROM generate_series(1,N)) AS _array)
SELECT count((SELECT _array[i] FROM t)) FROM generate_series(1,10000) as

Results for N between 1 and 10.000.000 (used locally connected psql with

N:          Time:
1           5.8ms
10          5.8ms
100         5.8ms
1000        6.7ms
--until there all reasonable
5k         21ms
10k        34ms
50k       177ms
100k      321ms
500k     4100ms
1M       8100ms
2M      22000ms
5M      61000ms
10M    220000ms = 22ms to sinlge array element access.

Is that behaviour is correct?

PS: what I actually lookin for - constant fast access by position
tuplestore for use with recursive queries and/or pl/pgsql, but without
using C programming.

Maxim Boguk
Senior Postgresql DBA.

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

Skype: maxim.boguk
Jabber: maxim(dot)boguk(at)gmail(dot)com

"People problems are solved with people.
If people cannot solve the problem, try technology.
People will then wish they'd listened at the first stage."


pgsql-performance by date

Next:From: Kevin GrittnerDate: 2012-06-22 15:59:44
Subject: Re: Why is a hash join being used?
Previous:From: Andy HalsallDate: 2012-06-21 20:07:01
Subject: Can I do better than this heapscan and sort?

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