Waiting for PostgreSQL 14 – Allow subscripting of hstore values.

On 11st of December 2020, Tom Lane committed patch:

Allow subscripting of hstore values.
 
This is basically a finger exercise to prove that it's possible for
an extension module to add subscripting ability.  Subscripted fetch
from an hstore is not different from the existing "hstore -> text"
operator.  Subscripted update does seem to be a little easier to
use than the traditional update method using hstore concatenation,
but it's not a fundamentally new ability.
 
However, there may be some value in the code as sample code, since
it shows what's basically the minimum-complexity way to implement
subscripting when one needn't consider nested container objects.
 
Discussion: https://postgr.es/m/3724341.1607551174@sss.pgh.pa.us

This is really cool. While this patch adds actual functionality, the backbone of this commit was actually this commit from two days earlier.

The general idea is that now, we will be able to provide subscripting ( value[subscript] ) for any datatype, not only arrays.

Hstore patch added support of subscripting to hstore. Which means that I can now:

$ SELECT payload['a'], payload FROM z WHERE id = 1 \gx
─[ RECORD 1 ]─────────────────────────────────────────────────────────────────────────────────────────────────────────
payload │ 47
payload │ "a"=>"47", "b"=>"35", "d"=>"97", "f"=>"42", "i"=>"75", "m"=>"35", "p"=>"41", "q"=>"86", "r"=>"56", "t"=>"60"

Of course, I could previously use custom operator:

$ SELECT payload->'a', payload FROM z WHERE id = 1 \gx
─[ RECORD 1 ]──────────────────────────────────────────────────────────────────────────────────────────────────────────
?COLUMN? │ 47
payload  │ "a"=>"47", "b"=>"35", "d"=>"97", "f"=>"42", "i"=>"75", "m"=>"35", "p"=>"41", "q"=>"86", "r"=>"56", "t"=>"60"

which, of course, it still available, but now we can also use [ subscript ].

One thing I need to caution you about is that while you can use it for selecting, using it for “where" clause is not indexable (yet?):

$ EXPLAIN analyze SELECT * FROM z WHERE payload['a'] = '47';
                                                     QUERY PLAN
────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Gather  (cost=1000.00..5778.67 ROWS=1000 width=117) (actual TIME=1.538..15.274 ROWS=734 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan ON z  (cost=0.00..4678.67 ROWS=417 width=117) (actual TIME=0.025..10.223 ROWS=245 loops=3)
         FILTER: (payload['a'::text] = '47'::text)
         ROWS Removed BY FILTER: 66422
 Planning TIME: 0.118 ms
 Execution TIME: 15.326 ms
(8 ROWS)

While you can use index when searching using @> operator:

$ EXPLAIN analyze SELECT * FROM z WHERE payload @> 'a=>47'::hstore;
                                                    QUERY PLAN
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────
 Bitmap Heap Scan ON z  (cost=56.16..131.96 ROWS=20 width=117) (actual TIME=5.975..9.452 ROWS=734 loops=1)
   Recheck Cond: (payload @> '"a"=>"47"'::hstore)
   ROWS Removed BY INDEX Recheck: 6586
   Heap Blocks: exact=3139
   ->  Bitmap INDEX Scan ON hidx  (cost=0.00..56.15 ROWS=20 width=0) (actual TIME=5.719..5.719 ROWS=7320 loops=1)
         INDEX Cond: (payload @> '"a"=>"47"'::hstore)
 Planning TIME: 0.200 ms
 Execution TIME: 9.517 ms
(8 ROWS)

All in all, I find it great feature, and am expecting more datatypes to support this feature, starting, probably, with json.

Thanks to all involved.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.