jsonb search
| От | Armand Pirvu (home) |
|---|---|
| Тема | jsonb search |
| Дата | |
| Msg-id | 813007CE-79C8-4FE5-A72A-326897FA41F4@gmail.com обсуждение исходный текст |
| Ответы |
Re: jsonb search
|
| Список | pgsql-general |
Hi
In my quest of JSONB querying and searching without having to actually cast into a text, I found JSQuery
I do admit my JSONB knowledge shortcoming and I am not a developer but a DBA. As such some examples would be greatly appreciated since I tend to understand better
I compiled and installed the extension
1 - Exact matching without knowing the hierarchy, just the key and element, I built a set like
col1 | col2
------+--------------------------------------------------
1 | {"Home Email": {"EmailAddress": "1@yahoo.com"}}
2 | {"Home Email": {"EmailAddress": "2@yahoo.com"}}
3 | {"Home Email": {"EmailAddress": "3@yahoo.com"}}
JSQuqery is super
SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "1@yahoo.com"';
Now I can do a performance boost using
CREATE INDEX idx1 ON test1 USING GIN (col2 jsonb_value_path_ops);
I see this yield
from
testdb=# explain analyze^JSELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "10@yahoo.com"';
Seq Scan on test1 (cost=0.00..12423.00 rows=500 width=68) (actual time=0.016..160.777 rows=1 loops=1)
Filter: (col2 @@ '*."EmailAddress" = "10@yahoo.com"'::jsquery)
Rows Removed by Filter: 499999
Planning time: 0.042 ms
Execution time: 160.799 ms
(5 rows)
to
testdb-# SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "10@yahoo.com"';
Bitmap Heap Scan on test1 (cost=31.88..1559.32 rows=500 width=68) (actual time=0.018..0.019 rows=1 loops=1)
Recheck Cond: (col2 @@ '*."EmailAddress" = "10@yahoo.com"'::jsquery)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx1 (cost=0.00..31.75 rows=500 width=0) (actual time=0.011..0.011 rows=1 loops=1)
Index Cond: (col2 @@ '*."EmailAddress" = "10@yahoo.com"'::jsquery)
Planning time: 0.039 ms
Execution time: 0.038 ms
(7 rows)
A whooping 4000 times improvement
But I also noticed a vodka index
testdb=# CREATE INDEX idx2 ON
testdb-# test1 USING vodka (col2);
ERROR: access method "vodka" does not exist
What am I missing ?
2 - Is there anyway I can accomplish a pattern and/or case insensitive search using JSQuery similar to
select * from test2 where upper((col2 -> 'Home Email') ->> 'EmailAddress') ilike '%3%YAH%';
select * from test2 where (col2 -> 'Home Email') ->> 'EmailAddress' like '%3%yah%';
If so what indexing strategy can be used to have similar gains as above ?
I compiled and installed the extension
1 - Exact matching without knowing the hierarchy, just the key and element, I built a set like
col1 | col2
------+--------------------------------------------------
1 | {"Home Email": {"EmailAddress": "1@yahoo.com"}}
2 | {"Home Email": {"EmailAddress": "2@yahoo.com"}}
3 | {"Home Email": {"EmailAddress": "3@yahoo.com"}}
JSQuqery is super
SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "1@yahoo.com"';
Now I can do a performance boost using
CREATE INDEX idx1 ON test1 USING GIN (col2 jsonb_value_path_ops);
I see this yield
from
testdb=# explain analyze^JSELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "10@yahoo.com"';
Seq Scan on test1 (cost=0.00..12423.00 rows=500 width=68) (actual time=0.016..160.777 rows=1 loops=1)
Filter: (col2 @@ '*."EmailAddress" = "10@yahoo.com"'::jsquery)
Rows Removed by Filter: 499999
Planning time: 0.042 ms
Execution time: 160.799 ms
(5 rows)
to
testdb-# SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "10@yahoo.com"';
Bitmap Heap Scan on test1 (cost=31.88..1559.32 rows=500 width=68) (actual time=0.018..0.019 rows=1 loops=1)
Recheck Cond: (col2 @@ '*."EmailAddress" = "10@yahoo.com"'::jsquery)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx1 (cost=0.00..31.75 rows=500 width=0) (actual time=0.011..0.011 rows=1 loops=1)
Index Cond: (col2 @@ '*."EmailAddress" = "10@yahoo.com"'::jsquery)
Planning time: 0.039 ms
Execution time: 0.038 ms
(7 rows)
A whooping 4000 times improvement
But I also noticed a vodka index
testdb=# CREATE INDEX idx2 ON
testdb-# test1 USING vodka (col2);
ERROR: access method "vodka" does not exist
What am I missing ?
2 - Is there anyway I can accomplish a pattern and/or case insensitive search using JSQuery similar to
select * from test2 where upper((col2 -> 'Home Email') ->> 'EmailAddress') ilike '%3%YAH%';
select * from test2 where (col2 -> 'Home Email') ->> 'EmailAddress' like '%3%yah%';
If so what indexing strategy can be used to have similar gains as above ?
Many thanks for any help
Armand
В списке pgsql-general по дате отправления: