Bert Huijben wrote: > Julian Foad wrote: >>>> -CREATE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE ( >>>> - wc_id, parent_relpath); >>>> +CREATE UNIQUE INDEX I_ACTUAL_PARENT ON ACTUAL_NODE ( >>>> + wc_id, parent_relpath, local_relpath); >>>> >>>> -CREATE INDEX I_NODES_PARENT ON NODES ( >>>> - wc_id, parent_relpath, op_depth); >>>> +CREATE UNIQUE INDEX I_NODES_PARENT ON NODES ( >>>> + wc_id, parent_relpath, local_relpath, op_depth); >> >> For 1.8, what's the point of still including 'parent_relpath' in the >> index, when we know that every 'local_relpath' value starts with >> 'parent_relpath'? Doesn't that just make the index a bit bigger and >> a bit slower than >> >> CREATE UNIQUE INDEX I_NODES_PARENT ON NODES ( >> wc_id, local_relpath, op_depth); > > We also have that index..., but we query nodes in 3 ways: > > * Where local_relpath = 'something' (exact lookup) > * Where parent_relpath = 'something' (everything in a directory) > * Where local_relpath > '...../' and local_relpath < '....0' (all > descendants) > > This index is used for that second variant and by making it a unique index > instead of one with the same value multiple times it provides a stable > order, cheaper index-updates (via exact lookup) and cheaper lookups for the > case where we only want values that are cached in the index.
That makes sense in itself, but then the original (wc_id, local_relpath, op_depth) index is redundant. So wouldn't it be better to add 'parent_relpath' to the primary key: - PRIMARY KEY (wc_id, local_relpath, op_depth) + PRIMARY KEY (wc_id, parent_relpath, local_relpath, op_depth) and not have a second index? Then there would only be one index to update, and all the other goodness would still be there. - Julian
