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: | CAK-MWwQxjfug8CqBjQXQJJQLQ2bb5kcagEm4GwFvVTgHkLuSBA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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):
WITH
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
g(i);
Results for N between 1 and 10.000.000 (used locally connected psql with
\timing):
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
МойКруг: http://mboguk.moikrug.ru/
"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."
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2012-06-22 15:59:44 | Re: Why is a hash join being used? |
Previous Message | Andy Halsall | 2012-06-21 20:07:01 | Can I do better than this heapscan and sort? |