To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: [noob] How to optimize this double pivot query?
Date: Tue, 02 Oct 2012 08:46:11 +0200
Lines: 24
Message-ID: <k4e2j6$hc2$1@ger.gmane.org>
References: 
 <CADf7wwVizZbiQbOp+51HKoDfzYtZRf4trCwJxNniCgqiJDAZ0Q@mail.gmail.com>
	<001201cda03a$49a686d0$dcf39470$@yahoo.com>
	<CADf7wwXqQYEgfCdgAuz974EaHhE0-XjeUE2RaTPGskxo8Yfi6Q@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.8.1.23) Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
In-Reply-To: 
 <CADf7wwXqQYEgfCdgAuz974EaHhE0-XjeUE2RaTPGskxo8Yfi6Q@mail.gmail.com>

Robert Buck, 02.10.2012 03:13:
> So as you can probably glean, the tables store performance metric
> data. The reason I chose to use k-v is simply to avoid having to
> create an additional column every time a new metric type come along.
> So those were the two options I thought of, straight k-v and column
> for every value type.
>
> Are there other better options worth considering that you could point
> me towards that supports storing metrics viz. with an unbounded
> number of metric types in my case?
>

Have a look at the hstore module. It's exactly meant for that scenario with the added
benefit that you can index on that column and looking up key names and their values
is blazingly fast then.

That combined with the tablefunc module (which let's you do pivot queries) might
make your queries substantially more readable (and maybe faster as well).

Regards
Thomas