Le 2012-11-07 à 10:21, Ivan Voras a écrit :
>
> This is unfortunately slow (because I know the load will increase and
> this will be a common operation).
>
> The thing I was planning to do is create a separate table, with only the
> unique tags, and possibly an array of documents which have these tags,
> which will be maintained with UPDATE and INSERT triggers on the
> documents table, but then I remembered that the GIN index itself does
> something not unlike this method. Is there a way to make use of this
> information to get a list of unique tags?
>
> Barring that, what would you suggest for efficiently handing a classic
> structure like this (meaning documents with tags)?
>
Can you structure it as the "classic" many to many pattern:
documents <-> taggings <-> tags
Unique tags then becomes a plain seq scan on a smallish table (tags). To keep the ability to have a single field, you
canhide the documents table behind a view that would do an array_agg, such as:
SELECT documents.*, array_agg(taggings.tag)
FROM documents JOIN tags ON tags.document_id = documents.id
GROUP BY documents.*
Not sure we can do GROUP BY documents.*, but if not, you list your columns individually.
Hope that helps!
François