One of the improvements coming in PostgreSQL 9.3 is the new
LATERAL SQL clause. LATERAL allows you to write more
succinct code than you would be able to otherwise and will be a welcome companion to extensions like hstore and PostGIS which both
have a plethora of set returning functions. In this article, I'll just demonstrate it's use with hstore and subsequent I'll talk
about it's potential use in PostGIS raster,geometry, and topology for exploding subelements.
One thing I was very interested in aside from the succinctness is whether it will be more performant than the older approach. A perfect test case for hstore would be the example we just demonstrated in Unpivoting data in PostgreSQL. For this example, we'll rewrite the hstore view using LATERAL instead of employing a subselect.
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 ;
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
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.
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.
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).
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