Обсуждение: [GENERAL] jsonb case insensitive search

On May 31, armand pirvu modulated:

> The idea is that I would like to avoid having an index for each key
> possibly wanted to search, but rather say have the whole record then
> search by the key and get the key value, thus having one index serving
> multiple purposes so to speak
>

First, benchmarking would be important to figure out if any proposed
indexing actually speeds up the kinds of queries you want to perform.
With the recently added parallel query features, a simpler indexing
scheme with some brute-force search might be adequate?

But, you could use a search idiom like this:

     (lower(json_column::text)::json) -> lower('key') = 'value'::json

This will down-convert the case on all values and keys.  The left-hand
parenthetic expression could be precomputed in an expression index to
avoid repeated case conversion. But, typical searches will still have
to scan the whole index to perform the projection and match the final
value tests on the right-hand side.

If you want to do things like substring matching on field values, you
might stick with text and using regexp matches:

     (lower(json_column::text)) ~ 'valuepattern'

or more structural searches:

     (lower(json_column::text)) ~ '"key": "[^"]*substring[^"]*"'

Here, the left-hand expression could be trigram indexed to help with
sparse, substring matching without a full index scan.  We've had good
luck using trigram indexing with regexp matching, though I've honestly
never used it for the purpose sketched above...

Karl

Thank you Karl and David

Ideally as far as I can tell the index would need to be show_id, file_id, lower(…)


The question is if this is  possible ?


Thanks
Armand


> On Jun 1, 2017, at 12:24 PM, Karl Czajkowski <karlcz@isi.edu> wrote:
>
> On May 31, armand pirvu modulated:
>
>> The idea is that I would like to avoid having an index for each key
>> possibly wanted to search, but rather say have the whole record then
>> search by the key and get the key value, thus having one index serving
>> multiple purposes so to speak
>>
>
> First, benchmarking would be important to figure out if any proposed
> indexing actually speeds up the kinds of queries you want to perform.
> With the recently added parallel query features, a simpler indexing
> scheme with some brute-force search might be adequate?
>
> But, you could use a search idiom like this:
>
>     (lower(json_column::text)::json) -> lower('key') = 'value'::json
>
> This will down-convert the case on all values and keys.  The left-hand
> parenthetic expression could be precomputed in an expression index to
> avoid repeated case conversion. But, typical searches will still have
> to scan the whole index to perform the projection and match the final
> value tests on the right-hand side.
>
> If you want to do things like substring matching on field values, you
> might stick with text and using regexp matches:
>
>     (lower(json_column::text)) ~ 'valuepattern'
>
> or more structural searches:
>
>     (lower(json_column::text)) ~ '"key": "[^"]*substring[^"]*"'
>
> Here, the left-hand expression could be trigram indexed to help with
> sparse, substring matching without a full index scan.  We've had good
> luck using trigram indexing with regexp matching, though I've honestly
> never used it for the purpose sketched above...
>
> Karl

I apologize before hand replying again on my own reply . I know it is frowned upon . My inline comments.

> On Jun 1, 2017, at 2:05 PM, armand pirvu <armand.pirvu@gmail.com> wrote:
>
> Thank you Karl and David
>
> Ideally as far as I can tell the index would need to be show_id, file_id, lower(…)
>
>
> The question is if this is  possible ?
>
>
> Thanks
> Armand
>
>
>> On Jun 1, 2017, at 12:24 PM, Karl Czajkowski <karlcz@isi.edu> wrote:
>>
>> On May 31, armand pirvu modulated:
>>
>>> The idea is that I would like to avoid having an index for each key
>>> possibly wanted to search, but rather say have the whole record then
>>> search by the key and get the key value, thus having one index serving
>>> multiple purposes so to speak
>>>
>>
>> First, benchmarking would be important to figure out if any proposed
>> indexing actually speeds up the kinds of queries you want to perform.
>> With the recently added parallel query features, a simpler indexing
>> scheme with some brute-force search might be adequate?
>>

Not sure what you mean by benchmarking
But I think comparative times , aka 2 seconds vs a couple milliseconds is quite a difference.
A table scan while in certain cases is okay , in a case when there is heavy usage on the same part/area , it will
becomea problem.  


>> But, you could use a search idiom like this:
>>
>>    (lower(json_column::text)::json) -> lower('key') = 'value'::json
>>
>> This will down-convert the case on all values and keys.  The left-hand
>> parenthetic expression could be precomputed in an expression index to
>> avoid repeated case conversion. But, typical searches will still have
>> to scan the whole index to perform the projection and match the final
>> value tests on the right-hand side.
>>
>> If you want to do things like substring matching on field values, you
>> might stick with text and using regexp matches:
>>
>>    (lower(json_column::text)) ~ ‘valuepattern'

In this case a regular index will be ignored even though IMO it should scan the index and get the needed information
The criteria I am after gets back 9 rows max out of 100k+ records so I say the restriction is darn good. Wouldn’t that
bethe case for the optimizer to pick the path with the least resistance aka best restriction ? Granted it uses a lower
functionwhich and the search in the text column which is the third in the index is not really starting form left. But
theindex starts with show_id , file_id and those are always part of the key. I can see though once the show_id, file_id
isNOT a good restriction anymore , than the last column will make the difference . Either case will that not translate
intoan index scan ? Or the index to be considered in this case, event the last column search has to follow the left to
right,aka not in between search ?   


>>
>> or more structural searches:
>>
>>    (lower(json_column::text)) ~ '"key": "[^"]*substring[^"]*"'
>>
>> Here, the left-hand expression could be trigram indexed to help with
>> sparse, substring matching without a full index scan.  We've had good
>> luck using trigram indexing with regexp matching, though I've honestly
>> never used it for the purpose sketched above...
>>
>> Karl
>


Seems to me trigram could be the answer since I have some decent results once I applied it, more to dig

Overall could it be that the optimizer blatantly ignores a scan index which is cheaper than a table scan, or jsonb
implementationstill has a long way to come up  or the way it is used in my case is not the one designed for ? 


thanks
Armand

On Jun 01, armand pirvu modulated:

> Overall could it be that the optimizer blatantly ignores a scan index which is cheaper than a table scan, or jsonb
implementationstill has a long way to come up  or the way it is used in my case is not the one designed for ? 
>

If I remember correctly, isn't a compound index always just using
btree?  In general, I have found better luck using several smaller
btree indices than one large compound one.  Unless your entire query
can be answered from an index-only lookup, the extra columns just
bloat the btree index.

So, you might as well use a simpler compound index for the regular
scalar row keys, and this index will be much smaller without the
baggage of the jsonb values at its leaves.  The planner can use the
jsonb from the actual candidate rows if it is going to have to visit
them anyway for other WHERE or SELECT clauses.

If the sparseness of your query is due to the content within the jsonb
values rather than the other scalar row keys, I think you'd need some
kind of GIN index over the contents of the jsonb documents to find the
small subset of candidate rows by these sparse criteria.  Trigram is
just one example of a GIN indexing scheme.

If your jsonb documents are "flat", i.e. just a bag of key value pairs
and not arbitrary nested jsonb structures, you might also explode them
into arrays of keys or values as separate indexed expressions?  Then,
you could GIN index the arrays and quickly find the subset of rows with
certain unusual keys or unusual values, but would still have to follow
up with a more exact check for the combination of key and value.


Karl