From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Sam Mason <sam(at)samason(dot)me(dot)uk> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Array comparison & prefix search |
Date: | 2009-12-07 15:08:39 |
Message-ID: | b42b73150912070708w629d8aeeuea6c3f81ab3ae698@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Dec 7, 2009 at 7:01 AM, Sam Mason <sam(at)samason(dot)me(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-12-07 15:12:47 | Re: Error - could not get socket error status: Invalid argument |
Previous Message | David Fetter | 2009-12-07 15:06:08 | Re: New PostgreSQL Committers |