I'm finding a weird behavior when using composite indexes. Say I have nodes :Output uniquely identified by tx_hash and output_index.
- tx_hash is a hash string of 64 random characters from a hash function
- output_index is an integer in the close to 0 range highly skewed to 0
I create a contraint:
CREATE CONSTRAINT ON (o:Output) ASSERT (o.tx_hash, o.output_index) IS NODE KEY;
But queries are being super slow and when I issue an explain I get:
Now if I introduce a property index as:
CREATE INDEX ON :Output(tx_hash)
Same query has execution plan:
and is executing way faster.
So my question is, what was wrong with the NODE KEY index and constraint. Why wasn't it enough for having fast queries? Given the distribution of the data, has the field order when defining the constraint something to do with performance of the index?