Index Join - #1008

Hi Folks!

I have a few quick questions on this one. What types of joins will this work for (inner, left)? Does it need to be a single equality operator? What if I have multiple columns in the join each with an equality comparison? Are there any data types restrictions or any other situations where this won't come into play?

My dataset is pretty big so indexing is expensive... I just want to make sure that my queries can utilize them!

Thanks!
-Alex

Hey Alex, this is only used for highly selective (If I remember correctly, up to a vector size) inner joins, so the real goal was point queries on equality joins.
The reason we decided to limit its usability is due to the tuple reconstruction costs, which quickly overcome the benefits of skipping data.
It also only works for one column, although I don't think extending it to work on a join with multiple would be such a difficult task ( I guess you're talking about having a compound index on multiple columns, or is it multiple single-dimensional indexes?)
Are you also thinking about using clustered indexes? I don't think those are supported yet, but they would solve the tuple reconstruction problem (With a penalty on storage and maintenance, ofc).

Anyway, let me know your schema and queries, and I'll be happy to take a look :-)

Btw, you can force the index joins with the following pragma, so they will be used regardless of selectivity.
PRAGMA force_index_join;

@pdet Hello another inquiry, is there a reason index_join got removed? Thanks in advance!