Re: Indexes on array columns

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?

On 3/16/06, Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> wrote:
>
> contrib/intarray might help you
>
> Oleg
> On Wed, 15 Mar 2006, karly(at)kipshouse(dot)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(at)clickspace(dot)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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>