Composite index way slower than property index on one field, how can that be?

[toni]

Hi,

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?

[michael_hunger]

Explain doesn't tell you anything, it just uses estimates (based on index selectivity).

test it for real with real lookups and PROFILE