Skip site navigation (1) Skip section navigation (2)

Re: FW: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)

From: "Pierre C" <lists(at)peufeu(dot)com>
To: "Stefan Keller" <sfkeller(at)gmail(dot)com>
Cc: mmoncure(at)gmail(dot)com, pgsql-performance(at)postgresql(dot)org, "Robert Haas" <robertmhaas(at)gmail(dot)com>
Subject: Re: FW: KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)
Date: 2011-05-26 09:24:24
Message-ID: op.vv20eyc1eorkce@apollo13 (view raw or flat)
Thread:
Lists: pgsql-performance
> 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.

In response to

pgsql-performance by date

Next:From: Merlin MoncureDate: 2011-05-26 12:11:28
Subject: Re: Speeding up loops in pl/pgsql function
Previous:From: Jochen ErwiedDate: 2011-05-26 08:46:55
Subject: Re: Speeding up loops in pl/pgsql function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group