From: Ivan Voras <ivoras@freebsd.org>
To: pgsql-performance@postgresql.org
Subject: Unique values across a table of arrays - documents and tags
Date: Wed, 07 Nov 2012 16:21:47 +0100
Message-ID: <k7duab$fn4$1@ger.gmane.org> (raw)
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 searching:
edem=> explain analyze select id,title,flags from documents where tags
@> ARRAY['tag'];
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on documents (cost=8.00..12.01 rows=1 width=39)
(actual time=0.067..0.086 rows=9 loops=1)
Recheck Cond: (tags @> '{tag}'::text[])
-> Bitmap Index Scan on documents_tags (cost=0.00..8.00 rows=1
width=0) (actual time=0.053..0.053 rows=9 loops=1)
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=> explain analyze select distinct unnest(tags) as tag from documents;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
HashAggregate (cost=28.54..28.84 rows=24 width=42) (actual
time=0.261..0.307 rows=44 loops=1)
-> Seq Scan on documents (cost=0.00..28.45 rows=36 width=42)
(actual time=0.020..0.157 rows=68 loops=1)
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)?
Attachments:
signature.asc (application/pgp-signature, 259B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ivoras@freebsd.org
Subject: Re: Unique values across a table of arrays - documents and tags
In-Reply-To: <k7duab$fn4$1@ger.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox