Re: Array comparison & prefix search

On Sat, Dec 05, 2009 at 02:23:13AM +0100, Denes Daniel wrote:
> 2009/12/4 Sam Mason <sam(at)samason(dot)me(dot)uk>
> > CREATE INDEX test_my_idx ON test (type,(ident[1]));
>
> Sorry, but this approach is no good, since I may search like:
> SELECT * FROM test WHERE type = 'three' AND (ident[1] = 'foo' AND ident[2] =
> 'bar');
> or for the first 3 items in an array with 6 items, or any other prefix...

Would a GIN index help? You'd be able to ask if a 'foo' appears
anywhere in the array (or some subset if you want). You can then have a
subsequent filter that actually expresses the clause you want. Not sure
what selectivity you're dealing with and if this would be a problem.

> The arrays are all the same length for a given type, but for type
> 'twenty-three' they may be 23 items long, or even longer for another type,
> so I can't create an index for all possible cases that way. And yet, all the
> information needed is in the primary index, I just don't know how to get
> PostgeSQL to use it.

Arrays and PG (not sure how well other databases handle this case
either) don't work too well. Have you thought about normalising your
schema a bit to give the database more help?

> And why is it this way when I'm using an ARRAY[], and the other way when
> using ROW()?

I'd say ROW is doing the wrong thing here, but I think other people may
well disagree with me. Composite/non-atomic types don't exist in the
SQL spec much (AFAIK) hence their behavior is somewhat ad-hoc and tends
to reflect the original use case rather than being too consistent.

--
Sam http://samason.me.uk/