Re: Array comparison & prefix search

On Mon, Dec 7, 2009 at 7:01 AM, Sam Mason <sam@samason.me.uk> wrote:
> 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.

hm, that's certainly an interesting idea, but I think unless you pad
all the strings out you are going to run into some odd ordering
issues.  If it works though I think you'll have a much tighter index
than the raw one over the array.

merlin