PostgreSQL 9.3 Lateral Part 1: Use with HStore   

Our original hstore view

In Pre-9.3, we'd write the view as we saw in the prior article:

CREATE OR REPLACE VIEW vw_zcta_unpivot_hs
AS 
SELECT zip, (h).key, (h).value As val
 FROM (SELECT zip, each(hstore(foo) - 'zip'::text) As h
 FROM zcta5 as foo  ) As unpiv ;

What is all of that cryptic mumbo jumbo?

The reason for the subselect instead of doing (each(..)).* is to prevent the each function from being called multiple times. For those who were confused by the - 'zip'::text idiom, one of the neat features of PostgreSQL is that it allows types to define their own implementations of operators. The hstore function defines it's own minus - operator to mean remove a key or set of keys from the list. If you subtract a string it will remove all key-val where the key is the string you passed in. If you wanted to remove multiple elements, you'd do something like hstore - array['zip', 'hu10']::text[].

The each function is a function packaged with hstore that explodes an hstore object into a set of key/value pairs. The hstore function, also packaged with hstore, takes many forms and the one we are using here, takes a row object and converts it to an hstore.

Rewriting the view with LATERAL

In 9.3, we can rewrite our view using LATERAL, but will it perform better? Here is the view rewritten using LATERAL.

CREATE OR REPLACE VIEW vw_zcta_unpivot_hs_lateral
AS 
SELECT zip, (h).key, (h).value As val
 FROM zcta5 AS foo 
   CROSS JOIN LATERAL each(hstore(foo) - 'zip'::text)  As h;

People may laugh for our explicit use of CROSS JOIN when a , would suffice. Sadly the new LATERAL clause does not support LEFT JOIN which would be a very welcome addition, and this is just a reminder that it doesn't. For this case there is no need, but there are many where LATERAL is still not an option if you need the LEFT side returned even if no matching records in the LATERAL.

As we can see, this version of the view is slighly shorter than the original, and would have been shorter if we weren't so anal about writing out CROSS JOIN. But is it faster?

I was hoping the speed would be as good or even better than the old approach, but sadly it was slightly worse. Difference in speed on my test 9.3 postgres instance -- old approach (250 ms), lateral approach (305 ms) -- this is doing a full explain analyze verbose (so without network effects). Index is still used if you do specific zip filter, and is about same speed as old approach (10ms).

There is hope

Well PostgreSQL 9.3 is not out yet so there is still hope that performance will be improved and LEFT JOIN or equivalent will be supported to rival the SQL Server OUTER APPLY clause.