To: pgsql-performance@postgresql.org
From: Ivan Voras <ivoras@freebsd.org>
Subject: Unique values across a table of arrays - documents and tags
Date: Wed, 07 Nov 2012 16:21:47 +0100
Lines: 86
Message-ID: <k7duab$fn4$1@ger.gmane.org>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigC4E84CAE5E40596464F0D019"
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64;
	rv:14.0) Gecko/20120812 Thunderbird/14.0

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigC4E84CAE5E40596464F0D019
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hello,

I know I need to re-engineer this so it doesn't suck by design, so I'm
wondering if there is some nifty PostgreSQL feature or best practice
which may automagically do the best thing.

I store information about documents which are tagged by string tags. The
structure is very simple:

CREATE TABLE documents (
        id SERIAL NOT NULL,
        title TEXT NOT NULL,
        -- other fields --
        tags TEXT[] NOT NULL,
        flags INTEGER
);

Currently, I have a GIN index on the tags field, and it works for searchi=
ng:

edem=3D> explain analyze select id,title,flags from documents where tags
@> ARRAY['tag'];
                                                      QUERY PLAN

-------------------------------------------------------------------------=
----------------------------------------------
 Bitmap Heap Scan on documents  (cost=3D8.00..12.01 rows=3D1 width=3D39)
(actual time=3D0.067..0.086 rows=3D9 loops=3D1)
   Recheck Cond: (tags @> '{tag}'::text[])
   ->  Bitmap Index Scan on documents_tags  (cost=3D0.00..8.00 rows=3D1
width=3D0) (actual time=3D0.053..0.053 rows=3D9 loops=3D1)
         Index Cond: (tags @> '{tag}'::text[])
 Total runtime: 0.135 ms
(5 rows)


The other feature I need is a list of unique tags in all the documents,
e.g.:

edem=3D> explain analyze select distinct unnest(tags) as tag from documen=
ts;
                                                 QUERY PLAN

-------------------------------------------------------------------------=
------------------------------------
 HashAggregate  (cost=3D28.54..28.84 rows=3D24 width=3D42) (actual
time=3D0.261..0.307 rows=3D44 loops=3D1)
   ->  Seq Scan on documents  (cost=3D0.00..28.45 rows=3D36 width=3D42)
(actual time=3D0.020..0.157 rows=3D68 loops=3D1)
 Total runtime: 0.419 ms
(3 rows)

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)?


--------------enigC4E84CAE5E40596464F0D019
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.19 (FreeBSD)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAlCafI0ACgkQ/QjVBj3/HSy/HgCfaKFuI05DImD2e3FHPBsRHxMu
U8QAnjvJL6KRLzPSKwOaCVvwDa9Z+j7B
=zLUn
-----END PGP SIGNATURE-----

--------------enigC4E84CAE5E40596464F0D019--