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 <s...@samason.me.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/ >
