Indexes on array columns

On Wed, Mar 15, 2006 at 09:36:04AM -0700, Brendan Duddridge wrote:

Hi,

Is it possible to put an index on an array column?

Apparently yes (I just did it as a test). However, consider the
following from the manual.

Tip: Arrays are not sets; searching for specific array elements
may be a sign of database misdesign. Consider using a separate
table with a row for each item that would be an array element. This
will be easier to search, and is likely to scale up better to large
numbers of elements.[1]

Arrays are attractive, but it seems they most often aren't the best
solution. FOr instance, I beleive I read somewhere that the index
will be on the whole array, and the individual elements are not
indexed, so

WHERE ary = {foo,bar}

might benefit from your index, but

WHERE 'foo' = ANY(ary)

probably wouldn't. At least that's the impression I got from
reading the archives of this list. I haven't done any testing of
it.

-karl

1. http://www.postgresql.org/docs/8.0/interactive/arrays.html#AEN5491

contrib/intarray might help you

Oleg
On Wed, 15 Mar 2006, karly@kipshouse.org wrote:

On Wed, Mar 15, 2006 at 09:36:04AM -0700, Brendan Duddridge wrote:

Hi,

Is it possible to put an index on an array column?

Apparently yes (I just did it as a test). However, consider the
following from the manual.

Tip: Arrays are not sets; searching for specific array elements
may be a sign of database misdesign. Consider using a separate
table with a row for each item that would be an array element. This
will be easier to search, and is likely to scale up better to large
numbers of elements.[1]

Arrays are attractive, but it seems they most often aren't the best
solution. FOr instance, I beleive I read somewhere that the index
will be on the whole array, and the individual elements are not
indexed, so

WHERE ary = {foo,bar}

might benefit from your index, but

WHERE 'foo' = ANY(ary)

probably wouldn't. At least that's the impression I got from
reading the archives of this list. I haven't done any testing of
it.

-karl

1. http://www.postgresql.org/docs/8.0/interactive/arrays.html#AEN5491

Thanks,

____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com

ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9

http://www.clickspace.com

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

I have tried testing the perofmance on indexing array element using standard
btree and it doesn't help anything. It still costing alot.

create index idx_properties_address_4 on properties ((address_arr[4]))

does contrib/intarray effective for text array?