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