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 becausevalue
can 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