I have a table with ~100M rows, all of which contain HSTORE
fields. I am creating a view that will return (convert) the hstore
column to key | value
columns and also add a few fields for filtering.
id | hstore id | user_key | user_value
-------------------------- --------------------------------
11 | "a" => "2", "b" => "z" => 10 | a | 2
> 10 | b | z
This view will be used by an export tool which knows sql, but doesn't grok hstore or json.
There are two reasonably nice solutions I have found, both using each
, but I am curious if there are any drawbacks to one approach over the other, especially considering the desire to remove empty rows.
$1) using each
To filter rows without user data I would need to compare the underlying hstore to empty (user_data != '' :: hstore
).
CREATE VIEW v1 AS
SELECT
t.id,
t.filterable_column,
s.owner,
(each(t.user_data)).key,
(each(t.user_data)).value
FROM
public.hugetable AS t
LEFT JOIN public.smalltable s ON s.id = t.s_id
$2) using lateral
left join
In this case I can use an INNER join on the lateral to remove empty rows (rows with no user data). Seems cleaner.
CREATE VIEW v2 AS
SELECT
t.id,
t.filterable_column,
s.owner,
u.key,
u.value
FROM
public.hugetable AS t
LEFT JOIN LATERAL each(t.user_data) AS u ON TRUE
LEFT JOIN public.smalltable s ON s.id = t.s_id
I can do explains on them, but I am not sure how to interepret since the difference seems small. Perhaps they are the same, essentially, or one is fundamentally worse than the other (more work, loops, or inefficent). I've no familiarity with each
or lateral joins
, but am reading up as I can.
select ... (each(t.user_data)).* ...
shorter a bit.