Add numeric metadata
Closes #773 (closed)
Filtering using a subquery does work well: Element.objects.filter(id__in=MetaData.objects.filter(name=…, value=…)), including with a Cast(). Filtering with = or <> adds 400ms to the API (200ms per query), and filtering with <, <=, >=, > adds 3800ms to the API (1900ms per query).
Many, many indexes were tested:
-
(name)(not used) -
(name) INCLUDE (element_id)(not used) -
(name, type)(not used) -
(name, type) INCLUDE (element_id)(not used) -
(name, type, value)(fails becausevaluecan exceed the index tuple size) -
(value)(not used) -
(value) WHERE type = 'numeric'(not used) -
((value::double precision)) INCLUDE (element_id) WHERE type = 'numeric'(not used) -
(name, (value::double precision)) WHERE type = 'numeric'(not used) -
(name, type, (value::double precision)) WHERE type = 'numeric'(not used) -
((value::double precision)) WHERE type = 'numeric' INCLUDE (element_id)(not used) -
((value::double precision)) WHERE type = 'numeric'(it works!)
This last index makes queries using lt, gt, gte and lte 20 to 25% faster, so queries take 3 seconds at the 13000th page of a query that filters 500K elements with 700K metadata (500K numeric) to get 250K elements.
Edited by Erwan Rouchet