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
Received on 2013-04-22 21:22:01 CEST