Обсуждение: Indexes on array columns
Hi,
Is it possible to put an index on an array column?
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
____________________________________________________________________
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
Вложения
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 >
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?
On 3/16/06, Oleg Bartunov <oleg@sai.msu.su> wrote:
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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster