Re: [GENERAL] jsonb search

2016-06-28 Thread Arthur Silva 
On Tue, Jun 28, 2016 at 5:09 PM, Oleg Bartunov  wrote:

> On Tue, Jun 28, 2016 at 5:15 PM, Armand Pirvu (home)
>  wrote:
> > 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
> > = "1...@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" = "1...@yahoo.com"'::jsquery)
> >   Rows Removed by Filter: 49
> > Planning time: 0.042 ms
> > Execution time: 160.799 ms
> > (5 rows)
> >
> >
> > to
> >
> > testdb-# SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress =
> > "1...@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" = "1...@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" = "1...@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
>
> Vodka is our experimental prototype of access method of next
> generation and it doesn't exists in production-ready form. You can
> check our presentation
> http://www.sai.msu.su/~megera/postgres/talks/highload-2014-vodka.pdf
> to understand jsquery limitation and why we stop its development.
> Also, 2 years ago I wrote (in russian)
> http://obartunov.livejournal.com/179422.html about jsonb query
> language and our plans. Google translate might helps
>
>
> https://translate.google.com/translate?sl=auto=en=y=_t=en=UTF-8=http%3A%2F%2Fobartunov.livejournal.com%2F179422.html==url
>
>
> >
> >
> > Armand
> >
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


Could you share your future plans for it (or it's reincarnation), if any?

Even in the limited form, vodka is very impressive.

--
Arthur Silva

Re: [GENERAL] jsonb search

2016-06-28 Thread Oleg Bartunov 
On Tue, Jun 28, 2016 at 5:15 PM, Armand Pirvu (home)
 wrote:
> 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
> = "1...@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" = "1...@yahoo.com"'::jsquery)
>   Rows Removed by Filter: 49
> Planning time: 0.042 ms
> Execution time: 160.799 ms
> (5 rows)
>
>
> to
>
> testdb-# SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress =
> "1...@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" = "1...@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" = "1...@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

Vodka is our experimental prototype of access method of next
generation and it doesn't exists in production-ready form. You can
check our presentation
http://www.sai.msu.su/~megera/postgres/talks/highload-2014-vodka.pdf
to understand jsquery limitation and why we stop its development.
Also, 2 years ago I wrote (in russian)
http://obartunov.livejournal.com/179422.html about jsonb query
language and our plans. Google translate might helps

https://translate.google.com/translate?sl=auto=en=y=_t=en=UTF-8=http%3A%2F%2Fobartunov.livejournal.com%2F179422.html==url


>
>
> Armand
>


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] jsonb search

2016-06-28 Thread Armand Pirvu (home) 
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 = 
"1...@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" = "1...@yahoo.com"'::jsquery)
  Rows Removed by Filter: 49
Planning time: 0.042 ms
Execution time: 160.799 ms
(5 rows)


to

testdb-# SELECT * FROM test1 WHERE col2 @@ '*.EmailAddress = "1...@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" = "1...@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" = "1...@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