pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Pierre C <lists@peufeu.com>
To: Stefan Keller <sfkeller@gmail.com>
Cc: mmoncure@gmail.com, pgsql-performance@postgresql.org, "Robert Haas" <robertmhaas@gmail.com>
Subject: Re: FW: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
Date: Thu, 26 May 2011 11:24:24 +0200
Message-ID: <op.vv20eyc1eorkce@apollo13> (raw)
In-Reply-To: <BANLkTiknLoZMjV8eY6cdwwdXKb0jxgKP4w@mail.gmail.com>
References: <BANLkTi=LjFTnn8hLwBZc1c87Vq+pw02+jQ@mail.gmail.com>
	<4DD06EF3.7030003@postnewspapers.com.au>
	<BANLkTimdGR4X0uMMyMwtvy9Uex-zeMtVCQ@mail.gmail.com>
	<BANLkTintNbJZw-hnVpy8q5jLW1__E9BY1w@mail.gmail.com>
	<99EECF01B5C87A498318776AB7E4A78D3640F59E26@sid00102.hsr.ch>
	<op.vvxkj7o8eorkce@apollo13>
	<BANLkTimiphaS_5yBLjr-jDPFjWV3Uc1etA@mail.gmail.com>
	<op.vv1qt1fdeorkce@apollo13>
	<BANLkTiknLoZMjV8eY6cdwwdXKb0jxgKP4w@mail.gmail.com>


> My problem is, that in fact I don't know which tag to index since I'm
> running a web admin application where users can enter arbitrary
> queries.

For a tag cloud, try this :

- table tags ( tag_id, tag_name )
- table articles ( article_id )
- table articles_to_tags( article_id, tag_id )

now this is the classical approach, which doesn't work so well when you  
want to get an article that has several tags (tag intersection).

So, materialize the list of tag_ids for each article in an INTEGER[] array  
in the articles table, kept up to date with triggers.

Create a gist index on that, and use indexed array vs array operators.



view thread (13+ messages)

Message-ID: <op.vv20eyc1eorkce@apollo13>
Permalink:  ../op.vv20eyc1eorkce@apollo13/
Also on:    postgresql.org/message-id/op.vv20eyc1eorkce@apollo13

 · 

reply
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: lists@peufeu.com, sfkeller@gmail.com, robertmhaas@gmail.com
  Subject: Re: FW: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
  In-Reply-To: <op.vv20eyc1eorkce@apollo13>

* 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