Re: [noob] How to optimize this double pivot query? - Mailing list pgsql-sql

Thank you, Samuel.

I am trying some of this out right now...

This is great information.

Thanks so much. This is a huge help.

Bob

On Tue, Oct 2, 2012 at 4:09 PM, Samuel Gendler <sgendler@ideasculptor.com> wrote:

One last comment - an alternative solution to this is to use array_agg(), which will simply add each value that matches a group by clause to an array.  If you are careful to structure your query with left joins so that every grouping will have the same number of rows, then you can get a quick and dirty crosstab like this

select row_name, array_agg(value) from test t left join value_table v on t.id = v.id group by 1;

Obviously, you'll want to deal with your potential for duplicate rows via max() or avg() in a subquery, rather than joining directly to the table, but you should get the idea from that example.  You can also use coalesce to convert nulls to some other value, if required.

Since the crosstab functions already require you to do all that work with regard to determining column names and building up the record structure, using array_agg can be every bit as effective, since it basically requires the same process.  First query for all possible names, then issue a query that will cause the values to be processed by array_agg in column order, then iterate over results, getting each array value and associating it with a particular name.

Your result will look  like this:

id_name, start_time, end_time, array_of_values

That may or may not be convenient for you, depending upon how you are using the resultset you get back. You'll still need to play all the same games with regard to unioning multiple queries together to pivot data from multiple tables into the same row.

On Tue, Oct 2, 2012 at 12:57 PM, Samuel Gendler <sgendler@ideasculptor.com> wrote:

On Tue, Oct 2, 2012 at 2:45 AM, Robert Buck <buck.robert.j@gmail.com> wrote:

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.

OK, you owe me.  I think I just spent more than an hour writing this up ;-)  

Given the numbers of rows you are talking about, I can't think of any good reason why the database shouldn't do what you need it to do pretty effectively/quickly.

Some questions before I provide crosstab samples - your example query has a hardcoded set of keys that it is looking for, but maybe that was code-generated.  There are multiple forms of the crosstab function, some of which rely on a constant column count and others which can generate the set of columns based on another query.  There are complications in that second form relating to race conditions with regard to new keys showing up between the query for the columns and the query for the data, so it is important to understand that and either structure queries accordingly or make sure you execute in a transaction with sufficient transaction isolation to prevent the race condition from appearing.

For crosstab queries, you generally want a query that returns results in the following form:

row_name | key | value

where row_name would be a date or some other label that all values that should share a row will have in common.  Key is the field that defines a column in the final row.  And value is the value to go in that column.  If it is the case that you always want ALL keys, then you can simply do a left join to ensure that you get a row for every key, regardless of whether there is an actual value.  You can use COALESCE to turn nulls resulting from left joins that don't match into '0' values.  If there is the potential for multiple rows with the same key and row_name, you need to use an aggregate function (like max or avg) to compress all rows into a single row.

The crosstab(text sql) form of the function requires that it gets a value for every possible column in every single row.  It makes some naive assumptions about the presence of a value for every spot in the matrix.  It also cannot include extra columns as plain payload in the final cross tab result.  For greater flexibility, you really want to use the crosstab(text sql, text sql) form of the function.  This allows you to specify the set of columns that should appear via the 2nd query.  When processing the results, it makes fewer naive assumptions about the presence of data for every column, so it correctly handles missing data (and, I assume, data for columns that didn't appear in the 2nd query).  This eliminates the need for left joins to get empty rows, though you do still need to use aggregates to combine multiple rows into one row.  

Your 2nd query would be a query that simply returns the distinct set of key names from both of your key/value tables.  You want them in a consistent and repeatable order, so order by id_name:

select id_name from (

    select distinct m.id_name from metadata_key m 

    union 

    select distinct d.id_name from metric_def d

) q order by id_name

The nested union query is required based on what I can gleam of your schema from your examples, since you are getting keys from multiple tables and we need them all in a single column in a consistent order.

Now you just need a query that returns 

row_name | extra_col1 | extra_col2 | key | max(value)

extra_col1 and extra_col2 are going to be the begin_time and end_time from your test table, but you are going to have to do the union thing again in order to extract results from multiple tables into a single column, and you'll have to do the subquery thing in order to get the rows in consistent order again:

select row_name, begin_time, end_time, category, value from

(

select t.id_name as row_name, max(t.begin_time) as begin_time, max(t.end_time) as end_time, 

          m.id_name as category, max(v.value_end) as value

from test t, metadata_key m, test_variables v 

where v.test_id = t.id and m.id = v.metadata_key_id group by 1, 4

union all -- use 'union all' to prevent the database from trying to de-dupe rows

select t.id_name as row_name, max(t.begin_time) as begin_time, max(t.end_time) as end_time, 

         d.id_name as category, max(r.value_end) as value

from test t, test_results r, metric_def d

where v.test_id = r.test_id and d.id = r.metric_def_id

) q 

order by row_name, category 

order by is important, as the crosstab processor is naive - it processes a row until a new row_name is found, then moves on.  If it encounters the same row_name later on, it will create a new row with the same name rather than going back and adding columns to the existing row. I don't think category order is important in this case, since it is doing category lookup by name rather than position, but all of my example code does order categories, too, probably just to be safe.

Now there is one final bit of wierdness related to the crosstab functions.  You must specify the structure of the returned results when you call it, because it returns a setof record.

So you either call it like this:

select * from crosstab(sql1, sql2) as myresult(text rowname, timestamp begin_time, timestamp end_time, int col1, int col2, int col3, int col4, int col5,...)

or if you know that you will have exactly 35 columns every single time, you can declare an alias to the function which specifies the structure of the returned records, which will allow you to call it simply as:

select * from crosstab35(sql, sql);

The documentation for the tablefunc module (http://www.postgresql.org/docs/9.1/static/tablefunc.html ) explains how to set up the aliases you might want if you have a consistent column set.  If you don't have a consistent column set, you have to first query for the names of all possible columns.  Then construct your two queries for the crosstab function, along with the 'as' clause, using the names that were returned from your initial call to limit the results in the eventual crosstab execution.  In other words, you need to make sure that the 2nd sql statement in the crosstab call cannot possibly return more columns than the set of columns you define in the 'as' clause, so you must either execute both queries in a transaction with at least REPEATABLE_READ isolation semantics, or else include a where clause in your column query that limits the columns returned to the same set of columns you got in the first query - select column_name from table where column_name in (...).

Basically, the flow looks like this:

select all possible column names from database.

build data query for crosstab func.

build column query for crosstab func (should be the same or similar to the earlier query for column names).

build 'as' clause for crosstab func - I just name my columns a1, a2, a3, a4, etc, knowing that they will be in the same order as my column names, since my column names are often not strings that would be legal column names in a query.

execute query "select * from crosstab(" + data_query + "," + column_query + ")" + as_clause

process crosstab result

I'll admit, it's a little difficult to set up correctly the first time you do it.  But execution is actually pretty efficient, though it is only doing pretty much exactly what you'd do if you were to pivot the data manually while iterating over the resultset for the data query.  If you're working in a a dynamically typed language that lets you easily write code that has efficient hash maps and the like, and you only have this one use case, it might actually be easier just to query for the raw data and pivot it yourself, where you'll have much more flexibility in dealing with column names and such. But if you have a wide variety of uses, it's probably worthwhile to become familiar with the crosstab functionality, since it is pretty simple to use once you get the hang of it. I've got fairly boilerplate code that deals with things like generating the record structure so I can usually add a new bit of crosstab functionality to an app pretty darn quickly these days.

One thing that's a drag is that the queries are just strings as far as the database is concerned, so depending upon your client library, you may not be able to use variable escaping best practices. Basically, jdbc won't allow you to do use prepared statement variables within a string, so you have to manually escape any user-provided input while constructing the query as a raw string, rather than using '?' in your query.  The same goes for trying to slip a crosstab query though hibernate's SQLQuery interface (I thought that might be a workaround, but it wasn't), so you are forced to construct the entire query string manually.

--sam