From: Robert Buck <buck.robert.j@gmail.com>
To: Samuel Gendler <sgendler@ideasculptor.com>
Cc: Thomas Kellerer <spam_eater@gmx.net>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: [noob] How to optimize this double pivot query?
Date: Tue, 2 Oct 2012 05:45:32 -0400
Message-ID: <2E4F715D-9957-4576-94DD-63D42C39ADDC@gmail.com> (raw)
In-Reply-To: <CAEV0TzBd8vUSY1adZhkB6QwwKeG0OQaT-rDs-mp4aDbHEJO2BQ@mail.gmail.com>
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>
Hi Samuel
Thank you. This may be a bit of a stretch for you, but would it 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?
This will be pretty common in my case. The biggest tables will get much larger 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 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 this off 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 politically acceptable, though I am personally agnostic on the matter. In the end it would be nice to directly report off a database, but so long as I can transform to csv I can always perform reporting and analytics in R, and optionally map and reduce natively in Ruby. Sane? Ideas? This is early on, and willing to adjust course and find a better way if suggestions indicate such. I've heard a couple options so far.
Best regards,
Bob
On Oct 2, 2012, at 5:21 AM, Samuel Gendler <sgendler@ideasculptor.com> wrote:
>
>
> On Mon, Oct 1, 2012 at 11:46 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
>
> 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).
>
>
> I woud think that using the crosstab functions in tablefunc would solve the problem without needing a complete change 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 or similar. In fact, wouldn't hstore actually make it more difficult to build a crosstab query than the schema that he has in place now?
>
> --sam
>
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-sql@postgresql.org
Cc: buck.robert.j@gmail.com, sgendler@ideasculptor.com, spam_eater@gmx.net
Subject: Re: [noob] How to optimize this double pivot query?
In-Reply-To: <2E4F715D-9957-4576-94DD-63D42C39ADDC@gmail.com>
* 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