Re: Array comparison & prefix search

I tried making the "ident" column a text instead of text[] in the beginning,
but searches were approximately of the same speed; so I voted for the array,
because this way there isn't even a possibility for the separator ("##") to
cause problems.

Anyway, the "ident BETWEEN ARRAY['foo', 'bar'] AND ARRAY['foo', 'bar',
NULL]" approach works really fast (uses the index), and selects all arrays
that are equal to or start with ['foo', 'bar'].

Thanks everybody,
Denes Daniel

2009/12/7 Sam Mason <sam(at)samason(dot)me(dot)uk>

> On Sat, Dec 05, 2009 at 09:54:58AM -0500, Merlin Moncure wrote:
> > GIN is a pretty heavy price to pay for something that should be btree
> > indexable. Also note he is using a multi column index with array as
> > second column...that would be pretty awkward with GIN.
>
> Yup, sounds as though it's not going to work here. I was mainly
> suggesting it as it's working now, as opposed to something that
> could/should be made to work.
>
> > Normalizing the data loses the nice property of being able to order
> > the entire structure using a single index. He's using the array as if
> > it was a string...it's basically an optimization.
>
> Hum, not sure why this didn't come up already: what about having an
> index on (type,(array_to_string(ident,'##')) and relying on the already
> existing optimizations for string prefixes. Not sure what sort of
> values can be used in "ident", but it could work.
>
> --
> Sam http://samason.me.uk/
>