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
