2009/12/5 Sam Mason <s...@samason.me.uk>
> 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. > I think that wouldn't be good for me, since the table will be 2-3M rows large and will be updated very often, and GIN indices are too slow at that. (In fact, the whole table's goal is to avoid updating GIN indices so frequently.) > 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? I don't have any idea how I could do that... except for creating separate tables for all "type"s. But I don't think that would be a better option. If you have any other idea, I'd really appreciate it. > 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. > According to the documentation, http://www.postgresql.org/docs/8.4/static/functions-comparisons.html#ROW-WISE-COMPARISON "Note: Prior to PostgreSQL 8.2, the <, <=, > and >= cases were not handled per SQL specification." I think the way ROW comparisons work now is per SQL specification. But wait! Thank you for making me read this part of the docs, because I've just found what I was looking for, at the very end of the page: > Note: The SQL specification requires row-wise comparison to return NULL if > the result depends on comparing two NULL values or a NULL and a non-NULL. > PostgreSQL does this only when comparing the results of two row constructors > or comparing a row constructor to the output of a subquery (as in Section > 9.20). In other contexts where two composite-type values are compared, two > NULL field values are considered equal, and a NULL is considered larger than > a non-NULL. This is necessary in order to have consistent sorting and > indexing behavior for composite types. I was sure I've read this part of the docs a hundred times, so I've gone after why I didn't find this before: this note is new in the 8.4 docs, it wasn't there before (and I'm using 8.3). http://www.postgresql.org/docs/8.3/static/functions-comparisons.html#ROW-WISE-COMPARISON But I'm pretty sure now that I can rely on this. Thanks, Denes Daniel
