Re: jsonb array-style subscripting

On Mon, Aug 17, 2015 at 11:26 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:

> 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, we are thinking about better indexing of subselects, let's first
have the syntax sugar in core, which Dmitry implemented.

>
> --
> Peter Geoghegan
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>