Clarification regarding array columns usage?

From: "m(dot) hvostinski" <makhvost(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Clarification regarding array columns usage?
Date: 2009-12-25 19:46:33
Message-ID: 3845047c0912251146r5eda7a66u961a930dac8f5239@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I would appreciate if someone could clarify the aspects of using array
columns.

We need to store up to ten related integer values per row and currently it
is implemented as a varchar column that holds a string that is concatenated
by a trigger function. Something like this:

FOR var IN (SELECT id FROM support_table WHERE...) LOOP
str := concatenate string...
END LOOP;
UPDATE main_table SET id_string = str WHERE...

So we have a string like this "1201,1202,1203,201" in the main_table varchar
column that is parsed by the client app. Recently I realized that it would
be more natural to use the array column in the main table to store the
values - no looping, concatenation, parsing, should take less space. After
implementing it I run explain on selects from the main table to compare
string vs. array and results are somewhat confusing.

EXPLAIN SELECT id_string FROM main_table WHERE...
returns row width: 3 where actual value of the id_string =
"1201,1202,1203,201"

EXPLAIN SELECT id_array FROM main_table WHERE...
returns row width: 26 for the same values

It looks like array takes more space than a string containing the same
values. Another strange thing is that for the varchar column explain shows
width 3, it's to low. I thought that it might be related to TOAST but I
understand that TOAST kicks in only if the row size is more than 2kb and

EXPLAIN SELECT * FROM main_table WHERE...
returns row width: 251

Hence the questions:

-- Could someone help me to interpret the explain readings above?

-- Is storing integers in array is really more space efficient approach?

-- Is there a noticeable performance difference in reading array vs varchar
columns? Creating id string is relatively rare operation in our case and if
reading strings is faster may be it makes sense to have the overhead of
string concatenation in the trigger.

-- Is it possible to estimate how much slower the string concatenation
trigger function would be in comparison to one that insets into array column
on up to 10 values per string/array? The trigger still will be executed
fairly often.

Thanks.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2009-12-25 20:13:04 Re: Updating from 8.2 to 8.4
Previous Message Tom Lane 2009-12-25 19:20:11 Re: Finding the bin path