Multiple keys lookup in inverted indexes

Hi,

I’m trying to filter for multiple values in a JSONB field, but as I can see right now it’s not possible:

CREATE TABLE jsondemo (
    id INT NOT NULL,
    data JSON NULL,
    CONSTRAINT "primary" PRIMARY KEY (id ASC),
    INVERTED INDEX data_idx (data)
); 
INSERT INTO jsondemo VALUES(1, '{"arrayField": [1,2,3,4]}');
SELECT * FROM jsondemo WHERE data @> '{"arrayField": [2,4]}';

According to the 20171020_inverted_indexes RFC it should produce 2 index scans and the results should be intersected.

Right now I got an error message (using v2.0-beta-20180312):
… trying to use multiple keys in index lookup

Will it change in the near future?

Hi @szalair,

v2.0-beta.20180326 on our website should support this query. However, the RFC is somewhat aspirational and in 2.0 we won’t support the full intersection algorithm. The queries will still work, but they won’t be as fast as possible. We’re currently working on some extensions to our execution engine that will support these queries better in 2.1.

Hope that helps!
Justin

Hi @justin,

Thank you for the answer! I tried in v2.0-beta.20180326 with the following table content:

+----+-------------------------------+
| id |              data             |
+----+-------------------------------+
|  1 | {"arr": [1, 2, 3, 4, 5, 6]}   |
|  2 | {"arr": [2, 4, 6]}            |
|  3 | {"arr": [1, 3, 5, 7]}         |
|  4 | [1, 2, 3, 4]                  |
|  5 | {"a": 5, "arr": [1, 2, 3, 4]} |
+----+-------------------------------+ 

Working:

SELECT * FROM jsondemo WHERE data @> '{"arr": [2, 4, 6], "a": 5}'
SELECT * FROM jsondemo WHERE data @> '[2, 3]'

Not working:

SELECT * from jsondemo WHERE data @> '{"arr": [2, 4, 6]}'
pq: logicalSpans = [[/'{"arr": [2, 4, 6]}' - /'{"arr": [2, 4, 6]}']], table ID = 51, index ID = 2: trying to use multiple keys in index lookup

So it’s a little confusing, but partly works. :slight_smile:

My other problem is that before this query (filter for JSONB field) we must filter for normal columns first. We have 3 billion rows and we can query first for an accountId to narrow the resultset and after that can we filter for values in the JSONB field. So basically a query like this for example:

SELECT * FROM messages WHERE accountId = 12345678 AND labels @> '[1, 4, 7]'

We got an index on accountId field and on the labels (JSONB) field as well, but we have to use the first index (accountId) first because it can narrow the results more drastically than the query for labels. Sadly a query can only use 1 index now, so we can’t use the JSONB index at all.
Do you plan to support multi column inverted indexes (where I can use “normal” columns near JSONB) or multiple index usage per query?

Thanks!

Thanks for the report - this was getting caused by a bug. There’s a fix out here https://github.com/cockroachdb/cockroach/pull/24376, and this will go into the first point release after 2.0. We really appreciate you noticing that!