Обсуждение: 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

>
> 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