Re: Array comparison & prefix search

2009/12/5 Sam Mason <sam(at)samason(dot)me(dot)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