On Sat, Dec 05, 2009 at 02:23:13AM +0100, Denes Daniel wrote: > 2009/12/4 Sam Mason <sam@xxxxxxxxxxxxx> > > 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
