Faster smart edits by hitting annotation_annotation_layer index #2832

New issue

Closed

opened 2015-09-11 13:56:43 +00:00 by wjt · 4 comments

wjt commented

2015-09-11 13:56:43 +00:00

For smart edits whose query matches annotations on relatively small layers (ie, not subtitles!), postgres will use the layer index to good effect if given the chance. But the way the query is built right now ends up producing upper(layer) = upper('foo') which can't use the (case-sensitive) index. The case-insensitivity is redundant for layer names, so here's a patch.

(With 400 such public smart edits, this patch makes the difference between being able to load the list of edits or the request timing out!)

For smart edits whose query matches annotations on relatively small layers (ie, not subtitles!), postgres will use the layer index to good effect if given the chance. But the way the query is built right now ends up producing `upper(layer) = upper('foo')` which can't use the (case-sensitive) index. The case-insensitivity is redundant for layer names, so here's a patch. (With 400 such public smart edits, this patch makes the difference between being able to load the list of edits or the request timing out!)

wjt added the

backend

label 2015-09-11 13:56:43 +00:00

wjt added this to the 14.04 milestone 2015-09-11 13:56:43 +00:00

j was assigned by wjt

2015-09-11 13:56:43 +00:00

wjt added the

normal

defect

labels 2015-09-11 13:56:43 +00:00

wjt commented

2015-09-11 13:57:12 +00:00

Author

Attachment 0001-ClipManager.filter_annotations-fix-opterator-typo.patch (915 bytes) added

**Attachment** 0001-ClipManager.filter_annotations-fix-opterator-typo.patch (915 bytes) added

0001-ClipManager.filter_annotations-fix-opterator-typo.patch

915 B

wjt commented

2015-09-11 14:02:13 +00:00

Author

Attachment 0002-ClipManager-match-annotation-layer-case-sensitively.patch (2011 bytes) added

**Attachment** 0002-ClipManager-match-annotation-layer-case-sensitively.patch (2011 bytes) added

0002-ClipManager-match-annotation-layer-case-sensitively.patch

2 KiB

wjt commented

2015-09-11 14:06:09 +00:00

Author

(Actually for some queries on edits you can sometimes do even better by moving the item_id in (...) clause to annotation_annotation rather than clip_clip and adding an index on annotation_annotation (item_id, layer) but it's a bit fiddly to get the query to come out that way. One for another time.)

(Actually for some queries on edits you can sometimes do even better by moving the `item_id in (...)` clause to `annotation_annotation` rather than `clip_clip` and adding an index on `annotation_annotation (item_id, layer)` but it's a bit fiddly to get the query to come out that way. One for another time.)

wjt commented

2015-09-14 12:14:35 +00:00

Author

In [pandora,5022]changeset:"pandora,5022":

#!CommitTicketReference repository="" revision="pandora,5022"
ClipManager: match annotation layer case-sensitively (fixes #2832)

The case must be correct anyway for the layer to be found in
settings.CONFIG['layers']. Running this:

    Q(annotation__layer__iexact='foo') &
    Q(annotation__findvalue__icontains='bar')

compiles to

    upper(layer) = upper('foo') and
    ...

which can't use the case-sensitive annotation_annotation_layer index.
This:

    Q(annotation__layer__exact='foo') &
    Q(annotation__findvalue__icontains='bar')

can. (It still can't use the findvalue_like index, though! The other
option is to add indices on upper(layer) and upper(findvalue)
[varchar_pattern_ops].)

In [pandora,5022]changeset:"pandora,5022": ``` #!CommitTicketReference repository="" revision="pandora,5022" ClipManager: match annotation layer case-sensitively (fixes #2832) The case must be correct anyway for the layer to be found in settings.CONFIG['layers']. Running this: Q(annotation__layer__iexact='foo') & Q(annotation__findvalue__icontains='bar') compiles to upper(layer) = upper('foo') and ... which can't use the case-sensitive annotation_annotation_layer index. This: Q(annotation__layer__exact='foo') & Q(annotation__findvalue__icontains='bar') can. (It still can't use the findvalue_like index, though! The other option is to add indices on upper(layer) and upper(findvalue) [varchar_pattern_ops].) ```

wjt added the

fixed

label 2015-09-14 12:14:35 +00:00

wjt closed this issue

2015-09-14 12:14:35 +00:00