References: 
 <CADf7wwVizZbiQbOp+51HKoDfzYtZRf4trCwJxNniCgqiJDAZ0Q@mail.gmail.com>
	<001201cda03a$49a686d0$dcf39470$@yahoo.com>
	<CADf7wwXqQYEgfCdgAuz974EaHhE0-XjeUE2RaTPGskxo8Yfi6Q@mail.gmail.com>
	<k4e2j6$hc2$1@ger.gmane.org>
	<CAEV0TzBd8vUSY1adZhkB6QwwKeG0OQaT-rDs-mp4aDbHEJO2BQ@mail.gmail.com>
In-Reply-To: 
 <CAEV0TzBd8vUSY1adZhkB6QwwKeG0OQaT-rDs-mp4aDbHEJO2BQ@mail.gmail.com>
Mime-Version: 1.0 (1.0)
Content-Type: multipart/alternative;
	boundary=Apple-Mail-06EE7F4C-3ED1-4601-A6F3-A1A128B8A320
Message-Id: <2E4F715D-9957-4576-94DD-63D42C39ADDC@gmail.com>
Content-Transfer-Encoding: 7bit
Cc: Thomas Kellerer <spam_eater@gmx.net>,
	"pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: Robert Buck <buck.robert.j@gmail.com>
Subject: Re: [noob] How to optimize this double pivot query?
Date: Tue, 2 Oct 2012 05:45:32 -0400
To: Samuel Gendler <sgendler@ideasculptor.com>


--Apple-Mail-06EE7F4C-3ED1-4601-A6F3-A1A128B8A320
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

Hi Samuel=20

Thank you. This may be a bit of a stretch for you, but would it be possible f=
or me to peek at a sanitized version of your cross tab query, for a good exa=
mple on how to do this for this noob?

This will be pretty common in my case. The biggest tables will get much larg=
er as they are raw metrics feeds, which at some point need to be fed through=
 reporting engines to analyze and spot regressions.

Lastly, am I simply using the wrong tech for data feeds and analytics? The f=
irst cut of this used flat files and R and though it scoured thousands of fi=
les was much faster than the SQL I wrote here. The big goal was to get this o=
ff disk and into a database, but as its highly variable, very sparse, metric=
 data, this is why I chose k-v. SQL databases are internally more politicall=
y acceptable, though I am personally agnostic on the matter. In the end it w=
ould be nice to directly report off a database, but so long as I can transfo=
rm to csv I can always perform reporting and analytics in R, and optionally m=
ap and reduce natively in Ruby. Sane? Ideas? This is early on, and willing t=
o adjust course and find a better way if suggestions indicate such. I've hea=
rd a couple options so far.

Best regards,

Bob

On Oct 2, 2012, at 5:21 AM, Samuel Gendler <sgendler@ideasculptor.com> wrote=
:

>=20
>=20
> On Mon, Oct 1, 2012 at 11:46 PM, Thomas Kellerer <spam_eater@gmx.net> wrot=
e:
>=20
> 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).
>=20
>=20
> I woud think that using the crosstab functions in tablefunc would solve th=
e problem without needing a complete change of structure. I've built crossta=
bs over a whole lot more than 54K rows in far, far less time (and resulting i=
n more than 35 columns, too) than the 11 seconds that was quoted here, witho=
ut feeling the need to deal with hstore or similar.  In fact, wouldn't hstor=
e actually make it more difficult to build a crosstab query than the schema t=
hat he has in place now?
>=20
> --sam
>=20

--Apple-Mail-06EE7F4C-3ED1-4601-A6F3-A1A128B8A320
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head></head><body bgcolor=3D"#FFFFFF"><div>Hi Samuel&nbsp;</div><div>=
<br></div><div>Thank you. This may be a bit of a stretch for you, but would i=
t be possible for me to peek at a sanitized version of your cross tab query,=
 for a good example on how to do this for this noob?</div><div><br></div><di=
v>This will be pretty common in my case. The biggest tables will get much la=
rger as they are raw metrics feeds, which at some point need to be fed throu=
gh reporting engines to analyze and spot regressions.</div><div><br></div><d=
iv>Lastly, am I simply using the wrong tech for data feeds and analytics? Th=
e first cut of this used flat files and R and though it scoured thousands of=
 files was much faster than the SQL I wrote here. The big goal was to get th=
is off disk and into a database, but as its highly variable, very sparse, me=
tric data, this is why I chose k-v. SQL databases are internally more politi=
cally acceptable, though I am personally agnostic on the matter. In the end i=
t would be nice to directly report off a database, but so long as I can tran=
sform to csv I can always perform reporting and analytics in R, and optional=
ly map and reduce natively in Ruby. Sane? Ideas? This is early on, and willi=
ng to adjust course and find a better way if suggestions indicate such. I've=
 heard a couple options so far.<br><br>Best regards,<div><br></div><div>Bob<=
/div></div><div><br>On Oct 2, 2012, at 5:21 AM, Samuel Gendler &lt;<a href=3D=
"mailto:sgendler@ideasculptor.com">sgendler@ideasculptor.com</a>&gt; wrote:<=
br><br></div><div></div><blockquote type=3D"cite"><div><br><br><div class=3D=
"gmail_quote">On Mon, Oct 1, 2012 at 11:46 PM, Thomas Kellerer <span dir=3D"=
ltr">&lt;<a href=3D"mailto:spam_eater@gmx.net" target=3D"_blank">spam_eater@=
gmx.net</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"=
margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
That combined with the tablefunc module (which let's you do pivot queries) m=
ight<br>
make your queries substantially more readable (and maybe faster as well).<br=
>
<br></blockquote><div><br></div><div>I woud think that using the crosstab fu=
nctions in tablefunc would solve the problem without needing a complete chan=
ge of structure. I've built crosstabs over a whole lot more than 54K rows in=
 far, far less time (and resulting in more than 35 columns, too) than the 11=
 seconds that was quoted here, without feeling the need to deal with hstore o=
r similar. &nbsp;In fact, wouldn't hstore actually make it more difficult to=
 build a crosstab query than the schema that he has in place now?</div>
<div><br></div><div>--sam</div><div><br></div></div>
</div></blockquote></body></html>=

--Apple-Mail-06EE7F4C-3ED1-4601-A6F3-A1A128B8A320--