Skip to content

Add numeric metadata

Erwan Rouchet requested to merge numeric-metadata into master

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 because value 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

Merge request reports

Loading