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 can hide 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
