Re: jsonb array-style subscripting

On Mon, Aug 17, 2015 at 12:26 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> ...is a good idea. postgres operators tend to return immutable copies
> of the item they are referring to.

This patch does not add an operator at all, actually. If feels like
there ought to be an operator, but in fact there is not. The parser is
hard-coded to recognize array-style subscripts, which this uses.

While I'm certainly glad that Dmitry took the time to work on this, I
think we will need an operator, too. Or, more accurately, there should
probably be a way to make something like this use some available GIN
index:

postgres=# explain analyze select * from testjsonb where p['a'] = '[1]';
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on testjsonb (cost=0.00..27.00 rows=7 width=32) (actual
time=0.022..0.023 rows=1 loops=1)
Filter: (p['a'] = '[1]'::jsonb)
Planning time: 0.070 ms
Execution time: 0.054 ms
(4 rows)

This doesn't really matter with arrays, but ISTM that it matters here.
I have no strong feelings on how it should work, but certain things do
seem to suggest themselves. For example, maybe the parser can be made
to create a query tree that uses an indexable operator based on
special-case logic. Although maybe that's a kludge too far, since I
can imagine it breaking other legitimate things. My sense is that this
will need to be discussed.

--
Peter Geoghegan