Checking value existence in index

I have this simple table:

CREATE TABLE c (
   id INT NOT NULL,
   foo INT[] NULL,
   CONSTRAINT "primary" PRIMARY KEY (id ASC),
   INVERTED INDEX c_foo_idx (foo)
)

I would like to check whether an integer is in any of the foo values. I don’t care about what rows have it, or how many of them have it, just the answer whether any rows have an integer in their foo column.

This works:

SELECT true FROM c WHERE foo@> ARRAY[542432941067993089] limit 1;

but according to EXPLAIN, it’s suboptimal:

       tree       |    field    |               description                
+-----------------+-------------+-----------------------------------------+
                  | distributed | false                                    
                  | vectorized  | false                                    
  render          |             |                                          
   └── index-join |             |                                          
        │         | table       | c@primary                            
        │         | key columns | id                             
        └── scan  |             |                                          
                  | table       | c@c_foo_idx                               
                  | spans       | /542432941067993089-/542432941067993090  
                  | limit       | 1                                        
(10 rows)

I’ve tried to select the primary key as well to spare that index-join (is that a real join BTW, so a query is made to the other table?).

On the other hand, isn’t there a “is this value exist”-type of index, which is more effective than storing individual (column) values and their primary keys?

Hi, I think we currently don’t have a way to not have an inverted index re-scan its primary index. This is probably a deficiency. I’ve filed an issue: https://github.com/cockroachdb/cockroach/issues/46765

Thanks for asking!

Hi,

Great, thank you!

For the other question:
Do you see any possibilities for having an optimized index just for checking the existence of values?