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,
"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).
But I'm pretty sure now that I can rely on this.
Thanks,
Denes Daniel
