Julian Foad wrote: > Bert Huijben wrote: >> 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.
Ah... but that index couldn't be used for queries that only provide a local_relpath. Although parent_relpath is a prefix of local_relpath, a plain 'local_relpath' index is ordered by lexical order where the '/' character is not special (ordering example: foo0bar, foo/bar, fooZbar), whereas the local relpaths in a (parent_relpath, local_relpath) index would be would be in a different order (foo/bar, foo0bar, fooZbar). I wish we indexed the tables by (parent_relpath, basename) instead of duplicating the whole parent_relpath in the local_relpath column; that would make things like this easier. Sorry for the noise. - Julian
