On Mon, Aug 17, 2015 at 11:26 PM, Peter Geoghegan <pg@heroku.com> wrote:
On Mon, Aug 17, 2015 at 12:26 PM, Merlin Moncure <mmoncure@gmail.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, we are thinking about better indexing of subselects, let's first have the syntax sugar in core, which Dmitry implemented.