Will writing a partial index work for an in [x,y,z] query

SQL++
1

I’d like to do a query like this:

select meta().id from bucket where x=1 and y in [a,b,c]

Will a partial index on (x) where y = a or y = b or y = c
support complete pushdown to the indexes?

further, will this work:

index on (x, y asc) where y = a or y = b or y = c

support complete push down on:
select meta().id from bucket where x=1 and y in [a,b,c] order by y asc
offset 10 limit 100

thank you for your help

2

Partial index on OR not recommended and may work simple cases. In your case you can do this

CREATE INDEX ix1 ON  bucket (x,y) WHERE y IN ["a","b","c"];

    select meta().id 
    from bucket 
    where x=1 and y in ["a","b","c"]
    order by y asc
    offset 10 limit 100;

    select meta().id 
    from bucket 
    where x=1 and y in ["a","b"]
    order by y asc
    offset 10 limit 100;

One easy way to verify these is create empty bucket and change query use that bucket and do EXPLAIN (no data needed for EXPLAIN)

1 Like