Maximum number of elements "IN".

[egamihideki]

For the future system development, we are trying to use Postgres9.4.1 and jsquery,

JSONB and jsquery is indispensable for the general purpose of the data model.
We are grateful to your provision of function!

I have a question.
When using the "IN" as follows,
error occurs when the number of elements exceeds 16.

SELECT info FROM sampleTable WHERE info @@ 'orderDate IN ("2015/06/01", "2015/06/02", ... , "2015/06/17")';

Is it possible to increase the maximum number of elements?
For example 100.

Hideki Egami

[akorotkov]

Which error occurs? Could you give a complete example, please?

[egamihideki]

Thank you for the reply.
I show the procedure below.

test_db=> CREATE TABLE orders(data JSONB);
test_db=> CREATE INDEX idx_1 ON orders USING gin (data jsonb_value_path_ops);
test_db=> CREATE INDEX idx_2 ON orders USING gin (data jsonb_path_value_ops);
test_db=> \d orders
Table "public.orders"
Column | Type | Modifiers
--------+-------+-----------
data | jsonb |
Indexes:
"idx_1" gin (data jsonb_value_path_ops)
"idx_2" gin (data jsonb_path_value_ops)

test_db=> INSERT INTO orders(data) values ('{"orderDate":"2015/06/01"}');

test_db=> SELECT count(data) FROM orders WHERE data @@ 'orderDate IN ("2015/06/01","2015/06/02","2015/06/03","2015/06/04","2015/06/05","2015/06/06","2015/06/07","2015/06/08","2015/06/09","2015/06/10","2015/06/11","2015/06/12","2015/06/13","2015/06/14","2015/06/15","2015/06/16")';
count
'--------
1

test_db=> SELECT count(data) FROM orders WHERE data @@ 'orderDate IN ("2015/06/01","2015/06/02","2015/06/03","2015/06/04","2015/06/05","2015/06/06","2015/06/07","2015/06/08","2015/06/09","2015/06/10","2015/06/11","2015/06/12","2015/06/13","2015/06/14","2015/06/15","2015/06/16","2015/06/17")';
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

[akorotkov]

Fixed in master.

[egamihideki]

Thank you for quick response! I've confirmed that Jsquery is moving as expected!

For reference, I was measuring the processing time.
More than 30 elements, took a lot of time. (29 elements took 5msec)

test_db=> SELECT count(data) FROM orders;
count
'--------
150055

test_db=> SELECT count(data) FROM orders WHERE data @@ 'orderDate IN ("2015/06/01","2015/06/02","2015/06/03","2015/06/04","2015/06/05","2015/06/06","2015/06/07","2015/06/08","2015/06/09","2015/06/10","2015/06/11","2015/06/12","2015/06/13","2015/06/14","2015/06/15","2015/06/16","2015/06/17","2015/06/18","2015/06/19","2015/06/20","2015/06/21","2015/06/22","2015/06/23","2015/06/24","2015/06/25","2015/06/26","2015/06/27","2015/06/28","2015/06/29","2015/06/30")';
count
'-------
1
5375.752 ms

[akorotkov]

Hmm... That seems quite strange. Could you share a dataset?

[egamihideki]

I am so sorry!
After re-create INDEX, Both processing time is now 0.9msec.